The UNNEST operator expands an ARRAY expression into one output row per element. It works with both plain arrays and ARRAY<STRUCT> types. UNNEST can run standalone or combined with JOIN to expand an array column from a table.
Expand an array with UNNEST
UNNEST turns an array expression into a row set — one row per element. Use it in the FROM clause, just like a table reference.
Syntax
SELECT ... FROM UNNEST (<expression>) [AS <alias>];Parameters
expression: Required. An ARRAY expression to expand.alias: Optional. A name for the expanded column. Without an alias, the system auto-generates a column name such as__generated_unnest_col_c0.
Examples
The following examples show how the auto-generated column name compares to a user-defined alias.
SELECT * FROM unnest(array(1,2,3));
-- Result: system generates the column name
+---------------------------+
| __generated_unnest_col_c0 |
+---------------------------+
| 1 |
| 2 |
| 3 |
+---------------------------+
SELECT * FROM unnest(array(1,2,3)) t;
-- Result: alias "t" is used as the column name
+------+
| t |
+------+
| 1 |
| 2 |
| 3 |
+------+Expand a STRUCT array with UNNEST
When the input is an ARRAY<STRUCT>, UNNEST expands the top-level fields of each STRUCT into separate columns. Nested STRUCTs are not expanded further — only the outermost STRUCT fields become columns.
Syntax
SELECT ... FROM UNNEST (<expression>) [AS <alias>];Parameters
expression: Required. An ARRAY<STRUCT> expression to expand.alias: Optional. A name for the expanded STRUCT. Reference individual fields using<alias>.fieldnotation, or use field names directly without the alias prefix.
Examples
Example 1: Basic STRUCT expansion — fields become columns
SELECT * FROM UNNEST(ARRAY(STRUCT(1 AS a, 2 AS b), STRUCT(3 AS a, 4 AS b)));
-- Result: 2 rows, each STRUCT field becomes a column
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
SELECT *, t FROM UNNEST(ARRAY(STRUCT(1 AS a, 2 AS b), STRUCT(3 AS a, 4 AS b))) t;
-- Result: alias "t" adds a column containing the full STRUCT value
+------+------+------------+
| a | b | t |
+------+------+------------+
| 1 | 2 | {a:1, b:2} |
| 3 | 4 | {a:3, b:4} |
+------+------+------------+Example 2: Access STRUCT fields by name or via the <alias>.field notation
SELECT a, t.a FROM UNNEST(ARRAY(STRUCT(1 AS a, 2 AS b), STRUCT(3 AS a, 4 AS b))) t;
-- Result: "a" and "t.a" resolve to the same field; the duplicate is renamed "a_1"
+------+------+
| a | a_1 |
+------+------+
| 1 | 1 |
| 3 | 3 |
+------+------+Example 3: Nested STRUCTs — only the top-level fields are expanded
UNNEST expands only the outermost fields. A nested STRUCT appears as a single column containing the full STRUCT value, not as individual columns.
SELECT * FROM UNNEST(ARRAY(STRUCT(STRUCT(1 AS a1, 2 AS a2) AS a, 3 AS b), STRUCT(STRUCT(4 AS a1, 5 AS a2) AS a, 6 AS b))) t;
-- Result: "a" is a nested STRUCT and stays as a single column; only "b" is a plain value
+--------------+------+
| a | b |
+--------------+------+
| {a1:1, a2:2} | 3 |
| {a1:4, a2:5} | 6 |
+--------------+------+Use UNNEST with JOIN
UNNEST and JOIN are both FROM clause constructs and can be combined. Place UNNEST on the right side of a JOIN and pass an ARRAY column from the left table — UNNEST expands that column laterally, producing one output row per array element.
Syntax
SELECT ... FROM ... JOIN UNNEST (<expression>) [AS <alias>] [ON <join_condition>]Parameters
expression: Required. An ARRAY column from the left-side table in the JOIN.alias: Optional. A name for the expanded element.join_condition: Optional. One or more equality expressions that filter the join results. Format:ON equality_expression [AND equality_expression]...
Examples
Example 1: Expand a plain integer array from a table column
The referenced column must be an ARRAY type. Without an alias, the expanded column gets an auto-generated name; with an alias, use it to refer to each element.
WITH t AS (SELECT * FROM VALUES (ARRAY(1,2,3)) t(a))
SELECT * FROM t JOIN UNNEST(t.a);
-- Result: 3 rows — one per element; column name is auto-generated
+-----------+---------------------------+
| a | __generated_unnest_col_c0 |
+-----------+---------------------------+
| [1, 2, 3] | 1 |
| [1, 2, 3] | 2 |
| [1, 2, 3] | 3 |
+-----------+---------------------------+
WITH t AS (SELECT * FROM VALUES (ARRAY(1,2,3)) t(a))
SELECT * FROM t JOIN UNNEST(t.a) col;
-- Result: 3 rows — alias "col" names the expanded column
+-----------+------+
| a | col |
+-----------+------+
| [1, 2, 3] | 1 |
| [1, 2, 3] | 2 |
| [1, 2, 3] | 3 |
+-----------+------+Example 2: Expand an ARRAY<STRUCT> column — STRUCT fields become columns
When the array elements are STRUCTs, UNNEST expands each STRUCT field into a separate column. Use an alias to reference specific fields with <alias>.field.
WITH t AS (SELECT * FROM VALUES (ARRAY(STRUCT(1 AS f1, 2 AS f2), STRUCT(3 AS f1, 4 AS f2))) t(a))
SELECT * FROM t JOIN UNNEST(t.a);
-- Result: 2 rows; f1 and f2 expand into separate columns
+------------------------------+------+------+
| a | f1 | f2 |
+------------------------------+------+------+
| [{f1:1, f2:2}, {f1:3, f2:4}] | 1 | 2 |
| [{f1:1, f2:2}, {f1:3, f2:4}] | 3 | 4 |
+------------------------------+------+------+
WITH t AS (SELECT * FROM VALUES (ARRAY(STRUCT(1 AS f1, 2 AS f2), STRUCT(3 AS f1, 4 AS f2))) t(a))
SELECT *, col.f1 FROM t JOIN UNNEST(t.a) col;
-- Result: alias "col" lets you reference individual fields like col.f1
+------------------------------+------+------+------+
| a | f1 | f2 | f1_1 |
+------------------------------+------+------+------+
| [{f1:1, f2:2}, {f1:3, f2:4}] | 1 | 2 | 1 |
| [{f1:1, f2:2}, {f1:3, f2:4}] | 3 | 4 | 3 |
+------------------------------+------+------+------+Example 3: Filter with an ON condition
Use ON to keep only the rows where an expanded element matches a value in the left table. If the array column can be NULL or empty, use LEFT JOIN to avoid silently dropping the entire row.
WITH user_configs AS (
SELECT
'user_001' AS user_id,
2 AS current_dept_id,
ARRAY(1, 2, 3) AS allowed_dept_ids
UNION ALL
SELECT
'user_002' AS user_id,
4 AS current_dept_id,
ARRAY(4, 5) AS allowed_dept_ids
)
SELECT
u.user_id,
u.current_dept_id,
dept_id AS allowed_dept_id
FROM user_configs u
JOIN UNNEST(u.allowed_dept_ids) AS dept_id
ON dept_id = u.current_dept_id;
-- Result: only rows where an allowed dept matches the current dept
+----------+-----------------+-----------------+
| user_id | current_dept_id | allowed_dept_id |
+----------+-----------------+-----------------+
| user_001 | 2 | 2 |
| user_002 | 4 | 4 |
+----------+-----------------+-----------------+