TopolearnRepeated fields are everywhere in BigQuery data, including GA4 and Firebase exports, and UNNEST is how you query them.
An ARRAY<T> holds an ordered list of values of one type, like ['red', 'blue'] or [1, 2, 3]. Arrays can't directly contain arrays (wrap an inner array in a STRUCT) and can't hold NULL elements in stored results.
UNNEST turns an array into a table with one row per element. Joining a table to UNNEST of its own array column flattens it:
SELECT o.id, item FROM orders AS o, UNNEST(o.items) AS itemThe comma is a CROSS JOIN, a correlated one, so each order joins only to its own items. Orders with an empty or NULL array disappear, exactly like an inner join; write LEFT JOIN UNNEST(o.items) AS item to keep them with a NULL item. WITH OFFSET AS pos adds each element's 0-based position.
Other array tools:
- Index with
arr[OFFSET(0)](0-based) orarr[ORDINAL(1)](1-based); these error when out of range, whileSAFE_OFFSETreturns NULL. ARRAY_LENGTH(arr),ARRAY_TO_STRING(arr, ','),GENERATE_ARRAY(1, 10),GENERATE_DATE_ARRAY(start, end).value IN UNNEST(arr)tests membership.ARRAY_AGG(x ORDER BY y LIMIT 3)builds an array from rows.ARRAY(SELECT ... FROM UNNEST(arr) ...)transforms an array without changing the row count.
Filtering on an element without changing the grain is a subquery: WHERE EXISTS (SELECT 1 FROM UNNEST(tags) t WHERE t = 'sale').
Example
WITH orders AS (
SELECT 1 AS id, ['pen', 'ink'] AS items UNION ALL
SELECT 2, [] UNION ALL
SELECT 3, ['pad']
)
SELECT o.id, item, pos
FROM orders AS o
LEFT JOIN UNNEST(o.items) AS item WITH OFFSET AS pos
ORDER BY o.id, pos;
-- 1 | pen | 0
-- 1 | ink | 1
-- 2 | NULL | NULL (kept thanks to LEFT JOIN)
-- 3 | pad | 0
SELECT
ARRAY_LENGTH(['a', 'b', 'c']) AS len, -- 3
['a', 'b', 'c'][OFFSET(1)] AS second, -- b
['a'][SAFE_OFFSET(5)] AS missing, -- NULL
'b' IN UNNEST(['a', 'b']) AS has_b, -- true
ARRAY(SELECT x * 2 FROM UNNEST([1, 2, 3]) AS x) AS doubled; -- [2, 4, 6]Remember
- FROM t, UNNEST(t.arr) AS x flattens an array, one row per element
- Use LEFT JOIN UNNEST to keep rows whose array is empty
- OFFSET is 0-based, ORDINAL 1-based; SAFE_ variants return NULL
- ARRAY_AGG builds arrays; ARRAY(SELECT ...) transforms them
Watch out for
- Counting orders after a comma-UNNEST and losing the orders that have no items.