LATERAL, UNNEST, and generate_series

Three SQL features that turn row-oriented data into something more useful for analytics: UNNEST flattens arrays, LATERAL lets you reference earlier FROM items in a subquery, and generate_series emits a virtual sequence.

UNNEST — flatten an array column

-- 'tags' is a list<utf8> column. UNNEST expands one row per element.
SELECT user_id, tag
FROM users
CROSS JOIN UNNEST(users.tags) AS t(tag);

Rows whose tags column is NULL are dropped (use CROSS JOIN UNNEST(...) with a default if you want them).

LATERAL — correlated subquery in FROM

LATERAL lets the subquery on the right reference columns from the left side. The most common use is "for each row, compute something from that row":

-- For each user, the top 3 orders by amount
SELECT u.id, o.amount, o.ts
FROM users u
CROSS JOIN LATERAL (
  SELECT amount, ts
  FROM orders
  WHERE orders.user_id = u.id
  ORDER BY amount DESC
  LIMIT 3
) o;

Equivalent to a correlated subquery in the SELECT list, but cheaper when you need to return multiple rows per outer row.

generate_series — virtual sequence

SELECT generate_series(TIMESTAMP '2024-01-01', TIMESTAMP '2024-01-31', INTERVAL '1 day') AS day;

Three forms:

Form	Emits
generate_series(start, stop)	Integers from start to stop, step 1.
generate_series(start, stop, step)	Integers from start to stop, step step.
generate_series(start, stop, interval)	Timestamps from start to stop, step interval.

Use for filling missing time buckets:

WITH days AS (
  SELECT generate_series(DATE '2024-01-01', DATE '2024-01-31', INTERVAL '1 day') AS day
)
SELECT
  d.day,
  COALESCE(SUM(o.amount), 0) AS total
FROM days d
LEFT JOIN orders o ON DATE_TRUNC('day', o.ts) = d.day
GROUP BY d.day
ORDER BY d.day;

UNNEST on structs

Unnest a struct column by naming its fields:

-- 'address' is a struct<street VARCHAR, city VARCHAR, zip VARCHAR>
SELECT user_id, addr.street, addr.city, addr.zip
FROM users
CROSS JOIN UNNEST(users.address) AS a(street, city, zip);

See also

• Recursive CTE
• Window Functions