Generically Flatten Json using c#

loop through nested json object c#
newtonsoft json deserialize flatten
nested json to dictionary c#
how to create nested json object in c#
read nested json in c#
c# deserialize json multidimensional array
jsonconvert.deserializeobject nested objects
c# json map

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.

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

Flattening JSON in JSON.NET, I've got an interesting problem at work where I need to take any arbitrary JSON blob (object or array) and represent the leaf nodes in memory  //Pass in the name of the array property you want to flatten public string FlattenJson(string input, string arrayProperty) { //Convert it to a JObject var unflattened = JsonConvert.DeserializeObject<JObject>(input); //Return a new array of items made up of the inner properties //of the array and the outer properties var flattened = ((JArray)unflattened[arrayProperty]) .Select(item => new JObject( unflattened.Properties().Where(p => p.Name != arrayProperty), ((JObject)item).Properties

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());

JsonFlatten 1.0.1, JsonFlatten. Extension methods to flatten a JSON.NET JObject to an IDictionary<​string, object> or vice versa. Supports flattening & unflattening  I managed to solve it out. Below is my code combined with Sarath Rachuri's flattening code.. I did not test it in too many cases, so it could be buggy. using Newtonsoft.Json.Linq; using System; using System.Collections.Generic; using System.Linq; using System.Text.RegularExpressions; namespace JSONHelper { class JSONFlattener { private enum JSONType{ OBJECT, ARRAY } public static Dictionary

Deserialize, then LINQ select to flatten. I'm guessing, since you haven't stated it, that you want all the appointment and installer information on the same record as the specific installation?

My initial idea would be to leverage dynamics so that you can avoid having to put in static schemas for your JSON. If you already have static types that can act as JSON schemas, then you can avoid dynamics (and all that it entails). Here's an example class - using JSON.NET - to illustrate what I'm thinking:

public class DeserializeAndFlatten
{
    public dynamic ParseJson()
    {
        var appointment = JObject.Parse(JsonData.JSON_TO_PARSE);  // <-- replace the constant w/ the real JSON...

        // this is where you flatten it all out!
        // not going to put all the fields in, that would kill the example, LOL
        var installations = appointment["installations"].Select(installation => new
        {
            appointmentId = appointment["appointmentid"],
            policyId = appointment["policyid"],

            vehicleId = appointment["vehicle"]["id"],
            vehicleMake = appointment["vehicle"]["make"],
            vehicleModel = appointment["vehicle"]["model"],

            installerId = appointment["installer"]["installerid"],
            installerName = appointment["installer"]["name"],

            installationId = installation["installationid"],
            installationStatus = installation["installationstatus"]["installationstatus"],
            installationStatusId = installation["installationstatus"]["installationstatusid"],
        }).ToList();

        return installations;
    }
}

You can test the code:

    static void Main(string[] args)
    {
        var jsonParser = new DeserializeAndFlatten();
        var installations = jsonParser.ParseJson();

        // FYI we get back a dynamic listing, 
        // so intellisense wont work...
        foreach (var installation in installations)
        {
            Console.WriteLine($"appointmentId: {installation.appointmentId}");
            Console.WriteLine($"installer: {installation.installerName}");
            Console.WriteLine($"installation id: {installation.installationId}");
            Console.WriteLine($"status: {installation.installationStatus}");
            Console.WriteLine();
        }

        Console.ReadLine();
    }

How to Map Nested JSON Objects to a .NET Dictionary · Nick , NET Dictionary. Sometimes a public JSON API responds in a format that you can't control. For example, nested objects instead of an array: In cases where JSON property names conflict with c# naming conventions, you can use DataMember or JsonProperty annotations to substitute a different name during serialization. For instance, the following works with both DataContractJsonSerializer and Json.NET:

Looked also for a solution and that did the job for me. Used new C# 7 Tupels for the result set. If anyone have another lightweight solution i'm interested:-)

