How to parse nestead json and construct relational database columns from dict values using python

Below is my sample json. Am trying to extract "attributes" part of the json and insert into a relational database. But I needed to construct "name" values as relational columns and insert "value" values into table. I mean {"name":"ID","value":"528BE6D9FD"} "ID" as a column and insert 528BE6D9FD under the "ID". Its just beginning of my python learning so not sure on how to construct columns from dictionary values.

d = 'C:/adapters/sample1.json'
json_data = open(d).read()
json_file = json.loads(json_data)
for children in json_file["events"]:
    #print (children)
    for grandchildren in children["attributes"]:
        #print(grandchildren)
        for key, value in grandchildren.iteritems():
                #if key == 'name':
                    print value



{
   "events":[
      {
         "timestamp":"2010-11-20T11:08:00.978Z",
         "code":"Event",
         "namespace":null,
         "version":null,
         "attributes":[
            {
               "name":"ID",
               "value":"528BE6D9FD"
            },
            {
               "name":"Total",
               "value":67
            },
            {
               "name":"PostalCode",
               "value":"6064"
            },
            {
               "name":"Category",
               "value":"More"
            },
            {
               "name":"State",
               "value":"QL"
            },
            {
               "name":"orderDateTime",
               "value":"2010-07-20T12:08:13Z"
            },
            {
               "name":"CategoryID",
               "value":"1091"
            },
            {
               "name":"billingCountry",
               "value":"US"
            },
            {
               "name":"shipping",
               "value":"Go"
            },
            {
               "name":"orderFee",
               "value":77
            },
            {
               "name":"Name",
               "value":"Roy"
            }
         ]
      }
   ]
}

Solution

As far as extracting the attributes hash of your json data, I would do that like so:

json_path = "c:\\adapters\\sample1.json"
with open(json_path) as json_file:
    json_dict = json.load(json_file)

attributes = json_dict['events'][0]['attributes']

Now, I don't know which database system you are using, but regardless, you can extract names, and values with list comprehensions like so:

names = [key['name'] for key in attributes]
values = [key['value'] for key in attributes]

And now just create a table if needed, insert names as column headers, and insert values as a single row with respect to names.