DataJanitor DataJanitor - 25 days ago 8
JSON Question

Nest in a Nest Json model to SQL Table

here is an example json. Previously, I was able to solve for Attachments and tags not being nested and as induvidual columns. Any help is deeply appreaciated!

{
"Volumes": [{
"AvailabilityZone": "us-east-1a",
"Attachments": [{
"AttachTime": "2013-12-18T22:35:00.000Z",
"InstanceId": "i-1234567890abcdef0",
"VolumeId": "vol-049df61146c4d7901",
"State": "attached",
"DeleteOnTermination": true,
"Device": "/dev/sda1",

"Tags": [{
"Value": "DBJanitor-Private",
"Key": "Name"
}, {
"Value": "DBJanitor",
"Key": "Owner"
}, {
"Value": "Database",
"Key": "Product"
}, {
"Value": "DB Janitor",
"Key": "Portfolio"
}, {
"Value": "DB Service",
"Key": "Service"
}]
}],
"Ebs": {
"Status": "attached",
"DeleteOnTermination": true,
"VolumeId": "vol-049df61146c4d7901",
"AttachTime": "2016-09-14T19:49:11.000Z"
},
"VolumeType": "standard",
"VolumeId": "vol-049df61146c4d7901"
}]
}

Answer

you can do it this way:

In [1]: fn = r'D:\temp\.data\40454898.json'

In [2]: with open(fn) as f:
   ...:     data = json.load(f)
   ...:

In [14]: t = pd.io.json.json_normalize(data['Volumes'],
    ...:                               ['Attachments','Tags'],
    ...:                               [['Attachments', 'VolumeId'],
    ...:                                ['Attachments', 'InstanceId']])
    ...:

In [15]: t
Out[15]:
         Key              Value Attachments.InstanceId   Attachments.VolumeId
0       Name  DBJanitor-Private    i-1234567890abcdef0  vol-049df61146c4d7901
1      Owner          DBJanitor    i-1234567890abcdef0  vol-049df61146c4d7901
2    Product           Database    i-1234567890abcdef0  vol-049df61146c4d7901
3  Portfolio         DB Janitor    i-1234567890abcdef0  vol-049df61146c4d7901
4    Service         DB Service    i-1234567890abcdef0  vol-049df61146c4d7901

NOTE: second argument ['Attachments','Tags'] is a path to to our nested record (data['Values']->Attachments->Tags) and third argument [['Attachments', 'VolumeId'], ['Attachments', 'InstanceId']] is a path to outer metadata (data['Values']->Attachments->VolumeId, data['Values']->Attachments->InstanceId)

Comments