In Snowflake, which function is commonly used to flatten a JSON array stored in a VARIANT column?

• A. CROSS JOIN
• B. LATERAL FLATTEN
• C. JSON_EXTRACT
• D. TABLE FLATTEN

Answer: (B)

Flattening a JSON array stored in a VARIANT column relies on turning each array element into its own row so you can query and filter individual items. In Snowflake, the FLATTEN table function does exactly this, and using LATERAL ties the flatten operation to the current row from the outer table. This correlation is what lets you pass the VARIANT value from each row into FLATTEN and produce one output row per array element, while still retaining the original row’s context (such as its id). A typical pattern looks like this: select the original row’s identifier and the flattened element values by listing the base table and then LATERAL FLATTEN(input => base_table.variant_col). The flattened value (often f.value) represents each array item, and you can also access other metadata like f.index if needed.

Other options don’t fit because extracting JSON data or expanding arrays across rows isn’t accomplished by a non-correlated function call. JSON_EXTRACT isn’t the standard way to produce multiple rows from an array in Snowflake, and TABLE FLATTEN is not the commonly used syntax in this direct form. The idiomatic approach is to use the LATERAL FLATTEN pattern to map each array element back to its originating row.