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.

Practice

1. What does the FLATTEN function do in Snowflake when working with nested data?

easy

A. It encrypts nested data for security.

B. It compresses data to save storage space.

C. It converts nested arrays or objects into simple rows.

D. It creates a backup of nested data.

Solution

1. Step 1: Understand the purpose of FLATTEN

   FLATTEN is designed to take nested arrays or objects and turn them into individual rows so they are easier to query.

2. Step 2: Compare options to FLATTEN's function

   Options A, B, and D describe encryption, compression, and backup, which are unrelated to FLATTEN's role.

3. Final Answer:

   It converts nested arrays or objects into simple rows. -> Option C

4. Quick Check:

   FLATTEN = convert nested data to rows [OK]

Hint: FLATTEN breaks nested data into rows for easy reading [OK]

Common Mistakes:

• Thinking FLATTEN compresses or encrypts data
• Confusing FLATTEN with backup or storage functions
• Assuming FLATTEN changes data format instead of structure

2. Which of the following is the correct syntax to use FLATTEN on a JSON column named data in Snowflake?

easy

A. SELECT FLATTEN(data) FROM table;

B. SELECT * FROM FLATTEN(input => data);

C. SELECT FLATTEN(input = data) FROM table;

D. SELECT * FROM table, LATERAL FLATTEN(input => data);

Solution

1. Step 1: Recall FLATTEN usage in FROM clause

   FLATTEN is used as a table function in the FROM clause with LATERAL to expand nested data.

2. Step 2: Analyze each option's syntax

   SELECT * FROM table, LATERAL FLATTEN(input => data); correctly uses FROM table, LATERAL FLATTEN(input => data). Options A and C misuse FLATTEN as a scalar function. SELECT * FROM FLATTEN(input => data); misses the table reference.

3. Final Answer:

   SELECT * FROM table, LATERAL FLATTEN(input => data); -> Option D

4. Quick Check:

   FLATTEN used with LATERAL in FROM clause [OK]

Hint: Use FLATTEN with LATERAL in FROM clause for nested data [OK]

Common Mistakes:

• Using FLATTEN as a scalar function in SELECT
• Omitting LATERAL keyword
• Not specifying input parameter correctly

3. Given the JSON column data with value '{"items": ["apple", "banana", "cherry"]}', what will the query below return?

SELECT f.value FROM table, LATERAL FLATTEN(input => data:items) f;

medium

A. Rows with values: apple, banana, cherry

B. A single row with the entire array as a string

C. An error because data:items is invalid syntax

D. Rows with keys and values of the JSON object

Solution

1. Step 1: Understand FLATTEN on JSON array

   FLATTEN(input => data:items) expands the array under 'items' into multiple rows, each with one element.

2. Step 2: Analyze the query output

   The query selects f.value, which will be each element: 'apple', 'banana', 'cherry' as separate rows.

3. Final Answer:

   Rows with values: apple, banana, cherry -> Option A

4. Quick Check:

   FLATTEN on array returns each element as a row [OK]

Hint: FLATTEN on JSON array returns each element as a separate row [OK]

Common Mistakes:

• Expecting a single row with the whole array
• Confusing keys with values in FLATTEN output
• Misreading JSON path syntax

4. You wrote this query to flatten nested JSON data:

SELECT f.value FROM table, FLATTEN(input => data:items) f;

But it returns an error. What is the likely cause?

medium

A. Missing LATERAL keyword before FLATTEN

B. Incorrect JSON path syntax in input

C. FLATTEN cannot be used on JSON arrays

D. SELECT statement missing WHERE clause

Solution

1. Step 1: Identify FLATTEN usage requirements

   FLATTEN is a table function that requires LATERAL when used with another table to expand nested data.

2. Step 2: Check query syntax

   The query misses the LATERAL keyword before FLATTEN, causing a syntax error.

3. Final Answer:

   Missing LATERAL keyword before FLATTEN -> Option A

4. Quick Check:

   FLATTEN needs LATERAL in FROM clause [OK]

Hint: Always add LATERAL before FLATTEN in FROM clause [OK]

Common Mistakes:

• Forgetting LATERAL keyword
• Assuming FLATTEN works without LATERAL
• Blaming JSON path syntax instead of syntax structure

5. You have a table with a column orders storing nested JSON arrays of items per order. You want to list each item with its order ID. Which query correctly uses FLATTEN to achieve this?

hard

A. SELECT order_id, FLATTEN(orders) FROM orders_table;

B. SELECT order_id, f.value FROM orders_table, LATERAL FLATTEN(input => orders) f;

C. SELECT order_id, f.value FROM orders_table JOIN FLATTEN(input => orders) f ON TRUE;

D. SELECT order_id, f.value FROM orders_table, FLATTEN(orders) f;

Solution

1. Step 1: Understand how to join FLATTEN with table

   FLATTEN must be used with LATERAL in the FROM clause to expand nested arrays per row.

2. Step 2: Evaluate each option's correctness

   SELECT order_id, f.value FROM orders_table, LATERAL FLATTEN(input => orders) f; correctly uses FROM orders_table, LATERAL FLATTEN(input => orders) f, selecting order_id and each item value. Options A and D misuse FLATTEN syntax. SELECT order_id, f.value FROM orders_table JOIN FLATTEN(input => orders) f ON TRUE; uses JOIN incorrectly without LATERAL.

3. Final Answer:

   SELECT order_id, f.value FROM orders_table, LATERAL FLATTEN(input => orders) f; -> Option B

4. Quick Check:

   Use LATERAL FLATTEN with table to list nested items [OK]

Hint: Use LATERAL FLATTEN(input => column) to expand nested arrays per row [OK]

Common Mistakes:

• Using FLATTEN without LATERAL
• Trying to JOIN FLATTEN without LATERAL
• Incorrect FLATTEN syntax in SELECT clause