The UNNEST operator unnests an array into multiple rows. It supports simple arrays and arrays of structs, and can be used with a JOIN to unnest an array column within a table. - MaxCompute

The UNNEST operator is used to unnest an ARRAY field into multiple rows. Depending on the type of array elements, there are two scenarios: unnesting a regular array and unnesting a STRUCT array. UNNEST can be used independently or combined with the JOIN syntax to horizontally expand an array column in a table.

Unnesting an array

The UNNEST operator unnests an array, returning a table with one row for each element in the array.

Syntax

SELECT ... FROM UNNEST (<expression>) [AS <alias>];

Parameters

expression: Required. The expression to unnest. Must be an array type.
alias: Optional. An alias for the unnested column. If you omit this alias, the system generates a default column name, such as __generated_unnest_col_c0.

Examples

The following examples show how to unnest an array. The second example uses an alias to name the resulting column.

SELECT * FROM unnest(array(1,2,3));

-- Result
+---------------------------+
| __generated_unnest_col_c0 |
+---------------------------+
| 1                         |
| 2                         |
| 3                         |
+---------------------------+

SELECT * FROM unnest(array(1,2,3)) t;

-- Result
+------+
| t    |
+------+
| 1    |
| 2    |
| 3    |
+------+

Unnesting an array of structs

When applied to an array of structs (an array where each element is a struct), the UNNEST operator expands each struct into a row with a column for each of its fields. If a struct contains nested structs, UNNEST only expands the top-level fields and does not recursively unnest the inner structs.

Syntax

SELECT ... FROM UNNEST (<expression>) [AS <alias>];

Parameters

expression: Required. The expression to unnest. Must be an array of structs, with the type array<struct>.
alias: Optional. An alias for the table produced by UNNEST. You can use this alias to reference the original struct element (<alias>) or its fields directly (<alias>.field or just field).

Examples

Example 1: The following examples show how to unnest an array of structs. The second example uses a table alias t, which allows you to select the original struct element in addition to its expanded fields.

SELECT * FROM UNNEST(ARRAY(STRUCT(1 AS a, 2 AS b), STRUCT(3 AS a, 4 AS b)));

-- Result
+------+------+
| 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
+------+------+------------+
| a    | b    | t          |
+------+------+------------+
| 1    | 2    | {a:1, b:2} |
| 3    | 4    | {a:3, b:4} |
+------+------+------------+

Example 2: Access struct fields by using the table alias. You can reference fields either directly by name (e.g., a) or by using the <alias>.field syntax (e.g., t.a).

SELECT a, t.a FROM UNNEST(ARRAY(STRUCT(1 AS a, 2 AS b), STRUCT(3 AS a, 4 AS b))) t;

-- Result
+------+------+
| a    | a_1  |
+------+------+
| 1    | 1    |
| 3    | 3    |
+------+------+

Example 3: UNNEST only expands the top-level fields of a struct. It does not recursively expand nested structs.

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            | b    |
+--------------+------+
| {a1:1, a2:2} | 3    |
| {a1:4, a2:5} | 6    |
+--------------+------+

Using UNNEST with JOIN

UNNEST and JOIN are both subclauses of the FROM clause and can be used together. When UNNEST serves as the right table in a JOIN, it unnests an array column from the left table.

Syntax

SELECT ... FROM ... JOIN UNNEST (<expression>) [AS <alias>] [ON <join_condition>]

Parameters

expression: Required. An array-type column from the left table of the JOIN.
alias: Optional. An alias for the column containing the unnested elements.
join_condition: Optional. The join condition for the JOIN. It consists of one or more equality expressions in the format on equality_expression [and equality_expression]....

Examples

Example 1: The UNNEST expression must reference an array column from the left table. The second example shows how to use an alias (col) to name the column of unnested elements.

WITH t AS (SELECT * FROM VALUES (ARRAY(1,2,3)) t(a))
SELECT * FROM t JOIN UNNEST(t.a);

-- Result
+-----------+---------------------------+
| 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
+-----------+------+
| a         | col  |
+-----------+------+
| [1, 2, 3] | 1    |
| [1, 2, 3] | 2    |
| [1, 2, 3] | 3    |
+-----------+------+

Example 2: If the array column from the left table contains struct elements, UNNEST expands each struct into separate columns for each of its fields.

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
+------------------------------+------+------+
| 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
+------------------------------+------+------+------+
| 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: Join the unnested elements with another column by using an ON condition

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
+----------+-----------------+-----------------+
| user_id  | current_dept_id | allowed_dept_id |
+----------+-----------------+-----------------+
| user_001 | 2               | 2               |
| user_002 | 4               | 4               |
+----------+-----------------+-----------------+