UNNEST in BigQuery

UNNEST turns an array into rows. A comma or CROSS JOIN with UNNEST drops rows whose array is empty, LEFT JOIN keeps them, and WITH OFFSET keeps the element order.

UNNEST takes an array and returns a table with one row per element. You use it in FROM to flatten an array column into rows, and in subqueries to search or sum inside an array without flattening the whole table. BigQuery stores repeated data as arrays, so UNNEST is how you query it.

SELECT fruit
FROM UNNEST(['apple', 'pear', 'plum']) AS fruit

Flattening an array column

Put UNNEST after a comma in FROM. For each order, BigQuery unnests that order’s own items and pairs each item with its order. The comma is a CROSS JOIN whose right side depends on the current row of the left side.

WITH orders AS (
  SELECT 'o1' AS order_id, ['apple', 'pear'] AS items UNION ALL
  SELECT 'o2', ['plum'] UNION ALL
  SELECT 'o3', CAST([] AS ARRAY<STRING>)
)
SELECT order_id, item
FROM orders, UNNEST(items) AS item
ORDER BY order_id, item

Order o3 has an empty array, and it is gone. A cross join with zero rows produces zero rows, so every row whose array is empty or NULL disappears. This is the most common UNNEST mistake: a count of orders after unnesting is silently lower than the real count.

In Querylab.io, a table’s SQL Templates include “SELECT all columns (unnested)”, which writes the UNNEST joins for the table’s ARRAY columns (DDL operations).

To keep those rows, use LEFT JOIN UNNEST(...). The order stays, with NULL for the item:

WITH orders AS (
  SELECT 'o1' AS order_id, ['apple', 'pear'] AS items UNION ALL
  SELECT 'o2', ['plum'] UNION ALL
  SELECT 'o3', CAST([] AS ARRAY<STRING>)
)
SELECT order_id, item
FROM orders
LEFT JOIN UNNEST(items) AS item
ORDER BY order_id, item

LEFT JOIN UNNEST needs no ON clause, because the array already belongs to the row.

The short form

Inside FROM, you can name the array column directly after the comma, without writing UNNEST. This implicit form behaves exactly like the comma join above:

WITH orders AS (
  SELECT 'o1' AS order_id, ['apple', 'pear'] AS items UNION ALL
  SELECT 'o2', ['plum'] UNION ALL
  SELECT 'o3', CAST([] AS ARRAY<STRING>)
)
SELECT order_id, item
FROM orders, orders.items AS item
ORDER BY order_id, item

Keeping the element order

UNNEST doesn’t guarantee the order of the rows it produces. When the position matters, add WITH OFFSET, which returns each element’s zero-based index as an extra column, and order by it:

SELECT step, position
FROM UNNEST(['open', 'add to cart', 'pay']) AS step WITH OFFSET AS position
ORDER BY position

Arrays of structs

When the elements are structs, give the unnested row an alias and read its fields with a dot:

WITH orders AS (
  SELECT 'o1' AS order_id, [STRUCT('apple' AS sku, 3 AS qty), STRUCT('pear', 1)] AS lines UNION ALL
  SELECT 'o2', [STRUCT('plum', 6)]
)
SELECT order_id, line.sku, line.qty
FROM orders, UNNEST(lines) AS line
ORDER BY order_id, line.sku

SELECT * over an unnested array of structs returns one column per struct field, not a single struct column:

SELECT *
FROM UNNEST([STRUCT('apple' AS sku, 3 AS qty), STRUCT('pear', 1)])

Working inside an array without flattening

Often you don’t want one row per element, only a value computed from the array. A subquery over UNNEST runs once per row, and the table keeps its shape:

WITH orders AS (
  SELECT 'o1' AS order_id, [STRUCT('apple' AS sku, 3 AS qty), STRUCT('pear', 1)] AS lines UNION ALL
  SELECT 'o2', [STRUCT('plum', 6)]
)
SELECT
  order_id,
  (SELECT SUM(qty) FROM UNNEST(lines)) AS total_qty,
  'pear' IN (SELECT sku FROM UNNEST(lines)) AS has_pear
FROM orders
ORDER BY order_id

To test whether an array contains a value, use IN UNNEST:

WITH posts AS (
  SELECT 1 AS post_id, ['sql', 'bigquery'] AS tags UNION ALL
  SELECT 2, ['python'] UNION ALL
  SELECT 3, ['bigquery', 'cost']
)
SELECT post_id
FROM posts
WHERE 'bigquery' IN UNNEST(tags)
ORDER BY post_id

Use EXISTS (SELECT 1 FROM UNNEST(tags) AS tag WHERE tag LIKE 'big%') when the test is more than equality.

Unnesting multiplies the other columns

After a join with UNNEST, every column of the parent row is repeated once per element. Aggregating a parent column after unnesting counts it several times. Order o1 has two items, so its amount of 100 is summed twice:

WITH orders AS (
  SELECT 'o1' AS order_id, 100 AS amount, ['apple', 'pear'] AS items UNION ALL
  SELECT 'o2', 50, ['plum']
)
SELECT SUM(amount) AS total_amount
FROM orders, UNNEST(items) AS item

Aggregate parent columns before unnesting, or compute array values in a subquery as shown above.

NULL and empty arrays

Unnesting a NULL array produces no rows, the same as an empty array:

SELECT COUNT(*) AS row_count
FROM UNNEST(CAST(NULL AS ARRAY<INT64>))

BigQuery doesn’t distinguish the two in results either: a NULL array column is returned as []. See NULL in BigQuery.

What it costs

Each field of a repeated struct is stored as its own column, and UNNEST lets BigQuery read only the fields you use. The Google Analytics sample stores every hit of a session in a repeated hits struct with many nested fields. Reading the whole table:

SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`

Reading only the hits column costs nearly as much, 13.8 MB of the table’s 15.9 MB:

SELECT hits
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`

Unnesting hits and reading one nested field costs 424.2 KB:

SELECT hit.page.pagePath
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
  UNNEST(hits) AS hit

So unnesting doesn’t make a query more expensive. Selecting the whole array or struct does. See Nested and repeated data and How a query is billed.