ChoETL nested JSON to CSV-CodePudding

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
    }));
}

CodePudding user response：

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

CodePudding user response：

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