JSON to CSV conversion using ChoETL displaying values in one row and not columns

Question

I am converting a JSON file to a CSV file. The JSON has multiple nested objects. While converting, I am able to get all the values out of the JSON and into the CSV. However, all the values are being shown as one row with the same heading repeated multiple times. I am using CHOETL library.

using (var csv = new ChoCSVWriter("file1.csv").WithFirstLineHeader().WithDelimiter(","))
{
    using (var json = new ChoJSONReader("file2.json")
        .WithField("RecordID", jsonPath: "$..Events[*].RecordId")
        .WithField("RecordType", jsonPath: "$..Events[*].RecordType")
        .WithField("EventDate", jsonPath: "$..Events[*].EventDate")
    {
        csv.Write(json);
    }
}

It shows the results as

• Record ID_0 Record ID_1 Record ID_2
• 123 456 789

Instead of as

• Record ID
• 123
• 456
• 789

Here is the JSON File

[
    {
        "Id": "3e399241",
        "IdLineage": [
            "sfdsfdsfs",
            "sdfdsfdsf"

        ],
        "Individuals": [
            {
                "Id": "1232112",
                "IdLineage": [
                    "fdsfsd1"
                ],
                "Events": [
                    {

                        "RecordId": "2132121321",
                        "RecordType": "SALE",
                        "EventDate": "2016-01-04T05:00:00Z"
                    },
                    {

                        "RecordId": "123213212",
                        "RecordType": "SALE",
                        "EventDate": "2012-07-16T04:00:00Z"
                    }


                ]
            },
            {
                "Id": "ssf2112",
                "IdLineage": [],
                "Events": [

                    {

                        "RecordId": "123213ds21",
                        "RecordType": "ACXIOMRECORD",
                        "EventDate": "2017-12-17T03:33:54.875Z"
                    }
                ]
            },
            {
                "Id": "asadsad",
                "IdLineage": [],
                "Events": [
                    {

                        "RecordId": "213213sa21",
                        "RecordType": "SALE",
                        "EventDate": "2018-03-09T05:00:00Z"
                    }
                ]
            }
        ]
    }
]

Answer 1

Based on sample code you posted, you are creating object from JSON as below

{
   RecordID : Array,
   RecordType: Array,
   EventDate: Array
}

This leads to generate CSV in the below format, this is as expected.

RecordID_0, RecordID_1, RecordID_2, RecordType_0, RecordType_1, ....

If you want to create CSV in the below format, you will have to fix the json path on each record field

RecordID, RecordType, EventData

sample code

using (var csv = new ChoCSVWriter("file1.csv").WithFirstLineHeader().WithDelimiter(","))
{
    using (var json = new ChoJSONReader("file2.json")
    .WithField("RecordID", jsonPath: "$..Events.RecordId")
    .WithField("RecordType", jsonPath: "$..Events.RecordType")
    .WithField("EventDate", jsonPath: "$..Events.EventDate")

    {
        csv.Write(json);
    }
}

UPDATE #1: After looking at the sample JSON, this is how you can pull the data and produce CSV file in expected format

StringBuilder msg = new StringBuilder();

using (var w = new ChoCSVWriter(msg)
    .WithFirstLineHeader()
    )
{
    using (var r = new ChoJSONReader("Sample32.json")
        .WithJSONPath("$..Events[*]")
        )
    {
        w.Write(r);
    }
}
Console.WriteLine(msg.ToString());

OUTPUT #1:

RecordId,RecordType,EventDate
2132121321,SALE,1/4/2016 5:00:00 AM
123213212,SALE,7/16/2012 4:00:00 AM
123213ds21,ACXIOMRECORD,12/17/2017 3:33:54 AM
213213sa21,SALE,3/9/2018 5:00:00 AM

UPDATE #2:

You must use Linq to combine id's with event members. Sample below shows how to

using (var fw = new StreamWriter("Sample32.csv", true))
{
    using (var w = new ChoCSVWriter(fw)
        .WithFirstLineHeader()
        )
    {
        using (var r = new ChoJSONReader("Sample32.json")
            .WithJSONPath("$..Individuals[*]")
            )
        {
            w.Write(r.SelectMany(r1 => ((dynamic[])r1.Events).Select(r2 => new { r1.Id, r2.RecordId, r2.RecordType, r2.EventDate })));
        }
    }
}
Console.WriteLine(File.ReadAllText("Sample32.csv"));

OUTPUT #2:

Id,RecordId,RecordType,EventDate
1232112,2132121321,SALE,1/4/2016 5:00:00 AM
1232112,123213212,SALE,7/16/2012 4:00:00 AM
ssf2112,123213ds21,ACXIOMRECORD,12/17/2017 3:33:54 AM
asadsad,213213sa21,SALE,3/9/2018 5:00:00 AM