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.