How to covert complex JSON to CSV using ChoETL

Question

I want to convert the following JSON to CSV:

{
  "Data": [
    {
      "Ref": "h123",
      "StartDate": "2023-01-01",
      "EndDate": "2024-01-01",
      "Person": {
        "Name": "Tester",
        "Email": "[email protected]",
        "PhoneNumber": null,
        "AddressLine1": "1 avenue",
        "AddressLine2": null,
        "AddressLine3": null,
        "AddressLine4": null,
        "AddressLine5": null,
        "Zipcode": "100123"
      },
      "ExtraData": null,
      "ExtraData1": {
        "X": "x",
        "Y": "y",
        "Z": "z"
      }
    },
    {
      "Ref": "h1234",
      "StartDate": "2023-01-01",
      "EndDate": "2024-01-01",
      "Person": {
        "Name": "Tester1",
        "Email": "[email protected]",
        "PhoneNumber": null,
        "AddressLine1": "2 avenue",
        "AddressLine2": null,
        "AddressLine3": null,
        "AddressLine4": null,
        "AddressLine5": null,
        "Zipcode": "100124"
      },
      "ExtraData": null,
      "ExtraData1": {
        "X": "x",
        "Y": "y",
        "Z": "z"
      }
    }
  ]
}

I use the latest ChoETL.JSON.NETStandard and .NET6 (and .NET8)

using var reader = new ChoJSONReader("sample.json");
using var writer = new ChoCSVWriter("sample.csv").WithFirstLineHeader();
writer.Write(reader);

But the output CSV is just one row, assuming need more configuration.

Data_0_Ref,Data_0_StartDate,Data_0_EndDate,Data_0_Person_Name,Data_0_Person_Email,Data_0_Person_PhoneNumber,Data_0_Person_AddressLine1,Data_0_Person_AddressLine2,Data_0_Person_AddressLine3,Data_0_Person_AddressLine4,Data_0_Person_AddressLine5,Data_0_Person_Zipcode,Data_0_ExtraData,Data_0_ExtraData1_X,Data_0_ExtraData1_Y,Data_0_ExtraData1_Z,Data_1_Ref,Data_1_StartDate,Data_1_EndDate,Data_1_Person_Name,Data_1_Person_Email,Data_1_Person_PhoneNumber,Data_1_Person_AddressLine1,Data_1_Person_AddressLine2,Data_1_Person_AddressLine3,Data_1_Person_AddressLine4,Data_1_Person_AddressLine5,Data_1_Person_Zipcode,Data_1_ExtraData,Data_1_ExtraData1_X,Data_1_ExtraData1_Y,Data_1_ExtraData1_Z
h123,2023-01-01,2024-01-01,Tester,[email protected],,1 avenue,,,,,100123,,x,y,z,h1234,2023-01-01,2024-01-01,Tester1,[email protected],,2 avenue,,,,,100124,,x,y,z

What I expect is multiple rows without field index.

Also is it possible to configure so that switch between the ExtraData and ExtraData1 include and don't include in the CSV.

Answer 1

I assume you want to convert JSON to csv by ExtraData1 child node. If yes, there is option out of the box to setup by this node to output as expected CSV

using (var r = ChoJSONReader.LoadText(json)
       .WithJSONPath("Data")
       .Configure(c => c.DefaultArrayHandling = false)
       .Configure(c => c.FlattenNode = true)
       .Configure(c => c.UseNestedKeyFormat = true)
       .Configure(c => c.FlattenByNodeName = "ExtraData1")
      )
{
    using (var w = new ChoCSVWriter(Console.Out)
           .WithFirstLineHeader()
          )
    {
        w.Write(r);
    }
} 

Output:

Ref,StartDate,EndDate,Person_Name,Person_Email,Person_PhoneNumber,Person_AddressLine1,Person_AddressLine2,Person_AddressLine3,Person_AddressLine4,Person_AddressLine5,Person_Zipcode,ExtraData,ExtraData1_X,ExtraData1_Y,ExtraData1_Z
h123,2023-01-01,2024-01-01,Tester,[email protected],,1 avenue,,,,,100123,,x,y,z
h1234,2023-01-01,2024-01-01,Tester1,[email protected],,2 avenue,,,,,100124,,x,y,z

Sample fiddle: https://dotnetfiddle.net/7jjCPa