How to extract fields from nested json and save in a data structure

user_11391453 0
Viewed 277

Using python, I am trying to extract fields of the name "first", "last", and "zipcode" and their respective values from JSON where the structure is not always known. An example of the JSON could look something like this:

{
"employees": [
    {
        "first": "Alice",
        "last_name": "Alast",
        "zipcode": "12345",
        "role": "dev",
        "nbr": 1,
        "team": [
            {
                "first_name": "fn",
                "last_name": "ln"
            },
            {
                "first_name": "fn2",
                "last_name": "ln2"
            }
        ]
    },
    {
        "name": "Bob",
        "role": "dev",
        "nbr": 2
    }
],
"firm": {
    "last_name": "Lhans",
    "zipcode": "67890",
    "location": "CA"
}}

In addition to this, I want to save this in a data structure, such as:

{ 
  {
    first: "firstname",
    last: "lastname",
    zipcode: "zipcode"
  }
}

I have tried flattening the nested JSON, basing my function off this. I can get the fields this way, but am having difficulty finding an optimal way to save this data in the format the model mentioned above. If one of the fields are empty, I want to fill that field in as NaN or an empty string, rather than ignoring it completely. Here's what I have so far, which creates a list fields and values, but if the field does not exist, it skips it instead of filling it with a none value.

def flatten_json(nested_json, fields: list):
    out = []
    
    def flatten(x, name=''):
            if type(x) is dict:
                for a in x:
                    flatten(x[a], a)
            elif type(x) is list:
                i = 0
                for a in x:
                    flatten(a)
                    i += 1
            elif name in fields:
                out.append(name+": "+x)
    flatten(nested_json)
    return out

This gives me something like:

['first: Alice', 'last: Jones', 'zipcode: 12345', 'first: fn1', 'last: ln1', 'first: fn2', 'last: ln2', 'last: ln3', 'zipcode: 67890']

Which isn't ideal. I'd rather have any missing fields filled with NaN or an empty string rather than not exist in the list.

data-structures nested python json
1 Answers

I've modified your function to capture the list of dictionaries. The dictionary will only contain the fields specified in the fields list as keys.


import pandas as pd


def flatten_json(nested_json, fields):
    out = []
    temp = {}

    def flatten(x, name=''):
        nonlocal temp
        if type(x) is dict:
            temp = {}
            for a in x:
                flatten(x[a], a)
        elif type(x) is list:
            for i, a in enumerate(x):
                flatten(a)
                i += 1
        elif name in fields:
            temp[name] = x
            out.append(temp)
    flatten(nested_json)
    return out


json1 = {"employees": [{"first": "Alice", "last_name": "Alast", "zipcode": "12345", "role": "dev", "nbr": 1, "team": [{"first_name": "fn", "last_name": "ln"}, {
    "first_name": "fn2", "last_name": "ln2"}]}, {"name": "Bob", "role": "dev", "nbr": 2}], "firm": {"last_name": "Lhans", "zipcode": "67890", "location": "CA"}}

fields = ['first_name', 'last_name', 'zipcode']
result = (flatten_json(json1, fields))

The output of the above function can then be loaded into pandas dataframe -

df = pd.DataFrame(result)
df.drop_duplicates(inplace=True)
print(df)

which will give output like this -

  last_name zipcode first_name
0     Alast   12345        NaN
2        ln     NaN         fn
4       ln2     NaN        fn2
6     Lhans   67890        NaN

Now, to get the data back in JSON format you can convert the dataframe back to dict using to_dict() function -

print(df.to_dict(orient='records'))

output-

[{'first_name': nan, 'last_name': 'Alast', 'zipcode': '12345'},
 {'first_name': 'fn', 'last_name': 'ln', 'zipcode': nan},
 {'first_name': 'fn2', 'last_name': 'ln2', 'zipcode': nan},
 {'first_name': nan, 'last_name': 'Lhans', 'zipcode': '67890'}]
user_154380330
Related