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'.

Practice

1. What is the Snowflake data type used to store semi-structured data like JSON or Avro?

easy

A. INTEGER

B. VARIANT

C. VARCHAR

D. BOOLEAN

Solution

1. Step 1: Understand Snowflake data types

   Snowflake uses specific data types for different data. VARIANT is designed for semi-structured data.

2. Step 2: Identify the correct type for JSON/Avro

   VARIANT can store JSON, Avro, XML, and other semi-structured formats directly.

3. Final Answer:

   VARIANT -> Option B

4. Quick Check:

   Semi-structured data type = VARIANT [OK]

Hint: Remember VARIANT stores JSON/Avro data in Snowflake [OK]

Common Mistakes:

• Choosing VARCHAR which stores plain text only
• Confusing INTEGER or BOOLEAN with semi-structured types
• Thinking JSON needs special external storage

2. Which of the following is the correct way to extract the value of the key name from a VARIANT column data containing JSON in Snowflake as a string?

easy

A. data:name

B. data['name']

C. data:name::string

D. data->'name'

Solution

1. Step 1: Understand JSON field extraction syntax in Snowflake

   Snowflake uses colon : to access JSON keys inside VARIANT columns.

2. Step 2: Cast extracted value to string for proper type

   Using ::string casts the extracted value to string, which is often needed for correct results.

3. Final Answer:

   data:name::string -> Option C

4. Quick Check:

   Extract and cast JSON key = data:name::string [OK]

Hint: Use colon and cast (::string) to get JSON string value [OK]

Common Mistakes:

• Using incorrect arrow syntax like data->'name'
• Not casting extracted value to string
• Using bracket notation data['name'] without casting to string

3. Given the JSON data stored in a VARIANT column data:

{"user": {"id": 101, "active": true}}

What will the query SELECT data:user:id::int FROM users; return?

medium

A. 101

B. "101"

C. true

D. NULL

Solution

1. Step 1: Access nested JSON key

   The query accesses user object then id key inside it.

2. Step 2: Cast the extracted value to integer

   The ::int cast converts the value 101 to integer type.

3. Final Answer:

   101 -> Option A

4. Quick Check:

   Nested JSON id cast to int = 101 [OK]

Hint: Use colon to access nested keys and cast to int for numbers [OK]

Common Mistakes:

• Returning string "101" without cast
• Confusing boolean true with id value
• Getting NULL due to wrong key access

4. You run the query SELECT data:user:active FROM users; but get NULL values even though the JSON has "active": true. What is the likely cause?

medium

A. Missing cast to BOOLEAN

B. JSON key is case-sensitive and should be capitalized

C. Incorrect key path syntax

D. Column data is not VARIANT type

Solution

1. Step 1: Check data type of column

   If the column is not VARIANT, JSON path extraction returns NULL.

2. Step 2: Confirm correct key path and case

   The key path user:active is correct and JSON keys are case-sensitive but here lowercase matches JSON.

3. Final Answer:

   Column data is not VARIANT type -> Option D

4. Quick Check:

   Non-VARIANT column returns NULL on JSON path [OK]

Hint: Ensure column is VARIANT type to query JSON paths [OK]

Common Mistakes:

• Assuming missing cast causes NULL for boolean
• Using wrong key path syntax
• Ignoring data type of the column

5. You have a VARIANT column data storing JSON arrays like

{"items": [{"id": 1}, {"id": 2}, {"id": 3}]}

. Which query correctly extracts all id values from the items array as separate rows?

hard

A. SELECT value:id::int FROM users, LATERAL FLATTEN(input => data:items);

B. SELECT data:items:id FROM users;

C. SELECT data:items[0]:id FROM users;

D. SELECT FLATTEN(data:items):id FROM users;

Solution

1. Step 1: Use FLATTEN to expand JSON array

   FLATTEN function explodes the array into rows, each with a value field.

2. Step 2: Extract id from each value and cast to int

   Access value:id and cast to integer for each row.

3. Final Answer:

   SELECT value:id::int FROM users, LATERAL FLATTEN(input => data:items); -> Option A

4. Quick Check:

   Use FLATTEN with LATERAL and extract id from value [OK]

Hint: Use LATERAL FLATTEN to turn JSON arrays into rows [OK]

Common Mistakes:

• Trying to access array elements without FLATTEN
• Using incorrect syntax like data:items:id
• Not casting extracted values to int