Semi-structured data querying (JSON, Avro) in Snowflake - Mini Project: Build & Apply

Querying Semi-Structured JSON Data in Snowflake

📖 Scenario: You work as a data analyst at a retail company. The company stores customer order details in a Snowflake table as JSON data. You need to extract specific information from this semi-structured JSON data to generate reports.

🎯 Goal: Build a Snowflake SQL query that extracts customer names and their order amounts from a JSON column.

📋 What You'll Learn

Create a table called orders with a data column of type VARIANT to store JSON.

Insert sample JSON data with customer names and order amounts.

Write a query to extract customer_name and order_amount from the JSON data.

Use Snowflake's LATERAL FLATTEN function to handle arrays in JSON.

💡 Why This Matters

🌍 Real World

Many companies store data in semi-structured formats like JSON in cloud data warehouses. Querying this data efficiently is essential for reporting and analytics.

💼 Career

Data analysts and engineers often need to extract and transform JSON data stored in Snowflake to generate business insights.

1

Create the orders table with a data column

Write a Snowflake SQL statement to create a table called orders with one column named data of type VARIANT.

Snowflake

-- Create the orders table with a VARIANT column named data
-- Your code here

Need a hint?

The VARIANT data type in Snowflake stores semi-structured data like JSON.

2

Insert sample JSON data into the orders table

Insert two rows into the orders table. Each row's data column should contain JSON with keys customer_name and orders. The orders key holds an array of objects with order_id and order_amount. Use these exact JSON values:

Row 1 JSON: {"customer_name": "Alice", "orders": [{"order_id": 101, "order_amount": 250}, {"order_id": 102, "order_amount": 450}]}
Row 2 JSON: {"customer_name": "Bob", "orders": [{"order_id": 103, "order_amount": 300}]}

Snowflake

CREATE OR REPLACE TABLE orders (data VARIANT);
-- Insert the two JSON rows into orders table
-- Your code here

Need a hint?

Use PARSE_JSON() to convert JSON strings into VARIANT data type for insertion.

3

Write a query to extract customer names and order amounts

Write a Snowflake SQL query that selects customer_name and order_amount from the orders table. Use LATERAL FLATTEN on the orders array inside the JSON data column to access each order object. Use the aliases o for the table and f for the flatten function. Extract customer_name from o.data and order_amount from f.value.

Snowflake

CREATE OR REPLACE TABLE orders (data VARIANT);
INSERT INTO orders (data) VALUES
  (PARSE_JSON('{"customer_name": "Alice", "orders": [{"order_id": 101, "order_amount": 250}, {"order_id": 102, "order_amount": 450}]}')),
  (PARSE_JSON('{"customer_name": "Bob", "orders": [{"order_id": 103, "order_amount": 300}]}'));

-- Write a query to select customer_name and order_amount using LATERAL FLATTEN
-- Your code here

Need a hint?

Use LATERAL FLATTEN to expand the orders array and extract each order's amount.

4

Complete the query with ordering by customer name and order amount

Add an ORDER BY clause to the query to sort the results first by customer_name ascending, then by order_amount descending.

Snowflake

CREATE OR REPLACE TABLE orders (data VARIANT);
INSERT INTO orders (data) VALUES
  (PARSE_JSON('{"customer_name": "Alice", "orders": [{"order_id": 101, "order_amount": 250}, {"order_id": 102, "order_amount": 450}]}')),
  (PARSE_JSON('{"customer_name": "Bob", "orders": [{"order_id": 103, "order_amount": 300}]}'));

SELECT
  o.data:customer_name::STRING AS customer_name,
  f.value:order_amount::NUMBER AS order_amount
FROM orders o,
LATERAL FLATTEN(input => o.data:orders) f
-- Add ORDER BY clause here
-- Your code here

Need a hint?

Use ORDER BY customer_name ASC, order_amount DESC to sort the results.