c# - 使用 ChoETL 将动态 JSON 格式转换为 CSV

Question

我会收到各种格式和层次结构的 JSON，需要知道通过动态形成列将任何 JSON 格式转换为 CSV 的可能性。例如，我提供了所需的详细信息，下面是多级 JSON 和预期CSV 输出

JSON

{
   "getUsers":[
      {
         "UserInformation":{
            "Id":1111122,
            "firstName":"*****1",
            "UserType":{
               "name":"CP"
            },
            "primaryState":"MA",
            "otherState":[
               "MA",
               "BA"
            ],
            "createdAt":null
         }
      },
      {
         "UserInformation":{
            "Id":3333,
            "firstName":"*****3",
            "UserType":{
               "name":"CPP"
            },
            "primaryState":"MPA",
            "otherState":[
               "KL",
               "TN"
            ],
            "createdAt":null
         }
      }
   ]
}

CSV 输出

标签： c#asp.netchoetl

Answer 1

以下是如何以编程方式使用 Cinchoo ETL 进行操作。

首先构造配置对象，以及运行时的字段配置，并将其传递给阅读器以加载 JSON

StringBuilder csv = new StringBuilder();

var config = new ChoJSONRecordConfiguration();
config.JSONPath = "$..getUsers[*].UserInformation";
config.AllowComplexJSONPath = true;

config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("Id"));
config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("FirstName"));
config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("UserType", "$.UserType.name"));
config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("primaryState"));
config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("otherState", "$.otherState[*]") { FieldType = typeof(string[]) });
config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("createdAt"));

using (var r = ChoJSONReader.LoadText(json, config))
{
    using (var w = new ChoCSVWriter(csv).WithFirstLineHeader()
        .UseNestedKeyFormat(false)
        )
        w.Write(r);
}

Console.WriteLine(csv.ToString());

小提琴：https ://dotnetfiddle.net/Gfw3r7