UNNEST
Definition
The UNNEST
function takes an ARRAY
and returns a table with a row for each element in the ARRAY
.
Syntax
UNNEST(ARRAY) [WITH OFFSET]
SELECT
*
FROM
UNNEST([1, 2, 2, 5, NULL]) AS unnest_column
The optional WITH OFFSET
clause provides an additional column containing the position of each element in the array (starting at zero) for each row produced by UNNEST
.
SELECT
*
FROM
UNNEST([1, 2, 2, 5, NULL]) AS unnest_column WITH OFFSET AS `offset`
Practical Info
UNNEST
requires an ARRAY
as an input but can return a table of any structure.NULL
as an input returns an empty table. An array containing NULL
values will produce a row of NULL
values.Common Questions
How can I quickly generate a table of values in BigQuery?
You can use UNNEST
to quickly create simple tables from arrays. For example:
SELECT
*
FROM
UNNEST(
ARRAY<STRUCT<fruit STRING , number INT64>>[
('apples', 4), ('pears', 6), ('bananas', 2)
]
)
AS simple_table;
How do I join each element in an array column to its corresponding row?
You can do this with a CROSS JOIN
. A cross join will take every individual element of your unnested array and join it back to its parent row. This will create multiple rows for each element of your array but you can then filter it down.
WITH fruit_baskets as
(
SELECT
*
FROM
UNNEST(
ARRAY<STRUCT<fruit ARRAY<STRING>,basket STRING>>[
(['bananas', 'apples', 'oranges'], 'basket 1'),
(['bananas', 'oranges'], 'basket 2'),
(['bananas', 'apples'], 'basket 3')
]
)
AS fruit
)
SELECT *
FROM fruit_baskets
CROSS JOIN UNNEST(fruit) as fruit_unnest