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 |