async Task Main()
{
    var jsonUsers = await new HttpClient().GetStringAsync(@"https://jsonplaceholder.typicode.com/users");

    foreach (var flattedChild in GetFlatJsonChilds(JToken.Parse(jsonUsers)))
        Console.WriteLine($"{flattedChild.path}: {flattedChild.value}");
}

IEnumerable<(string path, string value)> GetFlatJsonChilds(JToken token)
{
    foreach (var child in token.Children())
    {   
        if(token.Type != JTokenType.Array &&token.Children().First().Type != JTokenType.Property && !child.Children().Any())
            yield return (child.Path, child.ToString());
        foreach(var childChild in GetFlatJsonChilds(child))
            yield return childChild;
    }
}

The Result for https://jsonplaceholder.typicode.com/users :

[0].id: 1
[0].name: Leanne Graham
[0].username: Bret
[0].email: Sincere@april.biz
[0].address.street: Kulas Light
[0].address.suite: Apt. 556
[0].address.city: Gwenborough
[0].address.zipcode: 92998-3874
[0].address.geo.lat: -37.3159
[0].address.geo.lng: 81.1496
[0].phone: 1-770-736-8031 x56442
[0].website: hildegard.org
[0].company.name: Romaguera-Crona
[0].company.catchPhrase: Multi-layered client-server neural-net
[0].company.bs: harness real-time e-markets
[1].id: 2
[1].name: Ervin Howell
[1].username: Antonette
[1].email: Shanna@melissa.tv
[1].address.street: Victor Plains
[1].address.suite: Suite 879
[1].address.city: Wisokyburgh
[1].address.zipcode: 90566-7771
[1].address.geo.lat: -43.9509
[1].address.geo.lng: -34.4618
[1].phone: 010-692-6593 x09125
[1].website: anastasia.net
[1].company.name: Deckow-Crist
[1].company.catchPhrase: Proactive didactic contingency
[1].company.bs: synergize scalable supply-chains
[2].id: 3
[2].name: Clementine Bauch
[2].username: Samantha
[2].email: Nathan@yesenia.net
[2].address.street: Douglas Extension
[2].address.suite: Suite 847
[2].address.city: McKenziehaven
[2].address.zipcode: 59590-4157
[2].address.geo.lat: -68.6102
[2].address.geo.lng: -47.0653
[2].phone: 1-463-123-4447
[2].website: ramiro.info
[2].company.name: Romaguera-Jacobson
[2].company.catchPhrase: Face to face bifurcated interface
[2].company.bs: e-enable strategic applications
[3].id: 4
[3].name: Patricia Lebsack
[3].username: Karianne
[3].email: Julianne.OConner@kory.org
[3].address.street: Hoeger Mall
[3].address.suite: Apt. 692
[3].address.city: South Elvis
[3].address.zipcode: 53919-4257
[3].address.geo.lat: 29.4572
[3].address.geo.lng: -164.2990
[3].phone: 493-170-9623 x156
[3].website: kale.biz
[3].company.name: Robel-Corkery
[3].company.catchPhrase: Multi-tiered zero tolerance productivity
[3].company.bs: transition cutting-edge web services
[4].id: 5
[4].name: Chelsey Dietrich
[4].username: Kamren
[4].email: Lucio_Hettinger@annie.ca
[4].address.street: Skiles Walks
[4].address.suite: Suite 351
[4].address.city: Roscoeview
[4].address.zipcode: 33263
[4].address.geo.lat: -31.8129
[4].address.geo.lng: 62.5342
[4].phone: (254)954-1289
[4].website: demarco.info
[4].company.name: Keebler LLC
[4].company.catchPhrase: User-centric fault-tolerant solution
[4].company.bs: revolutionize end-to-end systems
[5].id: 6
[5].name: Mrs. Dennis Schulist
[5].username: Leopoldo_Corkery
[5].email: Karley_Dach@jasper.info
[5].address.street: Norberto Crossing
[5].address.suite: Apt. 950
[5].address.city: South Christy
[5].address.zipcode: 23505-1337
[5].address.geo.lat: -71.4197
[5].address.geo.lng: 71.7478
[5].phone: 1-477-935-8478 x6430
[5].website: ola.org
[5].company.name: Considine-Lockman
[5].company.catchPhrase: Synchronised bottom-line interface
[5].company.bs: e-enable innovative applications
[6].id: 7
[6].name: Kurtis Weissnat
[6].username: Elwyn.Skiles
[6].email: Telly.Hoeger@billy.biz
[6].address.street: Rex Trail
[6].address.suite: Suite 280
[6].address.city: Howemouth
[6].address.zipcode: 58804-1099
[6].address.geo.lat: 24.8918
[6].address.geo.lng: 21.8984
[6].phone: 210.067.6132
[6].website: elvis.io
[6].company.name: Johns Group
[6].company.catchPhrase: Configurable multimedia task-force
[6].company.bs: generate enterprise e-tailers
[7].id: 8
[7].name: Nicholas Runolfsdottir V
[7].username: Maxime_Nienow
[7].email: Sherwood@rosamond.me
[7].address.street: Ellsworth Summit
[7].address.suite: Suite 729
[7].address.city: Aliyaview
[7].address.zipcode: 45169
[7].address.geo.lat: -14.3990
[7].address.geo.lng: -120.7677
[7].phone: 586.493.6943 x140
[7].website: jacynthe.com
[7].company.name: Abernathy Group
[7].company.catchPhrase: Implemented secondary concept
[7].company.bs: e-enable extensible e-tailers
[8].id: 9
[8].name: Glenna Reichert
[8].username: Delphine
[8].email: Chaim_McDermott@dana.io
[8].address.street: Dayna Park
[8].address.suite: Suite 449
[8].address.city: Bartholomebury
[8].address.zipcode: 76495-3109
[8].address.geo.lat: 24.6463
[8].address.geo.lng: -168.8889
[8].phone: (775)976-6794 x41206
[8].website: conrad.com
[8].company.name: Yost and Sons
[8].company.catchPhrase: Switchable contextually-based project
[8].company.bs: aggregate real-time technologies
[9].id: 10
[9].name: Clementina DuBuque
[9].username: Moriah.Stanton
[9].email: Rey.Padberg@karina.biz
[9].address.street: Kattie Turnpike
[9].address.suite: Suite 198
[9].address.city: Lebsackbury
[9].address.zipcode: 31428-2261
[9].address.geo.lat: -38.2386
[9].address.geo.lng: 57.2232
[9].phone: 024-648-3804
[9].website: ambrose.net
[9].company.name: Hoeger LLC
[9].company.catchPhrase: Centralized empowering task-force
[9].company.bs: target end-to-end models

