SQL - UNNEST

Updated: 2020-06-29

expand an ARRAY into a relation(i.e. table)

  • Presto: supports ARRAY to 1 column and MAP to 2 columns table
  • BigQuery: only supports ARRAY to 1 column table
SELECT ARRAY[1, 2, 3]

Result is one row

f0_
---
[1, 2, 3]

Use UNNEST

SELECT * FROM UNNEST(ARRAY[1, 2, 3])

The result is a table of one column

f0_
---
1
2
3

Give it a name:

SELECT a FROM UNNEST(ARRAY[1, 2]) AS a
a
---
1
2
3

Equivalent to a map function:

SELECT a*a FROM UNNEST(ARRAY[1, 2, 3]) AS a
a
---
1
4
9
SELECT ARRAY(SELECT a*a FROM UNNEST(ARRAY[1, 2, 3]) AS a)
f0_
---
[1,4,9]
SELECT ARRAY_TO_STRING(
  ARRAY(SELECT CAST(a * a AS STRING) FROM UNNEST(ARRAY[1, 2, 3]) AS a), ","
)
f0_
---
1,4,9