Difficulty: Hard | Concepts: json_normalize, Nested JSON, record_path, meta, ETL, Semi-Structured Data

Flattening nested JSON in Python is a task almost every data engineer runs into when pulling data from REST APIs, event logs, or NoSQL sources like MongoDB — and it's a very practical Python interview question for ETL and data engineering roles, since real API responses are rarely flat. Getting this wrong usually means silently losing data buried inside nested arrays.

Flatten Nested JSON in Pandas — Problem Statement

You receive a JSON payload from an orders API. Each order has top-level fields, a nested customer object, and a nested list of items (each with its own fields). Convert this nested JSON structure into a flat Pandas DataFrame suitable for loading into a data warehouse table, with one row per order item.

Sample Nested JSON

import pandas as pd

data = [
    {
        "order_id": 101,
        "order_date": "2026-06-01",
        "customer": {"id": 501, "name": "Riya Shah", "city": "Bengaluru"},
        "items": [
            {"sku": "SKU-1", "qty": 2, "price": 250},
            {"sku": "SKU-2", "qty": 1, "price": 900}
        ]
    },
    {
        "order_id": 102,
        "order_date": "2026-06-02",
        "customer": {"id": 502, "name": "Aman Verma", "city": "Pune"},
        "items": [
            {"sku": "SKU-3", "qty": 3, "price": 150}
        ]
    }
]

Expected Output

order_id	order_date	customer.id	customer.name	customer.city	sku	qty	price
101	2026-06-01	501	Riya Shah	Bengaluru	SKU-1	2	250
101	2026-06-01	501	Riya Shah	Bengaluru	SKU-2	1	900
102	2026-06-02	502	Aman Verma	Pune	SKU-3	3	150

Solution — Using pandas.json_normalize with record_path and meta

flat_df = pd.json_normalize(
    data,
    record_path="items",
    meta=["order_id", "order_date", ["customer", "id"], ["customer", "name"], ["customer", "city"]],
    meta_prefix="",
    sep="."
)

# Reorder columns for readability
flat_df = flat_df[[
    "order_id", "order_date", "customer.id", "customer.name",
    "customer.city", "sku", "qty", "price"
]]

print(flat_df)

Explanation

The core of this JSON to DataFrame conversion is record_path, which tells json_normalize which nested list to explode into rows — here, items. The meta parameter then pulls in the surrounding order-level and customer-level fields and repeats them on every exploded row, which is exactly the "one row per line item" shape a data warehouse fact table needs. Nested dictionary fields like customer.id are referenced as a path list ["customer", "id"], and sep="." controls how those nested column names get flattened — matching the dot notation warehouse teams commonly expect.

For JSON that's nested even deeper (arrays inside arrays), a single json_normalize call won't fully flatten it — in that case it's applied iteratively, one nesting level at a time, until every column is a scalar type your target table can store.

Visual Representation

order_id: 101 | +-- customer: {id, name, city} -> repeated on every child row | +-- items: [ {sku, qty, price}, {sku, qty, price} ] | | becomes row 1 becomes row 2 Result: nested tree structure -> flat table, one row per item, order & customer fields duplicated across matching rows

Conclusion

Knowing how to flatten nested JSON for a Pandas DataFrame using record_path and meta — instead of writing manual nested loops — is a strong signal in a Python data engineering interview that you can handle real API and event-log data cleanly. This same pattern extends directly to parsing JSON API responses in Python for ETL pipelines feeding into Snowflake, BigQuery, or any relational warehouse.