Path extraction with #> and #>> in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to extract the JSON object at key 'address' using the #> operator.

PostgreSQL

SELECT data [1] '{address}' FROM users;

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

A->>

B#>>

C->

D#>

Attempts:

3 left

💡 Hint

Common Mistakes

Using ->> which returns text instead of JSON.

Using #>> which returns text, not JSON.

The #> operator extracts a JSON object at the specified path as JSON, which is suitable for nested JSON extraction.

2fill in blank

medium

Complete the code to extract the text value at path '{contact, phone}' using the #>> operator.

PostgreSQL

SELECT data [1] '{contact,phone}' FROM users;

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

A#>>

B#>

C->

D->>

Attempts:

3 left

💡 Hint

Common Mistakes

Using #> which returns JSON instead of text.

Using -> which only extracts one key, not a path.

The #>> operator extracts the JSON value at the path and returns it as text, which is useful for getting string values.

3fill in blank

hard

Fix the error in the code to extract the JSON object at path '{details, age}' using the correct operator.

PostgreSQL

SELECT data [1] '{details, age}' FROM users;

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

A#>

B#>>

C->

D->>

Attempts:

3 left

💡 Hint

Common Mistakes

Using ->> which only extracts one key and returns text.

Using #>> which returns text, not JSON.

The #> operator is correct for extracting a JSON object at a path given as a text array. The original code likely used an operator returning text or incorrect syntax.

4fill in blank

hard

Fill both blanks to extract the text value at path '{profile, email}' and alias it as user_email.

PostgreSQL

SELECT data [1] '{profile,email}' [2] user_email FROM users;

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

A#>>

BAS

C->>

DFROM

Attempts:

3 left

💡 Hint

Common Mistakes

Using #> which returns JSON instead of text.

Forgetting the AS keyword for aliasing.

The #>> operator extracts text at the given path. The AS keyword aliases the column name.

5fill in blank

hard

Fill all three blanks to extract the JSON object at path '{settings, theme}', convert it to text, and alias it as theme_setting.

PostgreSQL

SELECT data [1] '{settings,theme}' [2] theme_setting FROM users WHERE data [3] '{settings,theme}';

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

A#>

BAS

C?

D#>>

Attempts:

3 left

💡 Hint

Common Mistakes

Using #>> instead of #> when JSON output is needed.

Using incorrect operator for key existence check.

Use #> to extract JSON, AS to alias the column, and ? to check if the JSON path exists in the data.