Flatten nested JSON

Flatten nested JSON by selecting struct fields with dot notation and exploding arrays into rows. Do it step by step, and watch the row count as you go.

Example structure

{ "order_id": 1,
  "customer": { "id": 7, "address": { "city": "Pune" } },
  "items": [ {"sku": "A", "qty": 2}, {"sku": "B", "qty": 1} ] }

Structs: dot notation

flat = df.select(
    "order_id",
    F.col("customer.id").alias("customer_id"),
    F.col("customer.address.city").alias("city"),
    "items",
)

You can also expand every field of a struct at once with select("customer.*").

Arrays: explode

items = flat.select("order_id", "customer_id", "city", F.explode("items").alias("item"))
items = items.select("order_id", "customer_id", "city", "item.sku", "item.qty")

explode makes one row per element. The order with two items becomes two rows, and the order-level columns repeat. Use explode_outer if rows with an empty or NULL array must stay, with NULL in the item columns. inline("items") expands an array of structs straight into columns.

Unknown depth

If the structure varies, write a small recursive function over df.schema: for each field, if it is a StructType select its children with a prefix, and if it is an ArrayType of structs explode it and continue until no complex types remain. Keep the aliases unique, for example customer_address_city.

def flatten_structs(df):
    cols = []
    for f in df.schema.fields:
        if isinstance(f.dataType, StructType):
            cols += [F.col(f"{f.name}.{c.name}").alias(f"{f.name}_{c.name}") for c in f.dataType.fields]
        else:
            cols.append(F.col(f.name))
    return df.select(cols)

Call it repeatedly until no struct column remains.

Watch for row multiplication

If you explode two independent arrays in the same table, such as items and payments, you get a cross product: 3 items times 2 payments gives 6 rows per order. Explode each array in its own DataFrame and keep order_id as the link. Also remember that order-level amounts repeat after an explode, so summing them double counts.