Which SQL will retrieve city, date, and temperature from CITY_TEMPERATURE's TEMP JSON using the given pattern?

• A. select TEMPVAL:city::string as city,value:date::DATE as day ,VALUE:value::STRING as temperature from city_temperature lateral flatten( input => TEMPVAL:temp );
• B. select TEMPVAL:city::string as city,value:date::DATE as day ,VALUE:value::STRING as temperature from city_temperature, table flatten( input => TEMPVAL:temp );
• C. select TEMPVAL:city::string as city,value:date::DATE as day ,VALUE:value::STRING as temperature from city_temperature, flatten( input => TEMPVAL:temp );
• D. select TEMPVAL:city::string as city, value:date::DATE as day, VALUE:value::STRING as temperature from city_temperature, table ( flatten(input => TEMPVAL:temp) );

Answer: (A)

Flattening a JSON array into rows is the right move when you need to turn each element of a nested array into its own row while still keeping fields from the parent object. In Snowflake, you use FLATTEN on the array field and join it to the base row with a LATERAL (or CROSS JOIN LATERAL) so every original row expands into multiple rows—one for each element of the array.

This pattern pulls the city directly from the parent row (TEMPVAL:city) and then uses the flattened element’s content for the date and temperature via the VALUE column. Specifically, the query probes the flattened VALUE with date and value, giving day and temperature, while city remains tied to the original CITY_TEMPERATURE row. This matches the expected way to access nested JSON fields: the flattened element is exposed in VALUE, and its inner fields are referenced as VALUE:date and VALUE:value. The other options either mishandle the flatten call (omitting the proper lateral join) or rely on a less clear TABLE(...) form that makes it harder to reference the nested fields in a single SELECT.