SnowPro Associate: Platform Question 206
Single answer● Querying dataA retail analytics team stores semi-structured order data in a VARIANT column named ORDER_DATA in table RAW_ORDERS. Each row contains JSON similar to: {"customer":{"id":123,"name":"Ava"},"items":[{"sku":"P100","qty":2},{"sku":"P200","qty":1}],"status":"SHIPPED"}. An analyst needs a query that returns one row per item for only shipped orders, with columns CUSTOMER_ID, SKU, and QTY. Which query correctly produces the required result?
- A
SELECT ORDER_DATA:customer.id::NUMBER AS CUSTOMER_ID, f.value:sku::STRING AS SKU, f.value:qty::NUMBER AS QTY FROM RAW_ORDERS, LATERAL FLATTEN(input => ORDER_DATA:items) f WHERE ORDER_DATA:status::STRING = 'SHIPPED';
- B
SELECT ORDER_DATA.customer.id AS CUSTOMER_ID, items.sku AS SKU, items.qty AS QTY FROM RAW_ORDERS WHERE ORDER_DATA.status = 'SHIPPED';
- C
SELECT ORDER_DATA:customer:id::NUMBER AS CUSTOMER_ID, ORDER_DATA:items.sku::STRING AS SKU, ORDER_DATA:items.qty::NUMBER AS QTY FROM RAW_ORDERS WHERE ORDER_DATA:status = 'SHIPPED';
- D
SELECT VALUE:customer.id::NUMBER AS CUSTOMER_ID, VALUE:sku::STRING AS SKU, VALUE:qty::NUMBER AS QTY FROM RAW_ORDERS, FLATTEN(ORDER_DATA) WHERE VALUE:status::STRING = 'SHIPPED';
Show answer and explanation
Correct answer: A
Explanation
The correct approach is to use Snowflake semi-structured data path notation and LATERAL FLATTEN for arrays. For VARIANT columns containing JSON, object attributes are referenced with the syntax column_name:key.subkey, and values are commonly cast with ::STRING, ::NUMBER, and similar types for relational output. When a JSON field contains an array, such as items, FLATTEN transforms each array element into its own row. Using LATERAL with FLATTEN lets the query access both the original row and each element in the array. This is a core querying pattern for semi-structured data in Snowflake and is consistent with Snowflake documentation on querying semi-structured data and using FLATTEN table functions.
- A. Correct.
Correct. In Snowflake, JSON elements inside a VARIANT column are accessed with path notation such as ORDER_DATA:customer.id and cast to relational types when needed. Because items is an array, LATERAL FLATTEN(input => ORDER_DATA:items) is required to produce one row per array element. The alias f exposes each array element through f.value, allowing extraction of sku and qty. Filtering shipped orders with ORDER_DATA:status::STRING = 'SHIPPED' is also valid.
- B. Incorrect.
Incorrect. This uses dot notation as though ORDER_DATA were a structured relational object with directly addressable columns. In Snowflake, semi-structured data in VARIANT is queried using path notation with a colon from the column name, such as ORDER_DATA:customer.id. It also does not flatten the items array, so it would not return one row per item.
- C. Incorrect.
Incorrect. Although ORDER_DATA:customer:id may look close to valid syntax, Snowflake path expressions do not use multiple colons between nested object keys in this way. More importantly, ORDER_DATA:items is an array, so referencing ORDER_DATA:items.sku and ORDER_DATA:items.qty without FLATTEN does not expand array elements into separate rows. This reflects a common misconception that array members can be projected like scalar object fields.
- D. Incorrect.
Incorrect. FLATTEN is being applied to the entire ORDER_DATA object rather than specifically to the items array, so the resulting VALUE rows would represent top-level keys like customer, items, and status, not individual items. Also, VALUE:customer.id would not exist on the flattened item rows from the full object in the intended way. This query misuses FLATTEN and would not reliably return CUSTOMER_ID, SKU, and QTY per item.