Flattening Arrays with UNNEST

Concepts covered: Array Flattening, Lateral Joins, Join Cardinality, JSON Extraction, Arrays, Existence Checks

The flatten step rarely announces itself. "Top 10 SKUs by units sold this week." "Average line price across all orders." "How many orders contain a SKU from the clearance list?" None of these says array, yet each needs the items array inside every order's JSON turned into rows before an aggregate can reach it. The strong answer writes that flatten as its own named step, then builds the business logic on top of it.

What one output row means

UNNEST takes an array and returns a table with one row per element. Written in FROM after the parent table, it runs once for each parent row and joins its output back to that row, so every element row carries a copy of the parent's columns. The join is lateral: the function on the right reads a column from the row on the left, which is why it needs no join condition. The running example is orders_raw, 4 orders that each have a shipping_fee column and a payload whose items array holds {sku, qty, price} objects.

-- Trino: one row per order line
SELECT
  o.order_id,
  o.shipping_fee,
  i.line_no,
  JSON_EXTRACT_SCALAR(i.item, '$.sku')                  AS sku,
  CAST(JSON_EXTRACT_SCALAR(i.item, '$.qty') AS INTEGER) AS qty
FROM orders_raw AS o
CROSS JOIN UNNEST(
  CAST(JSON_EXTRACT(o.payload, '$.items') AS ARRAY(JSON))
) WITH ORDINALITY AS i(item, line_no)
ORDER BY o.order_id, i.line_no;

Four orders went in and 6 rows came out: 3 for 1001, 1 for 1002, none for 1003 and 2 for 1004. The result's row count is the sum of the array lengths, and the grain has moved from one row per order to one row per order line. shipping_fee now repeats on every line of its order, which does no harm while it is only displayed and becomes a bug the moment a later step adds it up.

The same move in every engine

The keyword changes and the operation does not. Calling it "a lateral join that fans each order out by its item count" lands on whichever engine the interviewer runs, and it shows you know what the syntax does rather than which keyword spells it. Watch the position base when translating: offset 0 in BigQuery is ordinality 1 in Trino, and an off-by-one there misaligns every later join on position.

Two arrays on one row

The follow-up that separates people who have flattened real payloads: each order also carries a promos array, and the answer needs both. Two independent lateral joins multiply, so an order with 3 items and 2 promos becomes 6 rows, every item paired with every promo. That shape is right only when the question is about pairs, and any sum of qty over it has silently doubled for that order.

Parallel arrays: zip them

• Element 1 of skus belongs with element 1 of qtys
• Trino and Postgres take both in one UNNEST(skus, qtys) and pad the shorter with NULL
• BigQuery unnests each WITH OFFSET and joins on equal offsets
• Rows out equal the longer array's length

Unrelated arrays: keep them apart

• items and promos describe different things
• Flatten each one in its own CTE
• Aggregate each back to one row per order
• Join the 2 results on order_id

Counting parents after the flatten

The clearance-list disguise hides a second trap. After the flatten, COUNT(*) behind a clearance filter counts lines, so an order holding 2 clearance SKUs is counted twice. Counting orders needs COUNT(DISTINCT o.order_id), or an EXISTS over the unnested array, which never changes the grain at all. The EXISTS form is the one worth naming: it can stop at the first matching element of each order and returns exactly one verdict per order by construction.

-- Trino: orders holding at least one clearance SKU, grain unchanged
SELECT COUNT(*) AS orders_with_clearance
FROM orders_raw AS o
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(CAST(JSON_EXTRACT(o.payload, '$.items') AS ARRAY(JSON))) AS i(item)
  WHERE JSON_EXTRACT_SCALAR(i.item, '$.sku') IN (SELECT sku FROM clearance_skus)
);

About this lesson

This section is part of the Semi-Structured Data: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.