How to extract specific keys from nested JSON?

Question

I'm trying to parse nested JSON data, but having difficulty getting the text from the heavily nested data

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 Output

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

The error I get is

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

I'm trying to get the data in the "from" dictionary as well as "records".

Answer 1

Use pandas.io.json.json_normalize:

• This will give you everything in json_obj
• Create a dataframe for the top keys, but records won't be expanded
• Create a dataframe for records that contains the top level uri
• Use pd.merge on the two dataframes

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 is the uri from records and renamed to records.uri
• uri_y & topuri are both the top level uri, which were retained from merging
  • Drop topuri and rename uri_y to top.uri
• Drop or rename any other columns as needed or create a separate dataframe with only the required columns
• To save, use df.to_csv or a number of other output options.

Note:

• If you only want records (from is in records), then you only need the following dataframe df_rec = json_normalize(data, 'records') and you don't need to merge.

Answer 2

As you can see, the structure of your JSON is as follows:

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

So if you want to acces result["from"]["name"] you're not actually getting to it. First you have records -> then from -> then name. And records is by the way an array, so you wouldn't do results['records']['from']['name']

If you want to parse your JSON, you can do it like this:

# 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"])

Output:

$ python test.py                                                                                                        
Jane Doe
Bob Smith