Merge JSON Array values into a single CSV column

Question

I have a JSON file something like this:

{
        "id": 2,
        "name": "I.1.A.2",
        "activeFlag": true,
        "recipients": [
            {
                "id": 3,
                "identityName": "idenity1",
                "fullName": "FullName1"
            },
            {
                "id": 4,
                "identityName": "identity2",
                "fullName": "FullName2"
            }
        ]
    }

I need to convert it to a CSV output similar to this using C# and dotnet Core.

"id","name","activeFlag","identityName"
"2","I.1.A.2","true","identity1;identity2"

However, I can only get the CSV output as:

"id","name","activeFlag","recipients_0", "recipients_1"
"2","I.1.A.2","true","identity1","identity2"

Here's the code that's generating the above output:

    using (var csv = new ChoCSVWriter(".\\temp\\csvoutput.csv").WithFirstLineHeader()
    )
    {
        using (var json = new ChoJSONReader(".\\temp\\tmpjson.json")
        .Configure(c => c.ConvertToFlattenObject(arrayIndexSeparator: ';'))
        .Configure(c => c.ArrayValueSeparator = ';')
        .Configure(c => c.ArrayValueSeparator = ';')
        .WithField("id", jsonPath: "$..id", isArray: false)
        .WithField("recipients", jsonPath: "$..recipients[*]..identityName", isArray: true, fieldName: "recipients")
)
        {
            csv.Write(json);
        }
    }

Right now, I'm using the ChoEtl library, but open to other options/suggestions. Been searching for an answer to this issue and haven't found any yet. Sorry if there's some solution I haven't found yet. I did try a similar solution here: How to output JSON array as a single field in CSV using ChoETL But didn't quite get it to fit my needs.

Answer 1

Above @Jack.A answer covers your scenarios to produce expected output.

Here is one another approach by setting UseNestedKeyFormat to false in CSV writer to produce the expected CSV output

string json = @"
{
        ""id"": 2,
        ""name"": ""I.1.A.2"",
        ""activeFlag"": true,
        ""recipients"": [
            {
                ""id"": 3,
                ""identityName"": ""idenity1"",
                ""fullName"": ""FullName1""
            },
            {
                ""id"": 4,
                ""identityName"": ""identity2"",
                ""fullName"": ""FullName2""
            }
        ]
    }";


StringBuilder csv = new StringBuilder();

using (var r = ChoJSONReader.LoadText(json)
    .WithField("id")
    .WithField("name")
    .WithField("activeFlag")
    .WithField("recipients", jsonPath: "$..recipients[*]..identityName")
    )
{
    using (var w = new ChoCSVWriter(csv)
        .Configure(c => c.ArrayValueSeparator = ';')
        .WithFirstLineHeader()
        .QuoteAllFields()
        .UseNestedKeyFormat(false)
        )
    {
        w.Write(r);
    }
}
Console.WriteLine(csv.ToString());

Output:

id,name,activeFlag,recipients
"2","I.1.A.2","True","idenity1,identity2"

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

Answer 2

Personally, I find the "Projection" approach easier to work with and reason about than the configuration-based approach. See https://www.codeproject.com/Articles/1193650/Cinchoo-ETL-Quick-Start-Converting-JSON-to-CSV-Fil

This works for me:

using (var csv = new ChoCSVWriter(output).WithFirstLineHeader()) {
    using (var json = new ChoJSONReader(input))
    {
        csv.Write(json.Select(jsonItem =>
        {
            var recipientList = new List<dynamic>(jsonItem.recipients);
            string recipientString = string.Join(';', recipientList.Select(r => r.identityName));
            return new
            {
                id = jsonItem.id,
                name = jsonItem.name,
                activeFlag = jsonItem.activeFlag,
                identityName = recipientString
            };
        }));
    }
}

There may be a more elegant way to construct the value for identityName, but since the values are dynamic, it's difficult to use Linq directly without running into CS1977 errors.

However, if you prefer the configuration-based approach, you can use a valueConverter combined with the jsonPath like so:

using (var csv = new ChoCSVWriter(output).WithFirstLineHeader())
{
    using (var json = new ChoJSONReader(input)
        .WithField("id")
        .WithField("name")
        .WithField("activeFlag")
        .WithField("recipients", jsonPath: "$.recipients[*].identityName"
            , valueConverter: o => string.Join(';', ((object[])o).Select(x => x.ToString())))
    )
    {
        csv.Write(json);
    }
}

I wasn't able to find any documentation for ConvertToFlattenObject so I'm not sure how that is supposed to work.