How to Map ChoETL Json to CSV Complex Types

301 Views Asked by At

I have the following Class structure

public class ResultData
{
    [JsonProperty(PropertyName = "Id")]
    public string Id { get; set; }

    [JsonProperty(PropertyName = "Results")]
    public IEnumerable<Result> Results{ get; set; }
}

public class Result
{
    public string Name { get; set; }

    public string Value { get; set; }
}

Here is my code to convert to csv

private static void ToCsv(ResultData data)
{
    StringBuilder csv = new StringBuilder();
    var json = JsonConvert.SerializeObject(data);

    var config = new ChoJSONRecordConfiguration {AllowComplexJSONPath = true};

    config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("Id"));
    config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("Results", "Name") { FieldType = typeof(string[]) });
    config.JSONRecordFieldConfigurations.Add(new ChoJSONRecordFieldConfiguration("Results", "Value") { FieldType = typeof(string[]) });

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

    File.WriteAllText("2.csv", csv.ToString());
}

Json

{"Id":"839c0a09-f2d0-4f29-9cce-bc022d3511b5","Results":[{"Name":"ABC","Value":"5"},{"Name":"CDE","Value":"2"}]}

What I would like is a CSV file With the Id and the results name in one column and the results value in another column

1

There are 1 best solutions below

2
On BEST ANSWER

Here is how you can produce the CSV output from JSON

string json = @"{
  ""Id"": ""839c0a09-f2d0-4f29-9cce-bc022d3511b5"",
  ""Results"": [
    {
      ""Name"": ""ABC"",
      ""Value"": ""5""
    },
    {
      ""Name"": ""CDE"",
      ""Value"": ""2""
    }
  ]
}";

StringBuilder csv = new StringBuilder();
using (var r = ChoJSONReader<ResultData>.LoadText(json)
    .UseJsonSerialization()
    )
{
    using (var w = new ChoCSVWriter(csv)
        .WithFirstLineHeader()
        .Configure(c => c.IgnoreDictionaryFieldPrefix = true)
        )
    {
        w.Write(r.Select(r1 => new
        {
            r1.Id,
            Results = r1.Results.ToDictionary(kvp => kvp.Name, kvp => kvp.Value)
        }));
    }
}

Console.WriteLine(csv.ToString());

Output:

Id,ABC,CDE
839c0a09-f2d0-4f29-9cce-bc022d3511b5,5,2

UPDATE:

private static void ToCsv(ResultData data)
{
    using (var w = new ChoCSVWriter("2.csv")
        .WithFirstLineHeader()
        .Configure(c => c.IgnoreDictionaryFieldPrefix = true)
        )
        w.Write(data.ConvertToEnumerable().Select(r1 => new
        {
            r1.Id,
            Results = r1.Results.ToDictionary(kvp => kvp.Name, kvp => kvp.Value)
        }));
}