Deserialization and Exporting of Nested JSON API Data to CSV

Question

I have requirement to take some data from an API from nested JSON and export it to CSV. I can't find the right syntax for taking that data to export it to CSV without creating multiple classes (there are hundreds of properties, and new ones can be added dynamically).

The JSON response structure from the API is below:

{
    "data": [
        {
            "id": "1",
            "type": "Bus",
            "attributes": {
                "property-two": "2020-12-10",
                "property-three": "D",
                "property-four": null,
                "property-five": 5
            }
        },
        {
            "id": "2",
            "type": "Car",
            "attributes": {
                "property-two": "2020-12-10",
                "property-three": "D",
                "property-four": null,
                "property-five": 5
            }
        }
    ]
}

We only need to export the "attributes" node from each dataset to CSV, but cannot seem to flatten the data or extract just those nodes.

The following code gets a list of JToken objects, but I'm not sure how this can be exported to CSV without re-serializing and de-serializing again. The datatype is dynamic since columns can be added and removed from it.

var jsonObject = JObject.Parse(apiResponseString);
var items = jsonObject["data"].Children()["attributes"].ToList();
//TODO: Export to CSV, DataTable etc

Is it possible to deserialize the data from the attributes nodes only, and how would that be done (whether on the initial JObject.Parse or serializing the JToken list again)? I'm not tied to JObject, so can use Newtonsoft or other as well.

Answer 1

Using Json.Net's LINQ-to-JSON API (JObjects), you can convert your JSON data to CSV as shown below.

First define a couple of short extension methods:

using System;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json.Linq;

public static class JsonHelper
{
    public static string ToCsv(this IEnumerable<JObject> items, bool includeHeaders = true)
    {
        if (!items.Any()) return string.Empty;
        var rows = new List<string>();
        if (includeHeaders)
        {
            rows.Add(items.First().Properties().Select(p => p.Name).ToCsv());
        }
        rows.AddRange(items.Select(jo => 
            jo.Properties().Select(p => p.Value.Type == JTokenType.Null ? null : p.Value).ToCsv()
        ));
        return string.Join(Environment.NewLine, rows);
    }

    public static string ToCsv(this IEnumerable<object> values)
    {
        const string quote = "\"";
        const string doubleQuote = "\"\"";
        return string.Join(",", values.Select(v => 
            v != null ? string.Concat(quote, v.ToString().Replace(quote, doubleQuote), quote) : string.Empty
        ));
    }
}

Then you can do:

var obj = JObject.Parse(json);
var csv = obj.SelectTokens("..attributes").Cast<JObject>().ToCsv();

Here is a working demo: https://dotnetfiddle.net/NF2G2l