Semi-structured data querying (JSON, Avro) in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the 'name' field from a JSON column named 'data'.

Snowflake

SELECT data:[1] FROM users;

Drag options to blanks, or click blank then click option'

Aname

Bage

Caddress

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Using dot notation instead of colon for JSON path.

Selecting a field not present in the JSON.

The JSON path data:name extracts the 'name' field from the JSON column.

2fill in blank

medium

Complete the code to parse a VARIANT column 'raw_data' as JSON and extract the 'id' field.

Snowflake

SELECT raw_data:[1]::STRING FROM events;

Drag options to blanks, or click blank then click option'

Atype

Btimestamp

Cid

Dstatus

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong field name in JSON path.

Forgetting to cast the extracted value.

The JSON path raw_data:id extracts the 'id' field from the VARIANT column.

3fill in blank

hard

Fix the error in the code to extract the 'email' field from JSON column 'profile'.

Snowflake

SELECT profile[1]email FROM users;

Drag options to blanks, or click blank then click option'

A:email

B['email']

C.email

D->email

Attempts:

3 left

💡 Hint

Common Mistakes

Using dot notation instead of colon.

Using arrow operator which is invalid in Snowflake.

Snowflake uses colon : to access JSON fields, so profile:email is correct.

4fill in blank

hard

Fill both blanks to filter rows where the JSON field 'status' equals 'active'.

Snowflake

SELECT * FROM users WHERE profile[1] = '[2]';

Drag options to blanks, or click blank then click option'

A:status

Binactive

Cactive

D.status

Attempts:

3 left

💡 Hint

Common Mistakes

Using dot notation instead of colon.

Comparing to wrong string value.

Use :status to access the JSON field and compare it to the string 'active'.

5fill in blank

hard

Fill all three blanks to extract 'user_id', 'event_type', and filter events where 'success' is true.

Snowflake

SELECT event[1]user_id, event[2]event_type FROM logs WHERE event[3]success = true;

Drag options to blanks, or click blank then click option'

A:

Bevent_type

Duser_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using dot notation instead of colon.

Mixing field names and access operators.

Use colon : to access JSON fields. The fields are 'user_id', 'event_type', and 'success'.