UNNEST()
All functions > CORE > UNNEST()
Unnests an array of rows into a table of rows
Signatures
Returns: Unnested row
UNNEST(base: ARRAY<ROW>) → ROWsql
| Parameter | Type | Required | Description |
|---|---|---|---|
base | ARRAY<ROW> | Yes | Array of rows to unnest |
Notes
- Unnests an array of rows into a table of rows
- Each row in the input array becomes a row in the output table
- The output table has the same number of rows and columns as the input array
- The output table has the same column names and types as the input array
Examples
Unnest in SELECT / REPORT patterns (grouping_unnesting suite)
FeatureQL
WITH
array_of_rows := ARRAY(
ROW('A', 10.00, 1),
ROW('B', 20.00, 1),
ROW('A', 240.00, 2),
ROW('B', 50.00, 2)
)::ARRAY(ROW(category VARCHAR, sum_price DECIMAL(10,2), customer_id BIGINT)),
row_unnested := UNNEST(array_of_rows)
SELECT
customer_id := row_unnested[customer_id],
category := row_unnested[category],
sum_price := row_unnested[sum_price]
ORDER BY
customer_id,
category
;Result
| CUSTOMER_ID BIGINT | CATEGORY VARCHAR | SUM_PRICE VARCHAR |
|---|---|---|
| 1 | A | 10.00 |
| 1 | B | 20.00 |
| 2 | A | 240.00 |
| 2 | B | 50.00 |