Flattening nested JSON for SQL: the three decisions that matter

"Flatten this JSON into a table" sounds like a formatting problem. It isn't. It's three modelling decisions wearing a trench coat, and the reason flattening code gets rewritten so often is that people make those decisions implicitly the first time.

Take an order payload — the shape almost every commerce or SaaS integration produces:

{
  "order": {
    "id": "ord_8f2c1a4b9e07",
    "customer": { "email": "ada@example.com" },
    "discount_code": null,
    "line_items": [
      { "sku": "TSHIRT-BLK-M", "quantity": 2, "price_cents": 1899 },
      { "sku": "MUG-01",       "quantity": 1, "price_cents": "2500" }
    ]
  }
}

Two structural features do all the damage: customer is an object, and line_items is an array. Objects are easy. Arrays are the whole problem.

Decision 1: what does one row mean?

An object nests without changing the row count — order.customer.email is just a column with a longer name. An array doesn't have that luxury. Two line items means either two rows or one row that has somehow squashed them together, and you have to pick.

Explode and one row becomes one line item. Order-level fields repeat down the rows. This is right when the array elements are the thing you care about — you're asking "what's the average line-item price?" It's wrong the moment someone sums order.total_cents across that table and gets a number inflated by however many items each order had. That bug ships to a dashboard roughly once per company.

Aggregate and one row stays one order, with the array collapsed into summary columns: line_item_count, total_quantity, distinct_skus. Safe for order-level analysis, and you've thrown away the detail.

Both — a table per grain, joined on the order id — is usually the right answer, and it's what a dimensional modeller would have done without thinking about it. One orders table, one order_line_items table. The cost is two objects to maintain; the benefit is that nobody can accidentally sum across the wrong grain.

The rule that keeps you out of trouble: never put two independent arrays in one flattened table. If an order has both line_items and refunds, exploding both produces a cartesian product — three items and two refunds becomes six rows, and every numeric column is now triple- or double-counted. There is essentially no query for which that table is the correct input.

Decision 2: what happens to empty arrays?

This one is quiet and it bites during reconciliation. An order with "line_items": [] — or with the key missing entirely — has nothing to explode. Depending on the syntax you reach for, that order either disappears from the output or survives as a row with NULLs where the item fields should be.

Most naive flattening drops it. In DuckDB, unnest() in a SELECT list behaves like an inner join: no elements, no rows. Then someone counts orders in the flattened table, compares to the source, finds 40 missing, and spends an afternoon on it.

Keep them with an explicit left join:

-- DuckDB — keeps orders with empty or NULL line_items
SELECT
  o."order"."id"            AS order__id,
  li."sku"                  AS line_items__sku,
  li."quantity"             AS line_items__quantity
FROM source AS o
LEFT JOIN LATERAL unnest(o."order"."line_items") AS t(li) ON TRUE;

-- Snowflake — OUTER => TRUE is the equivalent switch
SELECT
  o.payload:order:id::string        AS order__id,
  li.value:sku::string              AS line_items__sku,
  li.value:quantity::number         AS line_items__quantity
FROM source o,
LATERAL FLATTEN(input => o.payload:order:line_items, OUTER => TRUE) li;

-- BigQuery — LEFT JOIN UNNEST, not comma-join
SELECT
  o.order.id                        AS order__id,
  li.sku                            AS line_items__sku,
  li.quantity                       AS line_items__quantity
FROM source AS o
LEFT JOIN UNNEST(o.order.line_items) AS li;

Whichever you choose, decide deliberately and write it down, because the two versions differ only by a keyword and produce different row counts. Reviewers will not catch it.

Decision 3: what do you call the columns?

Nested paths have to collapse into flat names, and the obvious separator is a double underscore: order.customer.email becomes order__customer__email. Fine — until a source has both a customer.email object field and a top-level customer_email, and both want the same column.

It's rarer than the grain problem but harder to notice, because the second one silently overwrites the first in most naive implementations. If you're generating flattening code, check for collisions before you emit it. If you're writing it by hand, prefer keeping the full path even when it's verbose — order__customer__email is ugly and unambiguous, which is the right trade for a physical layer nobody reads directly.

Related: strip the [] marker from array paths, but keep the element fields namespaced under the array name. line_items[].sku → line_items__sku tells the next reader which array the column came from, which matters once there are two.

The thing flattening will expose

Look at the sample again: one price_cents is 1899 and the other is the string "2500". In JSON that's legal and invisible. In a typed column it's a decision — and the decision gets made by whatever inferred the schema, usually without telling you.

Some engines widen the column to VARCHAR, and your numeric aggregations silently start failing or returning garbage. Some coerce and succeed. Some coerce and drop the ones that don't parse. DuckDB's read_json_auto will generally widen to VARCHAR if it sees both in its sample — and if the string values are rare enough to fall outside the sample, it types the column as BIGINT and then errors on the full scan.

This is the single most common nasty surprise in JSON ingestion, and it has nothing to do with your SQL. It's worth checking the type consistency of every leaf before you write the flattening query, because the answer changes what you write: try_cast and a quarantine column if it's messy, a plain cast if it's clean.

When not to flatten at all

Modern warehouses all have native JSON types with path extraction, and "land the raw payload, extract on read" is a legitimate architecture. It's the better one when the schema is still moving, when you only query a handful of fields, or when you need the original bytes for replay and audit.

Flatten when you have downstream consumers who write SQL and shouldn't need to learn your payload's shape, when query performance on those fields matters, or when you want column-level tooling — data-quality checks, lineage, column-level access control — to see the fields at all. Most data-quality tools are strictly column-oriented, so anything still inside a JSON blob is invisible to them.

A common middle path: keep the raw payload column forever, build flattened views on top, and rebuild the views when the schema moves. You get replayability and ergonomics, and the views are cheap to change because they hold no state.

A checklist

• One grain per table. One array per table. No exceptions worth taking.
• Decide explicitly whether records with empty arrays survive, and encode it in the join type.
• Check for column-name collisions before generating names.
• Check type consistency per leaf first — a field that's usually a number and sometimes a string changes what the cast should be.
• Keep the raw payload. Views are cheap to rebuild; re-ingesting six months of webhooks isn't.