SELECT knowledge FROM sql_resources WHERE category='bigquery-standard-sql' AND slug='unnest'

UNNEST

Definition

The UNNEST function takes an ARRAY and returns a table with a row for each element in the ARRAY.

unnest_column
1
2
2
5
null

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.

unnest_column	offset
1	0
2	1
2	2
5	3
null	4

Practical Info

UNNEST requires an ARRAY as an input but can return a table of any structure.
Empty arrays and 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:

UNNEST(ARRAY) [WITH OFFSET]

fruit	number
apples	4
pears	6
bananas	2

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.

fruit	basket	fruit_unnest
bananas,apples,oranges	basket 1	bananas
bananas,apples,oranges	basket 1	apples
bananas,apples,oranges	basket 1	oranges
bananas,oranges	basket 2	bananas
bananas,oranges	basket 2	oranges
bananas,apples	basket 3	bananas
bananas,apples	basket 3	apples

SELECT
  *
FROM
  UNNEST([1, 2, 2, 5, NULL]) AS unnest_column

Loading code...

SELECT
  *
FROM
  UNNEST([1, 2, 2, 5, NULL]) AS unnest_column WITH OFFSET AS `offset`

Loading code...

SELECT
  *
FROM
  UNNEST(
       ARRAY<STRUCT<fruit STRING , number INT64>>[
         ('apples', 4), ('pears', 6), ('bananas', 2)
       ]
  )
AS simple_table;

Loading code...

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

Loading code...

UNNEST

Definition

Practical Info

Common Questions

How can I quickly generate a table of values in BigQuery?

How do I join each element in an array column to its corresponding row?

Related Pages