FLATTEN for nested data in Snowflake - Commands & Configuration

Introduction

Sometimes data is stored inside other data, like a list inside a table. FLATTEN helps to open up this nested data so you can see and use each item separately.

When you have a column with a list of items and want to see each item as its own row.

When you want to analyze or filter elements inside nested JSON data stored in a table.

When you need to join nested data with other tables by breaking it into simple rows.

When you want to count or aggregate values inside nested arrays.

When you want to transform complex nested data into a flat table for easier reporting.

Commands

Create a table named orders with a nested JSON column called items, then insert sample data with arrays of products and quantities.

Terminal

CREATE OR REPLACE TABLE orders (id INT, items VARIANT);

INSERT INTO orders VALUES
(1, PARSE_JSON('[{"product":"apple","qty":2},{"product":"banana","qty":3}]')),
(2, PARSE_JSON('[{"product":"orange","qty":1}]'));

Expected Output

Query executed successfully.

2 rows inserted.

Use FLATTEN to open the nested items array into separate rows, extracting product name and quantity for each item.

Terminal

SELECT id, f.value:product AS product, f.value:qty AS quantity
FROM orders,
LATERAL FLATTEN(input => items) f;

Expected Output

ID  | PRODUCT | QUANTITY

----|---------|---------

1   | apple   | 2

1   | banana  | 3

2   | orange  | 1

→

LATERAL - Allows FLATTEN to work on each row of the orders table.

→

input => items - Specifies the nested array column to flatten.

Count how many items each order has by flattening the items array and grouping by order id.

Terminal

SELECT id, COUNT(*) AS item_count
FROM orders,
LATERAL FLATTEN(input => items) f
GROUP BY id;

Expected Output

ID  | ITEM_COUNT

----|-----------

1   | 2

2   | 1

Key Concept

If you remember nothing else from this pattern, remember: FLATTEN turns nested arrays into simple rows so you can work with each element easily.

Common Mistakes

Trying to select nested elements without using LATERAL FLATTEN.

The nested array stays as one value and does not split into rows, so you can't access individual items.

Always use LATERAL FLATTEN on the nested column to expand it before selecting nested fields.

Not aliasing the FLATTEN output and trying to access fields directly.

Without aliasing, Snowflake cannot identify the flattened data to extract fields from.

Use an alias like 'f' for FLATTEN and then access nested fields with f.value:<field>.

Summary

Create a table with nested JSON data using VARIANT type.

Use LATERAL FLATTEN to expand nested arrays into rows.

Select nested fields from the flattened output for easy querying.