'C# - Flatten Nested Json

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;
                    });
c++sharparraysjsonjson.netflatten


Solution 1:[1]

You can try Cinchoo ETL - an open source lib for your needs.

using (var r = new ChoJSONReader("*** YOUR JSON FILE PATH ***")
    .WithJSONPath("$..d.results")
    .Configure(c => c.NestedColumnSeparator = '/')
    )
{
    var dt = r.AsDataTable();
    Console.WriteLine(dt.Dump());
}

Output:

__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,000012356,myuri.com,String,myuri.com,String,Casual,myuri.com,String,Casual
myuri.com,String,987654321,1,myuri.com,String,000012356,myuri.com,String,myuri.com,String,Casual,myuri.com,String,Casual

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

Disclaimer: I'm the author of this library.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions