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 each team member?
Exam SnowPro Advanced Data Engineer All QuestionsBrowse all questions from this exam
Question 13
Correct Answer: B
To return one row per team member along with all of their attributes from a JSON array stored in a Snowflake VARIANT column, you should use the FLATTEN function on the teamMembers array. This function essentially breaks down the array into individual elements, creating a normalized table-like structure. The correct syntax for such a query is: select f.value:age as age, f.value:eyeColor as eyeColor, f.value:name as name, f.value:registered as registered from jCustRaw, lateral flatten(input => jCustRaw.V:teamMembers) f;. This query specifically extracts each attribute of the team members from the array within the VARIANT column.
Discussion
stopthisnowOption: B
Technically, none of the statements work properly. The nearest valid option is B. create or replace table persons as select parse_json( '{ "_id": "4534554654640", "company": "Boo", "name": "Dean", "teamMembers": [ { "age":29, "eyeColor": "green" }, { "age":39, "eyeColor": "green2" } ] }' ) as c ; select f.value:eyeColor::varchar as eyeColor, f.value:age from TASK_DB.PUBLIC.PERSONS as p, lateral flatten(input => p:teamMembers) f;
BigDataBBOption: B
https://docs.snowflake.com/user-guide/semistructured-considerations#using-flatten-to-list-distinct-key-names