Code Editor, NET sandbox for developers to quickly try out code and share code snippets. .​NET Fiddle .NET Framework v 4.5. Language. C#. C#, VB.NET, F#. Project Type. Console. Console, Script using System.Collections.Generic;. 3. using System.​Linq;. 4. using Newtonsoft.Json;. 5. ​. 6 Something something = JsonConvert. Possible duplicate of How to do recursive descent of JSON using Json.Net? or maybe Generically Flatten Json using c# – Brian Rogers Aug 27 '19 at 20:25 add a comment | 2 Answers 2

For those who need same in F#:

module JsonFlatten =

let Join prefix name =
    if String.IsNullOrEmpty(prefix) then name else prefix + "." + name

let rec FillDictionaryFromJToken (dict:Dictionary<string, string>) (token:JToken) (prefix:string) =
    match token.Type with
    | JTokenType.Object ->
        for prop in token.Children<JProperty>() do
            FillDictionaryFromJToken dict prop.Value (Join prefix prop.Name)
    | JTokenType.Array ->
        let mutable index = 0

        for value in token.Children() do
            FillDictionaryFromJToken dict  value (Join prefix (index.ToString()))
            index <- index + 1
    | _ ->
        dict.Add(prefix, sprintf "%A" (token :?> JValue).Value)

let DeserializeAndFlatten(json:string) =
    let dict = Dictionary<string, string>()
    let token = JToken.Parse(json);
    FillDictionaryFromJToken dict  token ""
    dict

