Generically Flatten Json using c#

Question

I want to generically flatten some json so I can convert to a datatable and bind to a datagrid using c#

What is the best way of doign it, bearing in mind I dont know how many levels I am going down?

e.g.

{
  "appointmentid": 4,
  "policyid": 1,
  "guid": "00000000-0000-0000-0000-000000000000",
  "number": "1234567890",
  "ampm": "false",
  "date": "2015-09-08T00:00:00",
  "vehicle": {
    "id": 1,
    "guid": "00000000-0000-0000-0000-000000000000",
    "make": null,
    "model": null
  },
  "installer": {
    "installerid": "1",
    "name": "Installer 1",
    "contact": "qwerty",
    "qascore": "0",
    "address1": "qwerty",
    "address2": "qwerty",
    "address3": null,
    "address4": null,
    "city": "qwertyu",
    "county": "qwertyu",
    "postcode": "asdfghj",
    "country": "GB",
    "email": "asdfghj",
    "web": "asdfghjk",
    "archived": false
  },
  "installations": [
    {
      "installationid": 6,
      "installationstatus": {
        "installationstatusid": 4,
        "installationstatus": "FAIL"
      },
      "isactive": true
    },
    {
      "installationid": 7,
      "installationstatus": {
        "installationstatusid": 1,
        "installationstatus": "NEW"
      },
      "isactive": false
    }
  ],
  "archived": false
}

i would like to extend this (I suppose I could iterate over the datatable on I had converted it) rather than installations.1.installationid, i would get installationid1.

as I'm going to be displaying the resulting datatable in a grid I would like to keep the column names friendly.

Answer 1

You can use Json.Net's LINQ-to-JSON API to parse the data into a JToken structure. From there, you can use a recursive helper method to walk the structure and flatten it to a Dictionary<string, object> where the keys are the "path" to each value from the original JSON. I would write it something like this:

public class JsonHelper
{
    public static Dictionary<string, object> DeserializeAndFlatten(string json)
    {
        Dictionary<string, object> dict = new Dictionary<string, object>();
        JToken token = JToken.Parse(json);
        FillDictionaryFromJToken(dict, token, "");
        return dict;
    }

    private static void FillDictionaryFromJToken(Dictionary<string, object> dict, JToken token, string prefix)
    {
        switch (token.Type)
        {
            case JTokenType.Object:
                foreach (JProperty prop in token.Children<JProperty>())
                {
                    FillDictionaryFromJToken(dict, prop.Value, Join(prefix, prop.Name));
                }
                break;

            case JTokenType.Array:
                int index = 0;
                foreach (JToken value in token.Children())
                {
                    FillDictionaryFromJToken(dict, value, Join(prefix, index.ToString()));
                    index++;
                }
                break;

            default:
                dict.Add(prefix, ((JValue)token).Value);
                break;
        }
    }

    private static string Join(string prefix, string name)
    {
        return (string.IsNullOrEmpty(prefix) ? name : prefix + "." + name);
    }
}

Using this DeserializeAndFlatten method with your JSON you would end up with key-value pairs like this:

appointmentid: 4
policyid: 1
guid: 00000000-0000-0000-0000-000000000000
number: 1234567890
ampm: false
date: 9/8/2015 12:00:00 AM
vehicle.id: 1
vehicle.guid: 00000000-0000-0000-0000-000000000000
vehicle.make:
vehicle.model:
installer.installerid: 1
installer.name: Installer 1
installer.contact: qwerty
installer.qascore: 0
installer.address1: qwerty
installer.address2: qwerty
installer.address3:
installer.address4:
installer.city: qwertyu
installer.county: qwertyu
installer.postcode: asdfghj
installer.country: GB
installer.email: asdfghj
installer.web: asdfghjk
installer.archived: False
installations.0.installationid: 6
installations.0.installationstatus.installationstatusid: 4
installations.0.installationstatus.installationstatus: FAIL
installations.0.isactive: True
installations.1.installationid: 7
installations.1.installationstatus.installationstatusid: 1
installations.1.installationstatus.installationstatus: NEW
installations.1.isactive: False
archived: False

If you're looking to make the keys more human friendly, you could use a little string manipulation to cut them down. Maybe something like this:

var dict = JsonHelper.DeserializeAndFlatten(json);
foreach (var kvp in dict)
{
    int i = kvp.Key.LastIndexOf(".");
    string key = (i > -1 ? kvp.Key.Substring(i + 1) : kvp.Key);
    Match m = Regex.Match(kvp.Key, @"\.([0-9]+)\.");
    if (m.Success) key += m.Groups[1].Value;
    Console.WriteLine(key + ": " + kvp.Value);
}

That would give you this output instead:

appointmentid: 4
policyid: 1
guid: 00000000-0000-0000-0000-000000000000
number: 1234567890
ampm: false
date: 9/8/2015 12:00:00 AM
id: 1
guid: 00000000-0000-0000-0000-000000000000
make:
model:
installerid: 1
name: Installer 1
contact: qwerty
qascore: 0
address1: qwerty
address2: qwerty
address3:
address4:
city: qwertyu
county: qwertyu
postcode: asdfghj
country: GB
email: asdfghj
web: asdfghjk
archived: False
installationid0: 6
installationstatusid0: 4
installationstatus0: FAIL
isactive0: True
installationid1: 7
installationstatusid1: 1
installationstatus1: NEW
isactive1: False
archived: False

But note, with this arrangement, you have lost some context: for example, you can see that there are now two identical archived keys, whereas in the original JSON they were distinct because they appeared in different parts of the hierarchy (installer.archived vs. archived). You will need to figure out how to deal with that problem on your own.

Fiddle: https://dotnetfiddle.net/gzhWHk

Answer 2

Using the library Json.Net You could use the JSONPath $..* to get all members of the JSON structure and filter out the ones with no children to skip the container properties.

e.g.

var schemaObject = JObject.Parse(schema);
var values = schemaObject
    .SelectTokens("$..*")
    .Where(t => !t.HasValues)
    .ToDictionary(t => t.Path, t => t.ToString());

Answer 3

Another variant using Newtonsoft's Json.NET LINQ to JSON for object at root (the same can be done with JArray also):

var flattened = JObject.Parse(json)
    .Descendants()
    .OfType<JValue>()
    .ToDictionary(jv => jv.Path, jv => jv.ToString())