Flatten Nested JSON in Python

Question

I'm new to Python and I'm quite stuck (I've gone through multiple other stackoverflows and other sites and still can't get this to work).

I've the below json coming out of an API connection

    {
   "results":[
      {
         "group":{
            "mediaType":"chat",
            "queueId":"67d9fb5e-26b2-4db5-b062-bbcfa8d2ca0d"
         },
         "data":[
            {
               "interval":"2021-01-14T13:12:19.000Z/2022-01-14T13:12:19.000Z",
               "metrics":[
                  {
                     "metric":"nOffered",
                     "qualifier":null,
                     "stats":{
                        "max":null,
                        "min":null,
                        "count":14,
                        "count_negative":null,
                        "count_positive":null,
                        "sum":null,
                        "current":null,
                        "ratio":null,
                        "numerator":null,
                        "denominator":null,
                        "target":null
                     }
                  }
               ],
               "views":null
            }
         ]
      }
   ]
}

and what I'm mainly looking to get out of it is (or at least something as close as)

MediaType	QueueId	NOffered
Chat	67d9fb5e-26b2-4db5-b062-bbcfa8d2ca0d	14

Is something like that possible? I've tried multiple things and I either get the whole of this out in one line or just get different errors.

Tried a_json = json.loads(data_query_result) dataframe = pd.DataFrame.from_dict(a_json, orient="index") df = pd.json_normalize(dataframe) print (df) which just gives me an empty data frame. If I try to pass data_query_result right in to the json_normalize I get an error saying # check to see if a simple recursive function is possible to NotImplementedError: Other attempts I've gotten String Indices must be integers error — titan31, Jan 17 '22 at 12:27
Tried this too just earlier today https://towardsdatascience.com/flattening-json-objects-in-python-f5343c794b10 — titan31, Jan 17 '22 at 12:30

pugi · Accepted Answer · 2022-01-17T12:27:50.087

1

The error you got indicates you missed that some of your values are actually a dictionary within an array.

Assuming you want to flatten your json file to retrieve the following keys: mediaType, queueId, count.

These can be retrieved by the following sample code:

import json
with open(path_to_json_file, 'r') as f:
    json_dict = json.load(f)

for result in json_dict.get("results"):
    media_type = result.get("group").get("mediaType")
    queue_id = result.get("group").get("queueId")
    n_offered = result.get("data")[0].get("metrics")[0].get("count")

If your data and metrics keys will have multiple indices you will have to use a for loop to retrieve every count value accordingly.

edited Jan 17 '22 at 12:27

answered Jan 17 '22 at 12:22

pugi

323
1
12

Perfect, this worked for me. Had to change it to s = json_dict d = json.loads(s) for results in d.get("results"): media_type = results.get("group").get("mediaType") queue_id = results.get("group").get("queueId") n_offered = results.get("data")[0].get("metrics")[0].get("stats").get("count") – titan31 Jan 17 '22 at 14:24

edvard · Answer 2 · 2022-01-17T12:33:52.750

Assuming that the format of the API response is always the same, have you considered hardcoding the extraction of the data you want?

This should work: With response defined as the API output:

response =     {
   "results":[
      {
          "group":{
            "mediaType":"chat",
            "queueId":"67d9fb5e-26b2-4db5-b062-bbcfa8d2ca0d"
          },
          "data":[
            {
               "interval":"2021-01-14T13:12:19.000Z/2022-01-14T13:12:19.000Z",
               "metrics":[
                  {
                     "metric":"nOffered",
                     "qualifier":'null',
                     "stats":{
                        "max":'null',
                        "min":'null',
                        "count":14,
                        "count_negative":'null',
                        "count_positive":'null',
                        "sum":'null',
                        "current":'null',
                        "ratio":'null',
                        "numerator":'null',
                        "denominator":'null',
                        "target":'null'
                     }
                  }
               ],
               "views":'null'
            }
         ]
      }
   ]
}

You can extract the results as follows:

results = response["results"][0]

{
    "mediaType": results["group"]["mediaType"],
    "queueId": results["group"]["queueId"],
    "nOffered": results["data"][0]["metrics"][0]["stats"]["count"]
}

which gives

{
    'mediaType': 'chat',
    'queueId': '67d9fb5e-26b2-4db5-b062-bbcfa8d2ca0d',
    'nOffered': 14
}

That worked when I tested it, but not scalable for what I'm trying to do so went with the other solution. Thanks — titan31, Jan 17 '22 at 14:25

Flatten Nested JSON in Python

2 Answers2

Linked