SnowPro Advanced Data Engineer

Question

The JSON below is stored in a VARIANT column called V in a table called jCustRaw:

Question Image

Which query returns one row for every team member stored in the teamMembers array, together with all attributes for each team member?

Explanation

Snowflake FLATTEN applied laterally to v:teamMembers produces one row for every element of that array. Each element is an object exposed through the table function’s VALUE column, so member properties are accessed as t2.value:name, t2.value:registered, t2.value:age, and t2.value:eyeColor, with casts to the desired SQL types. Snowflake FLATTEN documentation

Learn more

SnowPro Advanced Data Engineer

AstroTutor

Notes

Readiness

QuestionQ115

Community Discussion