Serializing Collections, To serialize a collection - a generic list, array, dictionary, or your own custom collection - simply call the serializer with the object you want to get JSON for. Json. Jackson, JSON Java Processor Tutorials Jackson JSON - Using @JsonUnwrapped to serialize/deserialize properties as flattening data structure [Last Updated: Jun 9, 2019]

Complex Nested JSON Array Conversion to DataTable, How do I turn a C# object into a JSON string in .NET?How can I parse JSON with C#?Convert datatable to JSON in C#Generically Flatten Json  How to flatten a JSON or dict is a common question, to which, there are many answers. This answer focuses on using flatten_json to recursively flatten a nested dict or JSON; Assumptions: This answer assumes you already have the JSON or dict loaded into some variable (e.g. file, api, etc.) In this case we will use data; How is data loaded into

How to Flatten or Unflatten Complex JSON objects into Flat & Map , Couple of days back I got a questions on how to flatten JSON Object which may be simple of Complex in structure? JsonFlattener is a very  using System.Text.Json; using System.Text.Json.Serialization; Attributes from the System.Runtime.Serialization namespace aren't currently supported in System.Text.Json. How to write .NET objects to JSON (serialize) To write JSON to a string or to a file, call the JsonSerializer.Serialize method. The following example creates JSON as a string:

JsonObject Class (System.Json), C# Copy. public class JsonObject : System.Json.JsonValue, System. Generic.​IDictionary<string,System.Json.JsonValue>, System.Collections. Determines whether the specified object is equal to the current object. Projects each element of a sequence to an IEnumerable<T> and flattens the resulting sequences into  How to read JSON string in C# I think this part will be very easy for you. You just need an example JSON string and JSON class of your example. You can use jsonutils.com again, if you want to create a class for your example JSON. I will take JSON strings from my website. Reading JSON string from website in C# C# Codes for reading JSON string

Comments
  • There is a hierarchy here. How do you expect it to be as a datatable ?
  • Yes but this is purely for viewing in a grid. The columns are selected based on a field in the db so the hierarchy doesn't matter in this case
  • Hmm, is this a valid JSON? I'm trying to load it into a JSON visualizer and getting a "badly formatted JSON" error.
  • @code4life sorry I cut it down for brevity do it will be valid just possibly it's my human error sorry
  • NP. Will edit your OP with correct JSON & try to update my answer too
  • thank fo rthe code and the fiddle. One question however, if i pass in an array of appointments, then the resulting dictionary has 0.appointmentid etc. How would I be best stripping this out at the top level? after the dictionary is created, as per a regex example? or during the gerenic flatten method?
  • @brian, your approach is much more elegant than mm ine!
  • @MatthewFlynn I think you're better off manipulating the keys after the fact, because the recursive method as written requires that the keys be unique (by virtue of its using a dictionary to accumulate the values). Not to say you couldn't do it during the recursion, but if you went that route you would need to change the logic to be able to handle duplicate keys.
  • Just amazing... thank you. what if it was a list of the same object? right now it will return 0.appointmentid=1 .... 1.appointmentid =2 ,......... is there any way to save them in array or list?
  • @BrianRogers Do you have an example for unflattening?
  • This actually works, and is simpler and more elegant; kudos
  • I'm trying to have a generic solution. So is it not possible to generically flatten it? I.e with no concept of the fields being returned or the depth. As the view is gong to be used across my api so there any data could be returned.
  • It's definitely possible. Instead of specifying the fields by name, you would have to iterate all the fields. The complexity would be in having to initially traverse the JSON data hierarchy and identify the depth and sibling data at various depths. There's so many clever ways to do this, I've had to do that sort of work before, except that my generic output was from the JSON into an excel spreadsheet. The sample I've shown should be a decent starting point to start exploring different possibilities. The world is your oyster!
  • I was thinking of possibly using something recursive. So does jobject.parse return the items in a nested array structure?
  • Yes, it does. It basically will look like a domain object representing the JSON.