Typically Flatten Json uses C #

Question

I want to generally smooth some json so that I can convert to datatable and bind to datagrid using C #

What is the best way to do this, given that I don’t know how many levels I am lowering?

eg.

  {
   "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 expand this (I suppose I could iterate over datatable when I converted it), and not install.1.installationid, I would get installid1.

since I'm going to display the resulting data in a grid, I would like to keep the column names friendly.

Answer 1

You can use the Json.Net LINQ to JSON API to parse data in a JToken structure. From there, you can use the recursive helper method to walk through the structure and smooth it to Dictionary<string, object> , where the keys are "tracks" for 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 get key-value pairs, for example:

 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 want to make keys more human-friendly, you can use a little string manipulation to shorten them. 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); } 

This will give you this result:

 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 that with this arrangement you lost some context: for example, you can see that now there are two identical archived keys, whereas in the original JSON they were different because they appeared in different parts of the hierarchy ( installer.archived vs archived ). You will need to figure out how to solve this problem yourself.

Fiddle: https://dotnetfiddle.net/gzhWHk

Answer 2

Deserialize, then LINQ select flatten. I assume that since you have not stated this, you want all the information about the purpose and installation to be listed in the same record as the specific installation?

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

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

Answer 3

I also looked at the solution, and it helped me. The new CU7 Tupels are used for a set of results. If anyone has another lightweight solution, I wonder :-)

 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; } } 

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

 [0].id: 1 [0].name: Leanne Graham [0].username: Bret [0].email: [e-mail] [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: [phone] 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: [e-mail] [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: [phone] 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: [e-mail] [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: [phone] [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: [e-mail] [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: [phone] 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: [e-mail] [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: [phone] [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: [e-mail] [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: [phone] 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: [e-mail] [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: [phone] [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: [e-mail] [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: [phone] 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: [e-mail] [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: [phone] 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: [e-mail] [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: [phone] [9].website: ambrose.net [9].company.name: Hoeger LLC [9].company.catchPhrase: Centralized empowering task-force [9].company.bs: target end-to-end models 

Answer 4

Using the Json.Net Library You can use JSONPath $..* to get all members of the JSON structure and filter out elements without children to skip container properties.

eg,

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