Path extraction with #> and #>> in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Extracting a JSON object using #>

Given the table data with a JSONB column info containing:

{"user": {"name": "Alice", "age": 30}}

What is the output of this query?

SELECT info #> '{user}' AS result FROM data;

PostgreSQL

CREATE TABLE data (info JSONB);
INSERT INTO data VALUES ('{"user": {"name": "Alice", "age": 30}}');

A {"user": {"name": "Alice", "age": 30}}

B {"name": "Alice", "age": 30}

C NULL

D "{\"name\": \"Alice\", \"age\": 30}"

💡 Hint

The operator #> extracts a JSON object at the specified path as JSON, not as text.

The #> operator extracts the JSON object at the given path as JSONB. Here, the path '{user}' extracts the nested object {"name": "Alice", "age": 30}.

❓ query_result

intermediate

Extracting text from JSON using #>>

Given the same table data with JSONB column info containing:

{"user": {"name": "Alice", "age": 30}}

What is the output of this query?

SELECT info #>> '{user,name}' AS result FROM data;

PostgreSQL

CREATE TABLE data (info JSONB);
INSERT INTO data VALUES ('{"user": {"name": "Alice", "age": 30}}');

A Alice

B "Alice"

C {Alice}

D NULL

💡 Hint

The #>> operator extracts the value as text, without quotes.

The #>> operator extracts the value at the path as text. The path '{user,name}' points to the string "Alice", which is returned as plain text without quotes.

🧠 Conceptual

advanced

Difference between #> and #>> operators

Which statement correctly describes the difference between the PostgreSQL JSONB operators #> and #>>?

A Both #> and #>> return JSON objects, but #>> includes metadata.

B #> returns the value as text, while #>> returns a JSON object or array.

C #> returns a JSON object or array at the specified path, while #>> returns the value as text.

D #> extracts only scalar values, #>> extracts nested JSON objects.

💡 Hint

Think about the data type each operator returns.

The #> operator returns JSONB data (objects or arrays) at the specified path. The #>> operator returns the value as plain text, useful for extracting scalar values.

📝 Syntax

advanced

Identify the syntax error in JSON path extraction

Which of the following queries will cause a syntax error in PostgreSQL when extracting a JSON path?

A SELECT info #> 'user,age' FROM data;

B SELECT info #> '{user,age}' FROM data;

C SELECT info #>> '{user,age}' FROM data;

D SELECT info #>> '{user,age}' AS age FROM data;

💡 Hint

Check the format of the path argument for #> and #>> operators.

The path argument must be a text array literal enclosed in curly braces. Option A uses a plain string without braces, causing a syntax error.

❓ optimization

expert

Optimizing JSON path extraction in large datasets

You have a large table with a JSONB column data. You frequently query nested keys using #> and #>> operators. Which approach will improve query performance the most?

A Create a GIN index on the JSONB column and use the #> operator in queries.

B Create a B-tree index on the JSONB column and use the #>> operator in queries.

C Create no index and rely on sequential scans with #> operator.

D Create a GIN index on the JSONB column and extract values using #>> operator.

💡 Hint

Consider which index type supports JSONB path queries and text extraction efficiently.

GIN indexes support JSONB containment and path queries. Using #>> extracts text values which can be efficiently filtered with GIN indexes. B-tree indexes do not support JSONB well.

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