FLATTEN for nested data in Snowflake - Mini Project: Build & Apply

Flatten Nested Data Using FLATTEN in Snowflake

📖 Scenario: You work with a Snowflake database that stores customer orders. Each order has a list of items purchased, stored as a nested array. You need to extract each item as a separate row to analyze item sales.

🎯 Goal: Build a Snowflake SQL query that uses the FLATTEN function to convert nested arrays of order items into individual rows for easier analysis.

📋 What You'll Learn

Create a table called orders with columns order_id (integer) and items (variant) containing nested arrays

Insert sample data with nested arrays of items

Write a query using FLATTEN to extract each item from the nested items array

Select order_id and the flattened item values as separate rows

💡 Why This Matters

🌍 Real World

Many cloud data platforms store nested JSON data. Flattening helps analyze each nested element as a separate row for reporting and insights.

💼 Career

Data engineers and analysts often use FLATTEN in Snowflake to work with semi-structured data efficiently.

1

Create the orders table with nested items data

Create a table called orders with columns order_id as INTEGER and items as VARIANT. Insert two rows with order_id values 1 and 2. For items, insert the JSON arrays ["apple", "banana"] for order 1 and ["orange", "grape", "melon"] for order 2.

Snowflake

-- Create the orders table and insert two rows with nested items arrays
-- Your code here

Need a hint?

Use CREATE OR REPLACE TABLE to create the table. Use PARSE_JSON to insert JSON arrays into the items column.

2

Add a query to flatten the items array

Write a SELECT query that uses the FLATTEN function on the items column from the orders table. Alias the FLATTEN function as f. Select order_id and f.value as item.

Snowflake

CREATE OR REPLACE TABLE orders (
  order_id INTEGER,
  items VARIANT
);

INSERT INTO orders (order_id, items) VALUES
  (1, PARSE_JSON('["apple", "banana"]')),
  (2, PARSE_JSON('["orange", "grape", "melon"]'));

-- Write a SELECT query using FLATTEN on items
-- Your code here

Need a hint?

Use LATERAL FLATTEN(input => items) f in the FROM clause to expand the nested array.

3

Filter items to only include those starting with the letter 'g'

Modify the SELECT query to add a WHERE clause that filters the flattened item values to only include those starting with the letter 'g'. Use the LIKE operator with 'g%'.

Snowflake

CREATE OR REPLACE TABLE orders (
  order_id INTEGER,
  items VARIANT
);

INSERT INTO orders (order_id, items) VALUES
  (1, PARSE_JSON('["apple", "banana"]')),
  (2, PARSE_JSON('["orange", "grape", "melon"]'));

SELECT order_id, f.value AS item
FROM orders,
LATERAL FLATTEN(input => items) f
-- Add a WHERE clause to filter items starting with 'g'
-- Your code here

Need a hint?

Use WHERE f.value LIKE 'g%' to filter items starting with 'g'.

4

Complete the query by ordering results by order_id and item

Add an ORDER BY clause to the SELECT query to sort the results by order_id ascending and then by item ascending.

Snowflake

CREATE OR REPLACE TABLE orders (
  order_id INTEGER,
  items VARIANT
);

INSERT INTO orders (order_id, items) VALUES
  (1, PARSE_JSON('["apple", "banana"]')),
  (2, PARSE_JSON('["orange", "grape", "melon"]'));

SELECT order_id, f.value AS item
FROM orders,
LATERAL FLATTEN(input => items) f
WHERE f.value LIKE 'g%'
-- Add ORDER BY clause to sort by order_id and item
-- Your code here

Need a hint?

Use ORDER BY order_id ASC, item ASC to sort the results.