ChoETL json to csv with multiple arrays

Question

If I have a sample json like this and I want to use choETL to convert json to csv, how do i write both arrays to one file?

{
  "Id": "123456",
  "Request": [
    {
      "firstName": "A",
      "lastName": "B",
    }
  ],
  "Response": [
    {
      "SId": "123"
    }
  ]
}

Csv file should be something like Id,firstName,lastName,SId 123456,A,B,123

Cinchoo · Accepted Answer · 2022-05-20 12:18:43Z

1

Here is how you can produce the expected CSV from the json by using ChoETL

using (var r = ChoJSONReader.LoadText(json)
       .WithField("Id")
       .WithField("firstName", jsonPath:"Request[0].firstName", isArray:false)
       .WithField("lastName", jsonPath:"Request[0].lastName", isArray:false)
       .WithField("SId", jsonPath:"Response[0].SId", isArray:false)
       )
{
    using (var w = new ChoCSVWriter(Console.Out).WithFirstLineHeader())
        w.Write(r);
}

Output:

Id,firstName,lastName,SId
123456,A,B,123

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

UPDATE:

If the array is dynamic, here is one way to produce the CSV output by Request json array field

Sample JSON:

{
  "Id": "123456",
  "Request": [
    {
      "firstName": "A",
      "lastName": "B",
    },
    {
      "firstName": "A1",
      "lastName": "B1",
    }
    ],
  "Response": [
    {
      "SId": "123"
    },
    {
      "SId": "1234"
    }
  ]
}

Here is the code to parse the json by Request json array field

using (var r = ChoJSONReader.LoadText(json)
       .Configure(c => c.FlattenNode = true).Configure(c => c.FlattenByNodeName = "Request")
       )
{
    using (var w = new ChoCSVWriter(Console.Out).WithFirstLineHeader())
        w.Write(r);
}

Output:

Id,Response_0_SId,Response_1_SId,RequestfirstName,RequestlastName
123456,123,1234,A,B
123456,123,1234,A1,B

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

edited May 20, 2022 at 12:18

answered May 19, 2022 at 12:04

Cinchoo

6,3562 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

loga0060 Over a year ago

what if the array is dynamic?

Cinchoo Over a year ago

depends how the csv file to be produced. pls provide sample expected csv file.

Cinchoo Over a year ago

here is one approach dotnetfiddle.net/ya6Sjf

loga0060 Over a year ago

Thanks for your response i think the requirement was much simpler than originally thought, so i was able to get it to work with most of the samples available

Cinchoo Over a year ago

use .Configure(c => c.FileHeaderConfiguration.QuoteAllHeaders = true) on CSVWriter to writer headers with quotes.

|

Collectives™ on Stack Overflow

ChoETL json to csv with multiple arrays

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related