从嵌套的JSON中提取特定的键如何？

Question

我正在尝试解析嵌套的JSON数据，但是很难从大量嵌套的数据中获取文本

resp = platform.get('/restapi/v1.0/account/~/call-log', params)
print ((resp.text()))

cursor = mydb.cursor()

json_obj = json.loads((resp.text()))
for result in json_obj["records"]:
    cursor.execute("INSERT INTO calldata (sessionID, startTime, fromName) VALUES (%s, %s, %s)",
                        (result["sessionId"], 
                         result["startTime"], 
                         result["from"]["name"]))

JSON输出

{
  "uri" : "https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=1&perPage=2",
  "records" : [ {
    "uri" : "https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log/123456?view=Simple",
    "id" : "123456",
    "sessionId" : "123456",
    "startTime" : "2019-10-09T20:47:26.577Z",
    "duration" : 45,
    "type" : "Voice",
    "direction" : "Outbound",
    "action" : "VoIP Call",
    "result" : "Call connected",
    "to" : {
      "phoneNumber" : "123456"
    },
    "from" : {
      "name" : "Jane Doe",
      "phoneNumber" : "123456",
      "extensionId" : "123456"
    },
    "recording" : {
      "uri" : "https://platform.ringcentral.com/restapi/v1.0/account/123456/recording/123456",
      "id" : "123456",
      "type" : "Automatic",
      "contentUri" : "https://media.ringcentral.com/restapi/v1.0/account/123456/recording/581514130067/content"
    },
    "extension" : {
      "uri" : "https://platform.ringcentral.com/restapi/v1.0/account/123456/extension/1409182064",
      "id" : 123456
    }
  }, {
    "uri" : "https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log/123456?view=Simple",
    "id" : "123456",
    "sessionId" : "123456",
    "startTime" : "2019-10-09T20:37:49.540Z",
    "duration" : 7,
    "type" : "Voice",
    "direction" : "Inbound",
    "action" : "Phone Call",
    "result" : "Missed",
    "to" : {
      "phoneNumber" : "123456"
    },
    "from" : {
      "name" : "Bob Smith",
      "phoneNumber" : "123456"
    }
  } ],
  "paging" : {
    "page" : 1,
    "perPage" : 2,
    "pageStart" : 0,
    "pageEnd" : 1
  },
  "navigation" : {
    "nextPage" : {
      "uri" : "https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=2&perPage=2"
    },
    "firstPage" : {
      "uri" : "https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=1&perPage=2"
    },
    "lastPage" : {
      "uri" : "https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=1&perPage=2"
    }
  }
}

我得到的错误是

(result["sessionId"], result["startTime"], result["result"], result["direction"], result["duration"], result["from"]["name"])) KeyError: 'name'

我试图从“from”字典和“records”中获取数据。你知道吗

Answer 1

看看你的json，我会说问题是没有result["name"]键，但是有result["from"]["name"]，试着用这个来代替。你知道吗

Answer 2

如您所见，JSON的结构如下：

{
    "uri": "",
    "records": [
        {
            [...]
            "from": {
                "name": ""
            }
        },
        {
            [...]
        }
    ]
}

因此，如果你想访问result["from"]["name"]，你实际上并没有得到它。首先是records->；然后是from->；然后是name。顺便说一下，记录是一个数组，所以您不必执行results['records']['from']['name']

如果要解析JSON，可以这样做：

# Reading JSON section
import json

json_dict = json.load(open("a.json", "r"))
# Iterating over records
for record in json_dict["records"]:
    print(record["from"]["name"])

输出：

$ python test.py                                                                                                        
Jane Doe
Bob Smith

Answer 3

使用^{}：

• 这将为您提供json_obj中的所有内容
• 为顶键创建数据帧，但records不会展开
• 为records创建包含顶层uri的数据帧
• 在两个数据帧上使用pd.merge

import pandas as pd
from pandas.io.json import json_normalize

df_top = json_normalize(json_obj)

# drop the unexpanded records column
df_top.drop(columns='records', inplace=True)

df_rec = json_normalize(data, 'records', ['uri'], meta_prefix='top')

df_merged = pd.merge(df_rec, df_top, left_on='topuri', right_on='uri')

# drop and rename columns
df_m.drop(columns='topuri', inplace=True)
df_m.rename(columns={'uri_x': 'records.uri', 'uri_y': 'top.uri'}, inplace=True)

# df_merged view
                                                                              records.uri      id sessionId                 startTime  duration   type direction      action          result to.phoneNumber  from.name from.phoneNumber from.extensionId                                                                  recording.uri recording.id recording.type                                                                      recording.contentUri                                                                      extension.uri  extension.id                                                                                                                                                                    top.uri  paging.page  paging.perPage  paging.pageStart  paging.pageEnd                                                                                                                                                    navigation.nextPage.uri                                                                                                                                                   navigation.firstPage.uri                                                                                                                                                    navigation.lastPage.uri
 https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log/123456?view=Simple  123456    123456  2019-10-09T20:47:26.577Z        45  Voice  Outbound   VoIP Call  Call connected         123456   Jane Doe           123456           123456  https://platform.ringcentral.com/restapi/v1.0/account/123456/recording/123456       123456      Automatic  https://media.ringcentral.com/restapi/v1.0/account/123456/recording/581514130067/content  https://platform.ringcentral.com/restapi/v1.0/account/123456/extension/1409182064      123456.0  https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=1&perPage=2            1               2                 0               1  https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=2&perPage=2  https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=1&perPage=2  https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=1&perPage=2
 https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log/123456?view=Simple  123456    123456  2019-10-09T20:37:49.540Z         7  Voice   Inbound  Phone Call          Missed         123456  Bob Smith           123456              NaN                                                                            NaN          NaN            NaN                                                                                       NaN                                                                                NaN           NaN  https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=1&perPage=2            1               2                 0               1  https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=2&perPage=2  https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=1&perPage=2  https://platform.ringcentral.com/restapi/v1.0/account/123456/call-log?view=Simple&showBlocked=true&withRecording=false&dateFrom=2019-10-09T16:00:00.000Z&page=1&perPage=2

• uri_x是记录中的uri，并重命名为records.uri
• uri_y&；topuri都是顶层uri，它们在合并时被保留
  • 删除topuri并将uri_y重命名为top.uri
• 根据需要删除或重命名任何其他列，或仅使用所需列创建单独的数据框
• 要保存，请使用df.to_csv或其他一些output options。你知道吗

注：

• 如果您只需要records（from在记录中），那么您只需要以下数据帧df_rec = json_normalize(data, 'records')，而不需要合并。你知道吗