ChoETL nested JSON to CSV

Question

I need to convert a json to csv. The problem is that I can't select everything that i need in the nested json structure. Example of the json file:

{
    "system": {
        "created": "2021-08-01T13:33:37.123Z",
        "by": "web"
    },
    "location": {
        "id": 100,
        "country": "DE"
    },
    "order": [
        {
            "OrderID": 22,
            "OrderName": "Soda",
            "OrderArticles": [
                {
                    "Size": 33,
                    "ProductName": "Coke",
                    "ProductId": "999"
                },
                {
                    "Size": 66,
                    "ProductName": "Fanta",
                    "ProductId": "888"
                },
                {
                    "Size": 50,
                    "ProductName": "Pepsi",
                    "ProductId": "444"
                }
            ],
            "ProcessedId": 1001,
            "Date": "2021-08-02"
        },
        {
            "OrderID": 23,
            "OrderName": "Beverage",
            "OrderArticles": [
                {
                    "Size": 44,
                    "ProductName": "Coke",
                    "ProductId": "999"
                }
            ],
            "ProcessedId": 1002,
            "Date": "2021-08-03"
        }
    ]
}

This is the output i want:

created;by;id;country;OrderID;OrderName;Size;ProductName;ProductId
2021-08-01T13:33:37.123Z;web;100;DE;22;Soda;33;Coke;999
2021-08-01T13:33:37.123Z;web;100;DE;22;Soda;66;Fanta;888
2021-08-01T13:33:37.123Z;web;100;DE;22;Soda;50;Pepsi;444
2021-08-01T13:33:37.123Z;web;100;DE;23;Beverage;44;Coke;999

I can get the created and by values by them self and the values for OrderArticles. I just can't figure out how to get them togheter. This is the code I have used to get the result but divide into 2 different results:

using (var r = new ChoJSONReader(inBlob).WithJSONPath("$..order[*]").AllowComplexJSONPath(true))
{
    return (r.SelectMany(r1 => ((dynamic[])r1.OutputArticles).Select(r2 => new
    {
        r1.OrderID,
        r1.OrderName,
        r1.Size,
        r1.ProductName,
        r1.ProductId
    })));
}



using (var r = new ChoJSONReader(inBlob).WithJSONPath("$").AllowComplexJSONPath(true))
{
    return (r.Select(r1 => new
    {
        r1.system.created,
        r1.system.by
    }));
}

There's a lot of code there for something that, if you were using Newtonsoft.Json or System.Text.Json would be just `return JsonConvert.DeserializeObject(yourString)`. Any reason you're using that `ChoJSONReader` library? — Camilo Terevinto, Sep 06 '21 at 09:38

Cinchoo · Accepted Answer · 2021-09-06T16:33:43.303

Since you need system.created, system.by, location.id, location.country fields, you must load the entire json from root and then compose the expected object for the csv

Here are the working samples (Take the latest nuget packages)

METHOD 1: (Using dynamic model)

StringBuilder csv = new StringBuilder();
using (var r = new ChoJSONReader("*** YOUR JSON FILE PATH ***")
       .JsonSerializationSettings(s => s.DateParseHandling = DateParseHandling.None)
      )
{
    using (var w = new ChoCSVWriter(csv)
        .WithDelimiter(";")
        .WithFirstLineHeader())
    {
        w.Write(r.SelectMany(root =>
            ((Array)root.order).Cast<dynamic>()
            .SelectMany(order => ((Array)order.OrderArticles).Cast<dynamic>()
            .Select(orderarticle => new
            {
                root.system.created,
                root.system.by,
                root.location.id,
                order.OrderID,
                order.OrderName,
                orderarticle.Size,
                orderarticle.ProductName,
                orderarticle.ProductId,
            })
                )
            )
        );
    }
}
Console.WriteLine(csv.ToString());

Output:

