FLATTEN for nested data in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to flatten the nested array column named 'items'.

Snowflake

SELECT value FROM orders, LATERAL FLATTEN(input => [1]);

Drag options to blanks, or click blank then click option'

Aorders.items

Borders

Citems

Dorders.value

Attempts:

3 left

💡 Hint

Common Mistakes

Using the table name without the column.

Using just the column name without the table alias.

Using a non-array column as input.

The FLATTEN function requires the nested array column as input. Here, 'orders.items' is the correct nested array to flatten.

2fill in blank

medium

Complete the code to extract the 'name' field from each element after flattening.

Snowflake

SELECT f.value:[1] AS item_name FROM orders, LATERAL FLATTEN(input => orders.items) f;

Drag options to blanks, or click blank then click option'

Aid

Bname

Cvalue

Dprice

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong field name.

Omitting the colon before the field name.

Using 'value' instead of the actual field.

After flattening, each element is a JSON object. To get the 'name' field, use 'f.value:name'.

3fill in blank

hard

Fix the error in the code to correctly flatten the nested array 'products' in the 'sales' table.

Snowflake

SELECT s.id, f.value FROM sales s, LATERAL FLATTEN(input => s.[1]) f;

Drag options to blanks, or click blank then click option'

Aitems

Bproduct

Cproduct_list

Dproducts

Attempts:

3 left

💡 Hint

Common Mistakes

Using singular instead of plural column name.

Using a non-existent column.

Misspelling the column name.

The correct nested array column is 'products'. Using 'product' or other names causes errors.

4fill in blank

hard

Fill both blanks to flatten the 'details' array and extract the 'price' field.

Snowflake

SELECT f.value:[1] AS price FROM invoices, LATERAL FLATTEN(input => invoices.[2]) f;

Drag options to blanks, or click blank then click option'

Aprice

Bdetails

Camount

Ditems

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the nested array column and the field name.

Using incorrect JSON path syntax.

Using wrong column names.

The nested array is 'details', and the field to extract is 'price'.

5fill in blank

hard

Fill all three blanks to flatten the 'attributes' array, extract 'color', and filter where 'size' is 'M'.

Snowflake

SELECT f.value:[1] AS color FROM products p, LATERAL FLATTEN(input => p.[2]) f WHERE f.value:[3] = 'M';

Drag options to blanks, or click blank then click option'

Acolor

Battributes

Csize

Ddetails

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong nested array name.

Confusing 'color' and 'size' fields.

Incorrect JSON path syntax.

The nested array is 'attributes'. We extract 'color' and filter where 'size' equals 'M'.