Cohort 4Register

P7 Flatten nested JSON

FreeLesson 60 of 78

P7Flatten nested JSONDEHARD

Sounds like: "flatten this API payload", "turn nested JSON into table rows", "explode the items array"

Worked question: Flatten customer records with a nested address and a tags list into flat columns, then explode an order's items into one row each.

Approach: 1. recurse into dicts and lists 2. build dotted keys 3. decide: lists become columns, or rows?

RECURSIVE FLATTENPYTHON 3.12
import json

def flatten(obj, parent="", sep="."):
    items = {}
    if isinstance(obj, dict) and obj:
        for k, v in obj.items():
            key = f"{parent}{sep}{k}" if parent else k
            items.update(flatten(v, key, sep))
    elif isinstance(obj, list) and obj:
        for i, v in enumerate(obj):
            key = f"{parent}{sep}{i}" if parent else str(i)
            items.update(flatten(v, key, sep))
    else:
        items[parent] = obj        # leaf, or empty dict/list
    return items

customers = json.load(open("customers.json"))
print(flatten(customers[1]))
print(flatten(customers[5]))

Output

{'customer_id': 2, 'name': 'Diya', 'signup_date': '2025-01-12', 'referred_by': 1, 'address.city': 'Mumbai', 'address.pincode': '400001', 'tags.0': 'referral'}
{'customer_id': 6, 'name': 'Sana', 'signup_date': '2025-03-15', 'referred_by': None, 'address.city': None, 'address.pincode': None, 'tags': []}
EXPLODE A LIST INTO ROWSPYTHON 3.12
order = {"order_id": 114,
         "customer": {"id": 1, "city": "Bengaluru"},
         "items": [{"sku": 4, "qty": 1}, {"sku": 1, "qty": 1},
                   {"sku": 5, "qty": 1}]}

def explode(record, list_key):
    parent = {k: v for k, v in record.items() if k != list_key}
    base = flatten(parent)
    for child in record.get(list_key) or [{}]:   # keep empty
        kids = {f"{list_key}.{k}": v for k, v in child.items()}
        yield {**base, **kids}

for row in explode(order, "items"):
    print(row)

Output

{'order_id': 114, 'customer.id': 1, 'customer.city': 'Bengaluru', 'items.sku': 4, 'items.qty': 1}
{'order_id': 114, 'customer.id': 1, 'customer.city': 'Bengaluru', 'items.sku': 1, 'items.qty': 1}
{'order_id': 114, 'customer.id': 1, 'customer.city': 'Bengaluru', 'items.sku': 5, 'items.qty': 1}
THE PANDAS SHORTCUTPYTHON 3.12
import json
import pandas as pd

events = [json.loads(l) for l in open("events.jsonl")]
df = pd.json_normalize(events)
print(df.columns.tolist())
print(df.head(3).to_string(index=False))

Output

['user_id', 'ts', 'event', 'props.page', 'props.device']
 user_id                  ts    event props.page props.device
       1 2025-04-01T10:00:00     view       /p/4       mobile
       1 2025-04-01T10:05:00     cart      /cart       mobile
       1 2025-04-01T10:20:00 checkout  /checkout       mobile

pd.json_normalize does the dict part for you and record_path does

the explode. In the interview, write the recursive version first: that is what they are testing.

Practice before you move on

  1. Solve the practice question for P7 in Coding Practice, under Python.
  2. Flatten every customer in customers.json and print the column names you end up with.

In Coding Practice

Solve this pattern on fresh data, under Python. Hidden tests grade it.