created;by;id;OrderID;OrderName;Size;ProductName;ProductId
2021-08-01T01:33:37.123Z;web;100;22;Soda;33;Coke;999
2021-08-01T01:33:37.123Z;web;100;22;Soda;66;Fanta;888
2021-08-01T01:33:37.123Z;web;100;22;Soda;50;Pepsi;444
2021-08-01T01:33:37.123Z;web;100;23;Beverage;44;Coke;999

METHOD 2: Using POCO model

Define POCO objects matching with input JSON

public class System
{
    [JsonProperty("created")]
    public string Created { get; set; }

    [JsonProperty("by")]
    public string By { get; set; }
}

public class Location
{
    [JsonProperty("id")]
    public int Id { get; set; }

    [JsonProperty("country")]
    public string Country { get; set; }
}

public class OrderArticle
{
    [JsonProperty("Size")]
    public int Size { get; set; }

    [JsonProperty("ProductName")]
    public string ProductName { get; set; }

    [JsonProperty("ProductId")]
    public string ProductId { get; set; }
}

public class Order
{
    [JsonProperty("OrderID")]
    public int OrderID { get; set; }

    [JsonProperty("OrderName")]
    public string OrderName { get; set; }

    [JsonProperty("OrderArticles")]
    public List<OrderArticle> OrderArticles { get; set; }

    [JsonProperty("ProcessedId")]
    public int ProcessedId { get; set; }

    [JsonProperty("Date")]
    public string Date { get; set; }
}

public class OrderRoot
{
    [JsonProperty("system")]
    public System System { get; set; }

    [JsonProperty("location")]
    public Location Location { get; set; }

    [JsonProperty("order")]
    public List<Order> Orders { get; set; }
}

Then use the code below to load the json and output CSV in expected format

StringBuilder csv = new StringBuilder();
using (var r = new ChoJSONReader<OrderRoot>("*** YOUR JSON FILE PATH ***")
    .UseJsonSerialization()
    )
{
    using (var w = new ChoCSVWriter(csv)
        .WithDelimiter(";")
        .WithFirstLineHeader())
    {
        w.Write(r.SelectMany(root =>
            root.Orders
            .SelectMany(order => order.OrderArticles
            .Select(orderarticle => new
            {
                created = root.System.Created,
                by = root.System.By,
                id = root.Location.Id,
                order.OrderID,
                order.OrderName,
                orderarticle.Size,
                orderarticle.ProductName,
                orderarticle.ProductId,
            })
                )
            )
        );
    }
}
Console.WriteLine(csv.ToString());

METHOD 3: Simplified dynamic model approach

StringBuilder csv = new StringBuilder();
using (var r = new ChoJSONReader("*** YOUR JSON FILE PATH ***")
    .WithField("created", jsonPath: "$..system.created", isArray: false, valueConverter: o => ((DateTime)o).ToString("yyyy-MM-ddThh:mm:ss.fffZ"))
    .WithField("by", jsonPath: "$..system.by", isArray: false)
    .WithField("id", jsonPath: "$..location.id", isArray: false)
    .WithField("country", jsonPath: "$..location.country", isArray: false)
    .WithField("OrderID")
    .WithField("OrderName")
    .WithField("Size")
    .WithField("ProductName")
    .WithField("ProductId")
    .Configure(c => c.FlattenNode = true)
    )
{
    using (var w = new ChoCSVWriter(csv)
        .WithDelimiter(";")
        .WithFirstLineHeader())
    {
        w.Write(r);
    }
}
Console.WriteLine(csv.ToString());

METHOD 4: Even far simplified dynamic model approach

StringBuilder csv = new StringBuilder();
using (var r = new ChoJSONReader("*** YOUR JSON FILE PATH ***")
    .Configure(c => c.FlattenNode = true)
    .JsonSerializationSettings(s => s.DateParseHandling = DateParseHandling.None)
    )
{
    using (var w = new ChoCSVWriter(csv)
        .WithDelimiter(";")
        .WithFirstLineHeader()
        .Configure(c => c.IgnoreDictionaryFieldPrefix = true)
        )
    {
        w.Write(r);
    }
}
Console.WriteLine(csv.ToString());

