Skip to main content

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

ParameterDescriptionDefault
INPUTJSON or array data to flattenRequired
PATHPath to the array/object to flattenNone
OUTERInclude rows with zero results (with NULL values)FALSE
RECURSIVEFlatten nested elementsFALSE
MODEFlatten objects, arrays, or both'BOTH'
LATERALEnable cross-referencing with preceding table expressionsOptional

Output Columns

ColumnDescription
SEQSequence number for the input
KEYKey of the expanded value (NULL if none)
PATHPath to the flattened element
INDEXArray index (NULL for objects)
VALUEValue of the flattened element
THISElement 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 |
Explore Databend Cloud for FREE
Low-cost
Fast Analytics
Easy Data Ingestion
Elastic Scaling
Try it today