Insert JSON object into database

Question

Hi i have a set of record with below format (the item ID is not in the format "key" : "value" and it consists thousand of records)

{
   "items":[
    {
        "20057":{
             "name":"Name 20057",
             "item_name":"name_20057"
        }
    },
    {
    "20060":{
         "name":"Name 20060",
         "item_name":"name_20060"
        }
     }
   ]
}

and i need to insert all of them into database as below

ItemID | Name       | Item_Name
----------------------------------
20057  | Name 20057 | name_20057
20060  | Name 20060 | name_20060

Is there any way to do this using SQL query or ASP.NET?

UPDATE

I tried to do it with .net and deserialize it. But i failed get the ItemID since it is not in this format "key" : "value"

Use JSON.Net, deserialize the text to class/objects and push that object to database — , Oct 12 '13 at 23:09
I agree with Dan use something like JSON.NET to Dapper to get the data into the DB. — jcwrequests, Oct 12 '13 at 23:11
I tried to do it with .net and deserialize it. but i cannot get the ItemID since it is not in this format "key" : "value" — My2ndLovE, Oct 13 '13 at 17:32

Gregory Nozik · Answer 1 · 2013-10-14T08:32:18.143

You have several options.

Deserialize json into .Net Objects and then insert into DB.

In order to write an example , I used JSON.NET library.

 private void TestJson()
        {
            string json = @"{'items':[
    {
        '20057':{
             'name':'Name 20057',
             'item_name':'name_20057'
        }
    },
    {
    '20060':{
         'name':'Name 20060',
         'item_name':'name_20060'
        }
     }

   Newtonsoft.Json.Linq.JContainer jsonObj = (Newtonsoft.Json.Linq.JContainer)JsonConvert.DeserializeObject(json);
}

And than you can iterate using SelectToken or Path methods

Create store procedure which will receive JSON string. In store procedure, convert it into table, and insert into DB.

Example how to convert json string into table

I tried to do it with .net and deserialize it. but i cannot get the ItemID since it is not in this format "key" : "value" — My2ndLovE, Oct 13 '13 at 20:20

score 0 · Answer 2 · answered Oct 13 '13 at 02:57

0

If you could get your json to the below format things would be a little bit easier when it comes to using json.net library:

[
    {
        "ItemID": 20057,
        "name": "Name 20057",
        "item_name": "name_20057"
    },
    {
        "ItemID": 20060,
        "name": "Name 20060",
        "item_name": "name_20060"
    }
]

You'll need a c# class as follows:

public class ItemDetail
{
    public string item_name { get; set; }
    public int ItemID { get; set; }
    public string name { get; set; }
}

Finally you can call the following method to get the array:

var arr = JsonConvert.DeserializeObject<List<ItemDetail>>(json)

answered Oct 13 '13 at 02:57

deostroll

11,661
21
90
161

I cannot change the json format as it consists of thousand of records. Or is there any can i can convert it into the format above? – My2ndLovE Oct 13 '13 at 17:36
We can write custom converters for the job using the library. Look here: http://stackoverflow.com/a/8031283/145682 – deostroll Oct 19 '13 at 04:57

Insert JSON object into database

2 Answers2