Sample fiddle: https://dotnetfiddle.net/VCezp8

Resulting date-time fields differ from what was in the question. — Ivan Khorin, Sep 06 '21 at 13:30
Worst thing is that I tryed my POCO-driven idea with ChoETL and got really stuck of why is it converting string to date-time even is POCO class declares it as "string", for me, then sometimes object is not deserialized when attrs are not set to properties, I've found no corellation, theese two problems gave me experiense to avoid ChoETL in my projects. — Ivan Khorin, Sep 06 '21 at 14:20
Well, you are missing to tune the library to handle these situation. Use `UseJsonSerialization()` to handle those cases. I'll post sample if needed. — Cinchoo, Sep 06 '21 at 14:24
Thank you @Cinchoo . I went with your method 1 the dynamic model. It works as expected. I learnt a lot from your answer :) — Cokeisit, Sep 07 '21 at 13:17

score -1 · Answer 2 · answered Sep 06 '21 at 10:22

Here is my solution.

This is my data model:

using System.Text.Json.Serialization;

namespace JsonToCSV.Models;

// Root myDeserializedClass = JsonSerializer.Deserialize<Root>(myJsonResponse);
public class System
{
    [JsonPropertyName("created")]
    public string Created { get; set; }

    [JsonPropertyName("by")]
    public string By { get; set; }
}

public class Location
{
    [JsonPropertyName("id")]
    public int Id { get; set; }

    [JsonPropertyName("country")]
    public string Country { get; set; }
}

public class OrderArticle
{
    [JsonPropertyName("Size")]
    public int Size { get; set; }

    [JsonPropertyName("ProductName")]
    public string ProductName { get; set; }

    [JsonPropertyName("ProductId")]
    public string ProductId { get; set; }
}

public class Order
{
    [JsonPropertyName("OrderID")]
    public int OrderID { get; set; }

    [JsonPropertyName("OrderName")]
    public string OrderName { get; set; }

    [JsonPropertyName("OrderArticles")]
    public List<OrderArticle> OrderArticles { get; set; }

    [JsonPropertyName("ProcessedId")]
    public int ProcessedId { get; set; }

    [JsonPropertyName("Date")]
    public string Date { get; set; }
}

public class Root
{
    [JsonPropertyName("system")]
    public System System { get; set; }

    [JsonPropertyName("location")]
    public Location Location { get; set; }

    [JsonPropertyName("order")]
    public List<Order> Orders { get; set; }
}

and here is business logic (if you want, I can replace it with LINQ):

using System.Text.Json;
using JsonToCSV.Models;

var dataAsText = File.ReadAllText("data.json");

var data = JsonSerializer.Deserialize<Root>(dataAsText);

var csv = new List<string> { "created;by;id;country;OrderID;OrderName;Size;ProductName;ProductId" };

foreach (var order in data.Orders)
{
    foreach (var orderArticle in order.OrderArticles)
    {
        csv.Add(String.Format("{0};{1};{2};{3};{4};{5};{6};{7};{8}",
            data.System.Created,
            data.System.By,
            data.Location.Id,
            data.Location.Country,
            order.OrderID,
            order.OrderName,
            orderArticle.Size,
            orderArticle.ProductName,
            orderArticle.ProductId
        ));
    }    
}

File.WriteAllLines("data.csv", csv);

Creates .csv file with content:

created;by;id;country;OrderID;OrderName;Size;ProductName;ProductId
2021-08-01T13:33:37.123Z;web;100;DE;22;Soda;33;Coke;999
2021-08-01T13:33:37.123Z;web;100;DE;22;Soda;66;Fanta;888
2021-08-01T13:33:37.123Z;web;100;DE;22;Soda;50;Pepsi;444
2021-08-01T13:33:37.123Z;web;100;DE;23;Beverage;44;Coke;999

ChoETL nested JSON to CSV

2 Answers2