COF-C03 Question 300
Single answerSemi-structuredA retail company lands daily JSON files from an e-commerce platform into a Snowflake table named RAW_EVENTS with a single VARIANT column called SRC. Analysts need a query that returns the order_id and customer email only for rows where the JSON payload contains an array named items with at least one element. Which approach should the data engineer use?
- A
SELECT SRC:order_id::STRING AS order_id, SRC:customer.email::STRING AS email FROM RAW_EVENTS WHERE ARRAY_SIZE(SRC:items) > 0;
- B
SELECT SRC.order_id::STRING AS order_id, SRC.customer.email::STRING AS email FROM RAW_EVENTS WHERE COUNT(SRC.items) > 0;
- C
SELECT GET_PATH(SRC, 'order_id')::STRING AS order_id, GET_PATH(SRC, 'customer.email')::STRING AS email FROM RAW_EVENTS GROUP BY 1,2 HAVING ARRAY_SIZE(GET_PATH(SRC, 'items')) > 0;
- D
SELECT VALUE:order_id::STRING AS order_id, VALUE:customer.email::STRING AS email FROM RAW_EVENTS, LATERAL FLATTEN(INPUT => SRC) WHERE ARRAY_LENGTH(SRC:items) > 0;
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical querying of semi-structured JSON stored in a VARIANT column. In Snowflake, JSON attributes are commonly accessed with path notation using a colon, for example SRC:order_id and SRC:customer.email. When the extracted values are needed as relational output, explicit casting such as ::STRING is recommended. To determine whether a JSON array contains elements, ARRAY_SIZE on the array path is an appropriate row-level function. LATERAL FLATTEN is powerful but should be used only when the array must be expanded into separate rows. Snowflake documentation on querying semi-structured data and traversing VARIANT values describes the use of path notation, casting, and functions such as ARRAY_SIZE for arrays.
- A. Correct.
Correct. Snowflake supports semi-structured traversal using colon notation on VARIANT data, such as SRC:order_id and SRC:customer.email. Casting the extracted values to STRING is a common best practice when returning scalar values. ARRAY_SIZE(SRC:items) correctly checks that the items array exists and has at least one element. This directly solves the requirement without unnecessary flattening.
- B. Incorrect.
Incorrect. Dot notation in this form is not the standard way to traverse fields inside a VARIANT column in SQL queries; Snowflake uses path notation such as SRC:order_id or bracket notation. In addition, COUNT(SRC.items) is an aggregate and would require grouping, and it does not test the length of the array within each row.
- C. Incorrect.
Incorrect. GET_PATH can be used with VARIANT data, so the field extraction itself is plausible. However, GROUP BY and HAVING are unnecessary because the requirement is row-level filtering, not aggregation. Using HAVING here complicates the query and reflects a misunderstanding of when to use aggregate filtering.
- D. Incorrect.
Incorrect. LATERAL FLATTEN is useful when expanding arrays or objects into multiple rows, but it is not needed just to check whether an array has at least one element and return top-level fields. Also, VALUE refers to the flattened element, not the original SRC object, so VALUE:order_id and VALUE:customer.email would not reliably return the intended root-level attributes.