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.

Please include the C# code that creates the current output. – Jack A. Sep 13 '21 at 20:53 — Jack A., Sep 13 '21 at 20:53
Thanks @JackA.! Added the code snippet. – MarioP Sep 13 '21 at 20:59 — MarioP, Sep 13 '21 at 20:59

score 2 · Answer 1 · answered Sep 13 '21 at 22:04

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.

Thanks! However II'm getting errors as such: 'object[]' does not contain a definition for 'Select' and the best extension method overload 'ChoDynamicQueryable.Select(IQueryable, string, params object[])' requires a receiver of type 'IQueryable' , not sure why because seems like it should work. — MarioP, Sep 13 '21 at 22:45
@MarioP you need to `include System.Linq` so it recognizes the `Select` extension method. — Jack A., Sep 13 '21 at 23:04

score 1 · Accepted Answer · edited Sep 13 '21 at 23:41

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

thanks! Almost there. How do I change the delimiter for the "recipients" column as I need it to be a semicolon. Otherwise, this works exactly as expected! — MarioP, Sep 13 '21 at 22:46
Answered my own question :) Small edit to add .Configure(c => c.ArrayValueSeparator = ';') Thanks again for the quick response! — MarioP, Sep 13 '21 at 22:58

Merge JSON Array values into a single CSV column

2 Answers2