Convert JSON tree structure to list

Question

I am new to JSON. I have a JSON list in a tree structure like below:

{
  "complaint@simulator.amazonses.com": {
    "time": "2018-01-02T20:45:46.65Z",
    "type": "Complaint",
    "bounceType": "null",
    "bounceSubType": "null"
  },
  "struax@example.org": {
    "time": "2018-01-02T20:53:03Z",
    "type": "Bounce",
    "bounceType": "Permanent",
    "bounceSubType": "Suppressed"
  },
  "bounce-test@service.socketlabs.com": {
    "time": "2018-01-02T21:06:40.097Z",
    "type": "Bounce",
    "bounceType": "Permanent",
    "bounceSubType": "Suppressed"
  },
  "bounce@simulator.amazonses.com": {
    "time": "2018-01-02T21:08:02Z",
    "type": "Bounce",
    "bounceType": "Permanent",
    "bounceSubType": "General"
  },
  "jstrechay@example.org": {
    "time": "2018-01-05T06:31:39Z",
    "type": "Bounce",
    "bounceType": "Permanent",
    "bounceSubType": "General"
  },
  "leematt45@example.org": {
    "time": "2018-01-05T06:49:13Z",
    "type": "Bounce",
    "bounceType": "Permanent",
    "bounceSubType": "Suppressed"
  },
  "afbweb@example.org": {
    "time": "2018-01-07T12:50:38Z",
    "type": "Bounce",
    "bounceType": "Transient",
    "bounceSubType": "General"
  },
  "bajanina2013@example.org": {
    "time": "2018-01-02T08:12:19Z",
    "type": "Bounce",
    "bounceType": "Transient",
    "bounceSubType": "MailboxFull"
  },
  "martin.bunt@example.org": {
    "time": "2018-01-05T07:00:24Z",
    "type": "Complaint",
    "bounceType": "null",
    "bounceSubType": "null"
  }
}

My SQL table columns are Email, time, type, bounceType, and bounceSubType. How can I extract data from the JSON list and save it in the DB?

I am using this code:

string JSON = response.Content.ReadAsStringAsync().Result;
var jObj = (JObject)JsonConvert.DeserializeObject(JSON);

The JSON is in a tree structure and I am not able to fetch the parent node and the respective child in a list.

Possible duplicate of [How to convert Json array to list of objects in c#](https://stackoverflow.com/questions/19581820/how-to-convert-json-array-to-list-of-objects-in-c-sharp) — Hasan Fathi, Jan 09 '18 at 11:59

Brian Rogers · Accepted Answer · 2018-01-09T18:44:41.043

Here is what I would do. First, define a model class to hold the item data (you might already have such a class):

class BounceItem
{
    public string Email { get; set; }
    public DateTime Time { get; set; }
    public string Type { get; set; }
    public string BounceType { get; set; }
    public string BounceSubType { get; set; }
}

Next, deserialize the JSON into a Dictionary<string, BounceItem>:

var dict = JsonConvert.DeserializeObject<Dictionary<string, BounceItem>>(json);

The email addresses will become the keys in the dictionary, and values will be BounceItem objects containing the nested properties. However, notice that the Email property in each BounceItem will not be populated at this point.

To fix that, post-process the dictionary to copy each key into the Email property on the corresponding item, storing results into a List<BounceItem>:

var list = dict.Select(kvp => { kvp.Value.Email = kvp.Key; return kvp.Value; }).ToList();

Now you have a list of model objects which should match up to your SQL table, and you can insert them using whatever method is appropriate for your database of choice.

Fiddle: https://dotnetfiddle.net/5rzyCs

score 1 · Answer 2 · answered Jan 09 '18 at 11:59

1

It's pretty simple using Json.NET or using Newtonsoft.Json.Linq, if you are using c#.NET.

First Method:

dynamic jsonObject = JsonConvert.DeserializeObject("your json string");

Second Method:

dynamic jsonObject = JObject.Parse("your json string");

After parsing you can pass the jsonObject to the DB.

answered Jan 09 '18 at 11:59

Arun Raj R

2,197
3
13
23

thank for reply @Arun i am using this code string JSON = response.Content.ReadAsStringAsync().Result; var jObj = (JObject)JsonConvert.DeserializeObject(JSON); the json is in tree structure and i not able to fetch the parent node and the respected child in a list – riya mahanty Jan 09 '18 at 12:55

score 1 · Answer 3 · answered Jan 09 '18 at 12:01

1

You need to create a class based on your Json Responce. Than from that class deserialize your object like this

Class1 class1Object = JsonConvert.DeserializeObject<Class1>(str)

answered Jan 09 '18 at 12:01

shyam

327
2
7

thank for reply @shyam i am using this code string JSON = response.Content.ReadAsStringAsync().Result; var jObj = (JObject)JsonConvert.DeserializeObject(JSON); the json is in tree structure and i not able to fetch the parent node and the respected child in a list – riya mahanty Jan 09 '18 at 12:52
If you are not passing any class it will take dynamic. jObj = (JObject)JsonConvert.DeserializeObject(JSON); is actually jObj = (JObject)JsonConvert.DeserializeObject(JSON); so you need to create a class that has same pattern as your json object and pass that class instead of dynamic – shyam Jan 09 '18 at 16:46

Convert JSON tree structure to list

3 Answers3