将Json模型嵌套到SQL选项卡中

Question

下面是一个json示例。以前，我能够解决附件和标签不嵌套和作为独立列。任何帮助都是非常感激的！你知道吗

{
    "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 1

你可以这样做：

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

注意：第二个参数['Attachments','Tags']是到嵌套记录（data['Values']->Attachments->Tags）的路径，第三个参数[['Attachments', 'VolumeId'], ['Attachments', 'InstanceId']]是到外部元数据（data['Values']->Attachments->VolumeId，data['Values']->Attachments->InstanceId）的路径