C# - Flatten Nested Json

Question

I have multiple JSONs with different layouts and i'm trying to create some code to flatten these and then turn it into a Datatable.

Example JSON 1

{
  "d": {
    "results": [
      {
        "__metadata": {
          "uri": "myuri.com",
          "type": "String"
        },
        "jobNumber": "123456789",
        "numberVacancy": "1",
        "some_obj": {
          "__metadata": {
            "uri": "myuri.com",
            "type": "String"
          },
          "code": "000012356"
        },
        "anothernested": {
          "results": [
            {
              "__metadata": {
                "uri": "myuri.com",
                "type": "String"
              },
              "picklistLabels": {
                "results": [
                  {
                    "__metadata": {
                      "uri": "myuri.com",
                      "type": "String"
                    },
                    "label": "Casual"
                  },
                  {
                    "__metadata": {
                      "uri": "myuri.com",
                      "type": "String"
                    },
                    "label": "Casual"
                  }
                ]
              }
            }
          ]
        }
      },
      {
        "__metadata": {
          "uri": "myuri.com",
          "type": "String"
        },
        "jobNumber": "987654321",
        "numberVacancy": "1",
        "some_obj": {
          "__metadata": {
            "uri": "myuri.com",
            "type": "String"
          },
          "code": "000012356"
        },
        "anothernested": {
          "results": [
            {
              "__metadata": {
                "uri": "myuri.com",
                "type": "String"
              },
              "picklistLabels": {
                "results": [
                  {
                    "__metadata": {
                      "uri": "myuri.com",
                      "type": "String"
                    },
                    "label": "Casual"
                  },
                  {
                    "__metadata": {
                      "uri": "myuri.com",
                      "type": "String"
                    },
                    "label": "Casual"
                  }
                ]
              }
            }
          ]
        }
      }
    ]
  }
}

Example of how I would like the JSON to be flattened into a Datatable.

__metadata/uri	__metadata/type	jobNumber	numberVacancy	some_obj/__metadata/uri	some_obj/__metadata/type	some_obj/code	anothernested/results/0/__metadata/uri	anothernested/results/0/__metadata/type	anothernested/results/0/picklistLabels/results/0/__metadata/uri	anothernested/results/0/picklistLabels/results/0/__metadata/type	anothernested/results/0/picklistLabels/results/0/label	anothernested/results/0/picklistLabels/results/1/__metadata/uri	anothernested/results/0/picklistLabels/results/1/__metadata/type	anothernested/results/0/picklistLabels/results/1/label
myuri.com	String	123456789	1	myuri.com	String	12356	myuri.com	String	myuri.com	String	Casual	myuri.com	String	Casual
myuri.com	String	987654321	1	myuri.com	String	12356	myuri.com	String	myuri.com	String	Casual	myuri.com	String	Casual

I will be flattening the JSON from the d.results index.

So far, I have this which will flatten each separate Json inside the results array into a dictionary of string. However, I am unsure about how to convert this into a datatable, keeping into mind that sometimes the elements in the dictionary may not be in the same order or there may be more or less elements in each of the JSON arrays.

IEnumerable<JToken> jTokens = jsonObject.Descendants().Where(p => p.Count() == 0);
results1 = jTokens.Aggregate(new Dictionary<string, string>(), (properties, jToken) =>
                    {
                        properties.Add(jToken.Path, jToken.ToString());
                        return properties;
                    });

Answer 1

To create a datatable from your source json you will need this code:

JObject jsonObject = JObject.Parse(json);
List<string> jpaths = jsonObject.Descendants().OfType<JProperty>().Where(jp => jp.Value is JArray).Select(jp => jp.Path).ToList();
List<JToken> rowtokens = jsonObject.SelectTokens("$.d.results.[*]").ToList();

DataTable resultTable = new DataTable();
resultTable.Columns.AddRange(((JObject)rowtokens[0]).Descendants().OfType<JProperty>().Where(jp => jp.Value is JValue).Select(jp => new DataColumn(jp.Path)).ToArray());
foreach (JToken rowtoken in rowtokens)
{
    resultTable.Rows.Add(((JObject)rowtoken).Descendants().OfType<JProperty>().Where(jp => jp.Value is JValue).Select(jp => jp.Value.ToString()).ToArray());
}