The JSON below is stored in a VARIANT column named V in a table named jCustRaw: Which query will return one row per team member (stored in the teamMembers array) along with all of the attributes of…

Question

Exhibits

Options

• Aselect t2.name AS memberName ,t2.registered AS registeredDttm ,t2.age AS age ,t2.eyeColor AS eyeColor from jCustRaw t1 ,lateral flatten (v) t2;
• Bselect t2.value:name::varchar AS memberName ,t2.value:registered::timestamp AS registeredDttm ,t2.value:age::number AS age ,t2.value:eyeColor::varchar AS eyeColor from jCustRaw t1 ,lateral flatten (input => v:teamMembers) t2;
• Cselect v:teamMembers.name::varchar AS memberName ,v:teamMembers.registered::timestamp AS registeredDttm ,v:teamMembers.age::number AS age ,v:teamMembers.eyeColor::varchar AS eyeColor from jCustRaw;
• Dselect v:teamMembers[0].name::varchar AS memberName ,v:teamMembers[0].registered::timestamp AS registeredDttm ,v:teamMembers[0].age::number AS age ,v:teamMembers[0].eyeColor::varchar AS eyeColor from jCustRaw;

• A
  3% (1)
• B
  76% (25)
• C
  15% (5)
• D
  6% (2)

Explanation

To return one row per element in a JSON array stored in a VARIANT column, Snowflake requires LATERAL FLATTEN targeting the specific array path. Option B correctly uses lateral flatten(input => v:teamMembers) to explode each element of the teamMembers array into its own row, then uses t2.value:fieldname::type syntax to extract and cast each attribute from the flattened element. Option A flattens the entire top-level VARIANT column (not the nested array) and uses incorrect column-reference syntax. Option C attempts to access fields directly on the array object itself (e.g., v:teamMembers.name), which does not iterate over array elements and will not return one row per member. Option D uses a hardcoded index [0] which only retrieves the first element of the array, producing one row per source row rather than one row per team member.

No community discussion yet for this question.