FLATTEN
Introduced or updated: v1.2.213
Transforms nested JSON or array data into a tabular format, where each element or field is represented as a separate row.
Syntax
[LATERAL] FLATTEN (
INPUT => <expr>
[, PATH => <expr>]
[, OUTER => TRUE | FALSE]
[, RECURSIVE => TRUE | FALSE]
[, MODE => 'OBJECT' | 'ARRAY' | 'BOTH']
)
Parameters
Parameter | Description | Default |
---|---|---|
INPUT | JSON or array data to flatten | Required |
PATH | Path to the array/object to flatten | None |
OUTER | Include rows with zero results (with NULL values) | FALSE |
RECURSIVE | Flatten nested elements | FALSE |
MODE | Flatten objects, arrays, or both | 'BOTH' |
LATERAL | Enable cross-referencing with preceding table expressions | Optional |
Output Columns
Column | Description |
---|---|
SEQ | Sequence number for the input |
KEY | Key of the expanded value (NULL if none) |
PATH | Path to the flattened element |
INDEX | Array index (NULL for objects) |
VALUE | Value of the flattened element |
THIS | Element being flattened |
Note: When using LATERAL, output columns may vary due to dynamic cross-referencing.
Examples
Basic Flattening
-- Flatten a JSON object with nested structures
SELECT * FROM FLATTEN(
INPUT => PARSE_JSON(
'{"name": "John", "languages": ["English", "Spanish"], "address": {"city": "New York"}}'
)
);
Results in top-level keys being flattened:
| seq | key | path | index | value | this |
|-----|-----------|-----------|-------|----------------------|----------------------|
| 1 | name | name | NULL | "John" | {original JSON} |
| 1 | languages | languages | NULL | ["English","Spanish"]| {original JSON} |
| 1 | address | address | NULL | {"city":"New York"} | {original JSON} |
Using PATH Parameter
-- Flatten only the languages array by specifying the PATH
SELECT * FROM FLATTEN(
INPUT => PARSE_JSON(
'{"name": "John", "languages": ["English", "Spanish"]}'
),
PATH => 'languages'
);
Results in array elements being flattened:
| seq | key | path | index | value | this |
|-----|------|--------------|-------|-----------|-------------------|
| 1 | NULL | languages[0] | 0 | "English" | ["English","Spanish"] |
| 1 | NULL | languages[1] | 1 | "Spanish" | ["English","Spanish"] |
Recursive Flattening
-- Recursively flatten nested objects and arrays
SELECT * FROM FLATTEN(
INPUT => PARSE_JSON(
'{"name": "John", "address": {"city": "New York", "zip": 10001}}'
),
RECURSIVE => TRUE
);
Results in nested objects being flattened:
| seq | key | path | index | value | this |
|-----|---------|--------------|-------|-------------|-----------------|
| 1 | name | name | NULL | "John" | {original JSON} |
| 1 | address | address | NULL | {"city":...}| {original JSON} |
| 1 | city | address.city | NULL | "New York" | {"city":...} |
| 1 | zip | address.zip | NULL | 10001 | {"city":...} |
Using LATERAL FLATTEN
-- Use LATERAL FLATTEN to transform a JSON array into rows
-- This allows direct access to array elements without a table
SELECT
f.value:item::STRING AS item_name,
f.value:price::FLOAT AS price
FROM
LATERAL FLATTEN(
INPUT => PARSE_JSON('[
{"item":"coffee", "price":2.50},
{"item":"donut", "price":1.20}
]')
) f;
Results:
| item_name | price |
|-----------|-------|
| coffee | 2.5 |
| donut | 1.2 |