Semi-structured data handling (JSON) in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to extract the 'name' field from the JSON column 'data'.

dbt

SELECT data:[1] AS name FROM users

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

Aaddress

Bage

Cname

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Using dot notation instead of colon for JSON extraction.

Selecting a wrong key name that doesn't exist in the JSON.

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

2fill in blank

medium

Complete the code to filter rows where the JSON field 'status' equals 'active'.

dbt

SELECT * FROM users WHERE data:[1] = 'active'

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

Astatus

Btype

Clevel

Drole

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong JSON key that does not represent status.

Forgetting to use quotes around the string 'active'.

The condition data:status = 'active' filters rows where the JSON field 'status' is 'active'.

3fill in blank

hard

Fix the error in the code to correctly extract the 'city' from the nested JSON field 'address'.

dbt

SELECT data:address[1]city AS city FROM users

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

A:

B.

C->

D->>

Attempts:

3 left

💡 Hint

Common Mistakes

Using dot notation instead of colon for nested JSON.

Using arrow operators which are not supported in dbt SQL.

In dbt SQL, use : to access nested JSON fields, so data:address:city extracts the city.

4fill in blank

hard

Fill both blanks to create a dictionary of word lengths for words longer than 3 characters.

dbt

SELECT [1] AS word, LENGTH([2]) AS length FROM words WHERE LENGTH(word) > 3

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

Aword

Bwords

Dtext

Attempts:

3 left

💡 Hint

Common Mistakes

Using different column names for word and length calculation.

Using table name instead of column name inside LENGTH.

The column word is selected as the word, and LENGTH(word) calculates its length.

5fill in blank

hard

Fill all three blanks to create a filtered dictionary of uppercase words and their lengths greater than 4.

dbt

SELECT [1] AS word_upper, LENGTH([2]) AS length FROM words WHERE LENGTH([3]) > 4

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

AUPPER(word)

Bword

DLOWER(word)

Attempts:

3 left

💡 Hint

Common Mistakes

Using LOWER instead of UPPER for the first blank.

Using different columns for length and filtering.

Use UPPER(word) to get uppercase words, and word for length and filtering.