JSON to CSV conversion using ChoETL Some array value missing

Question

I am converting a JSON file to a CSV file. The JSON has multiple nested objects and large size. While converting, I am able to get all the values out of the JSON and into the CSV. However, array values are missing . I am using CHOETL library. the sample json is (original json is long and big size)

{
    "getUsers": [
        {
            "UserInformation": {
                "Id": 1111122,
                "firstName": "*****1",
                "UserType": {
                    "name": "CP"
                },
                "primaryState": "MA",
                "otherState": [
                    "MA",
                    "BA"
                ],
                "createdAt": null
            }
        },
        {
            "UserInformation": {
                "Id": 3333,
                "firstName": "*****3",
                "UserType": {
                    "name": "CPP"
                },
                "primaryState": "MPA",
                "otherState": [
                    "KL",
                    "TN",
                    "DL",
                    "AP",
                    "RJ"
                ],
                "createdAt": null
            }
        }
    ]
}

the otherState array in first array is two but second array its four. its not showing is csv. the input json is long and nested hierarchy and mostly dynamic the code is

StringBuilder msg = new StringBuilder();

            using (var w = new ChoCSVWriter(msg)
                .WithFirstLineHeader()
                )
            {
                using (var r = new ChoJSONReader(@"E:/Temp/CSV/input/Data_Sample2.json")
                    .WithJSONPath("$..getUsers[*]")
                    )
                {
                    w.Write(r);
                }
            }
            File

the output is

UserInformation_Id,UserInformation_firstName,UserInformation_UserType_name,UserInformation_primaryState,UserInformation_otherState_0,UserInformation_otherState_1,UserInformation_createdAt
1111122,*****1,CP,MA,MA,BA, 3333,*****3,CPP,MPA,KL,TN,

Answer 1

You will have to mention the CSV driver to scan and discover fields automatically using MaxScanRows property (default 1)

Sample code below shows how to

StringBuilder csv = new StringBuilder();

using (var r = ChoJSONReader.LoadText(json)
    .WithJSONPath("$..getUsers[*]")
    )
{
    using (var w = new ChoCSVWriter(csv)
        .WithFirstLineHeader()
        .Configure(c => c.MaxScanRows = 2)
        .Configure(c => c.ThrowAndStopOnMissingField = false)
        )
    {
        w.Write(r);
    }
}

Console.WriteLine(csv.ToString());

Answer 2

I created a file test.json and put it in the bin directory. Test.json had the same contents above. then I used the following code:

using (var r = new ChoJSONReader("test.json"))
{
   using (var w = new ChoCSVWriter("sample.csv").WithFirstLineHeader())
   {
      w.Write(r);
   }
}

It generated the file sample.csv with the right array values. Are you sure, stringbuilder msg has the right content.