FLATTEN for nested data in Snowflake - Step-by-Step Execution

Process Flow - FLATTEN for nested data

Start with nested data

↓

Apply FLATTEN function

↓

Extract each nested element as a row

↓

Use output in query

↓

Result: flat table with nested elements separated

FLATTEN takes nested data and turns each nested item into its own row, making it easier to query.

Execution Sample

Snowflake

SELECT value
FROM TABLE(FLATTEN(input => PARSE_JSON('[1,2,[3,4]]')));

This query flattens a JSON array with nested arrays, outputting each element as a separate row.

Process Table

Step	Input JSON	FLATTEN Action	Output Rows	Notes
1	[1,2,[3,4]]	Start with nested JSON array	N/A	Initial nested data
2	[1,2,[3,4]]	Apply FLATTEN	Row 1: 1 Row 2: 2 Row 3: [3,4]	Top-level elements extracted
3	[3,4]	Apply FLATTEN again on nested array	Row 3.1: 3 Row 3.2: 4	Nested array elements extracted
4	N/A	Final output	Rows: 1, 2, 3, 4	All nested elements flattened into rows

💡 All nested elements have been extracted as individual rows.

Status Tracker

Variable	Start	After Step 2	After Step 3	Final
Input JSON	[1,2,[3,4]]	[1,2,[3,4]]	[3,4]	N/A
Output Rows	None	1, 2, [3,4]	1, 2, 3, 4	1, 2, 3, 4

Key Moments - 2 Insights

Why does FLATTEN output the nested array [3,4] as a single row first?

Because FLATTEN processes one level at a time, it first extracts top-level elements. The nested array [3,4] is treated as one element until flattened again (see step 2 and 3 in execution_table).

Can FLATTEN automatically flatten all nested levels in one step?

No, FLATTEN only flattens one level per call. To flatten deeper levels, you apply FLATTEN multiple times or use lateral joins.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what are the output rows?

A 1, 2, [3,4]

B 1, 2, 3, 4

C [1,2,[3,4]]

D 3, 4

💡 Hint

Check the 'Output Rows' column in step 2 of execution_table.

At which step does the nested array [3,4] get split into individual elements?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'FLATTEN Action' and 'Output Rows' columns in execution_table.

If the input JSON had no nested arrays, how would the output rows change?

A FLATTEN would fail with an error

B There would be no output rows

C They would be the same as the input elements, no extra flattening needed

D Output rows would be empty arrays

💡 Hint

Refer to variable_tracker and think about flattening a flat array.

Concept Snapshot

FLATTEN extracts nested elements into separate rows.
It processes one nesting level at a time.
Use TABLE(FLATTEN(input => nested_data)) in Snowflake.
Nested arrays require multiple FLATTEN calls or lateral joins.
Result is easier querying of nested data.

Full Transcript

FLATTEN in Snowflake is used to turn nested data into flat rows. It works by taking one level of nested elements and outputting each as a separate row. For example, a JSON array with nested arrays is first flattened at the top level, producing rows for each element including nested arrays as single rows. Then, applying FLATTEN again on those nested arrays breaks them into individual elements. This step-by-step flattening helps query nested data easily. FLATTEN only handles one level at a time, so multiple calls or lateral joins are needed for deeper nesting. The execution table shows how the nested JSON '[1,2,[3,4]]' is flattened first into rows '1', '2', and '[3,4]', then the nested array '[3,4]' is flattened into '3' and '4'. This process stops when all nested elements are extracted as rows.