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.

Practice

1. What does the PostgreSQL operator #> do when used with JSON data?

easy

A. Deletes JSON data at a specified path

B. Extracts text at a specified path

C. Extracts JSON data at a specified path

D. Updates JSON data at a specified path

Solution

1. Step 1: Understand the operator #>

   This operator extracts JSON data from a JSON column or value at the given path as JSON type.

2. Step 2: Compare with similar operator #>>

   The #>> operator extracts text, not JSON, at the path.

3. Final Answer:

   Extracts JSON data at a specified path -> Option C

4. Quick Check:

   #> extracts JSON data [OK]

Hint: Remember #> returns JSON, #>> returns text [OK]

Common Mistakes:

• Confusing #> with #>>
• Thinking #> extracts text
• Assuming #> modifies JSON

2. Which of the following is the correct syntax to extract the text value of the key 'name' from a JSON column data using the #>> operator?

easy

A. SELECT data->'name' FROM table;

B. SELECT data #>> '{name}' FROM table;

C. SELECT data->>'name' FROM table;

D. SELECT data #> '{name}' FROM table;

Solution

1. Step 1: Understand #>> syntax

   The #>> operator requires a text array path inside curly braces, e.g., '{name}', to extract text.

2. Step 2: Compare with other operators

   ->> extracts text but uses a different syntax without braces; #> extracts JSON, not text.

3. Final Answer:

   SELECT data #>> '{name}' FROM table; -> Option B

4. Quick Check:

   Correct syntax for #>> uses braces and returns text [OK]

Hint: Use braces '{}' with #>> for text extraction [OK]

Common Mistakes:

• Using #> instead of #>> for text extraction
• Omitting braces around path
• Confusing ->> with #>> syntax

3. Given the JSON column info with value '{"person": {"name": "Alice", "age": 30}}', what is the result of the query:
SELECT info #>> '{person,name}' FROM users;

medium

A. Alice

B. "Alice"

C. {"name": "Alice"}

D. NULL

Solution

1. Step 1: Understand the JSON and path

   The JSON has a nested object under 'person' with key 'name' having value "Alice".

2. Step 2: Apply #>> operator

   #>> extracts the text value at the path, so it returns the string without quotes.

3. Final Answer:

   Alice -> Option A

4. Quick Check:

   #>> returns text without quotes [OK]

Hint: Text extraction returns unquoted string [OK]

Common Mistakes:

• Expecting JSON with quotes returned
• Confusing #> and #>> output types
• Assuming NULL if nested path

4. Consider the query:
SELECT data #>> '{address,city}' FROM customers;
If this query returns NULL unexpectedly, what is the most likely cause?

medium

A. The path '{address,city}' does not exist in some JSON rows

B. The operator #>> is used incorrectly and should be #>

C. The JSON column is not valid JSON format

D. The query syntax is invalid

Solution

1. Step 1: Understand NULL from JSON path extraction

   If the specified path does not exist in the JSON data, the operator returns NULL.

2. Step 2: Check other options

   The syntax is correct; #>> is valid for text extraction; invalid JSON would cause errors, not NULL.

3. Final Answer:

   The path '{address,city}' does not exist in some JSON rows -> Option A

4. Quick Check:

   Missing path returns NULL [OK]

Hint: NULL means path missing, not syntax error [OK]

Common Mistakes:

• Assuming syntax error causes NULL
• Confusing #> and #>> usage
• Ignoring missing keys in JSON

5. You have a JSON column profile with nested data:
{"user": {"details": {"email": "[e-mail]", "phone": "12345"}}}
Which query correctly extracts the email as text using the #>> operator?

hard

A. SELECT profile #>> 'user.details.email' FROM accounts;

B. SELECT profile #> '{user,details,email}' FROM accounts;

C. SELECT profile->'user'->'details'->>'email' FROM accounts;

D. SELECT profile #>> '{user,details,email}' FROM accounts;

Solution

1. Step 1: Understand the nested JSON path

   The email is nested under keys user, details, then email.

2. Step 2: Use correct operator and path syntax

   #>> extracts text and requires path as an array in braces: '{user,details,email}'.

3. Step 3: Evaluate other options

   #> returns JSON, not text; SELECT profile->'user'->'details'->>'email' FROM accounts; uses different operators but is valid syntax; SELECT profile #>> 'user.details.email' FROM accounts; uses incorrect path format.

4. Final Answer:

   SELECT profile #>> '{user,details,email}' FROM accounts; -> Option D

5. Quick Check:

   Use #>> with array path in braces for text extraction [OK]

Hint: Use braces with comma-separated keys for #>> path [OK]

Common Mistakes:

• Using dot notation inside quotes for path
• Using #> instead of #>> for text
• Confusing operator syntax with -> and ->>