SQL Resources
SQL
UNNEST

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
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.

SELECT
  *
FROM
  UNNEST([1, 2, 2, 5, NULL]) AS unnest_column WITH OFFSET AS `offset`
unnest_columnoffset
10
21
22
53
null4

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:

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

    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
    fruitbasketfruit_unnest
    bananas,apples,orangesbasket 1bananas
    bananas,apples,orangesbasket 1apples
    bananas,apples,orangesbasket 1oranges
    bananas,orangesbasket 2bananas
    bananas,orangesbasket 2oranges
    bananas,applesbasket 3bananas
    bananas,applesbasket 3apples

    Related Pages

  • ARRAY_AGG
  • Subscribe to newsletter

    Subscribe to receive the latest blog posts to your inbox every week.

    By subscribing you agree to our Privacy Policy.
    Thank you! Your submission has been received!
    Oops! Something went wrong while submitting the form.

    Start solving your organization's biggest problems with Count today.

    Stay up to date with all things count, data and problem solving.