You have a JSON column data with nested arrays of orders. How would you write a dbt SQL query to flatten the array data:orders and select each order's id and amount?
hard🚀 Application Q8 of 15
dbt - Advanced Patterns
You have a JSON column data with nested arrays of orders. How would you write a dbt SQL query to flatten the array data:orders and select each order's id and amount?
AUse <code>SELECT data:orders->>'id', data:orders->>'amount' FROM table</code>
BUse <code>SELECT order.value:id, order.value:amount FROM table, LATERAL FLATTEN(input => data:orders) AS order</code>
CUse <code>SELECT json_extract_path_text(data, 'orders', 'id'), json_extract_path_text(data, 'orders', 'amount') FROM table</code>
DUse <code>SELECT data->'orders'->'id', data->'orders'->'amount' FROM table</code>
Step-by-Step Solution
Solution:
Step 1: Understand flattening JSON arrays
LATERAL FLATTEN is used in Snowflake to expand JSON arrays into rows.
Step 2: Analyze options
Use SELECT order.value:id, order.value:amount FROM table, LATERAL FLATTEN(input => data:orders) AS order correctly uses LATERAL FLATTEN with alias to access each order's fields. Others try to extract without flattening, which won't produce rows per order.
Final Answer:
Use SELECT order.value:id, order.value:amount FROM table, LATERAL FLATTEN(input => data:orders) AS order -> Option B
Quick Check:
Flatten JSON arrays with LATERAL FLATTEN [OK]
Quick Trick:Use LATERAL FLATTEN to expand JSON arrays into rows [OK]
Common Mistakes:
MISTAKES
Trying to extract array elements without flattening
Using ->> on arrays directly
Ignoring need for lateral join
Master "Advanced Patterns" in dbt
9 interactive learning modes - each teaches the same concept differently
