Path extraction with #> and #>> in PostgreSQL - Step-by-Step Execution

Concept Flow - Path extraction with #> and #>>

Start with JSON column

↓

Use #> operator

↓

Extract JSON object/array at path

↓

Result is JSON

↓

Use #>> operator

↓

Extract text at path

↓

Result is text

↓

Use result in query or output

This flow shows how to extract parts of a JSON column using #> to get JSON and #>> to get text from a specified path.

Execution Sample

PostgreSQL

SELECT data #> '{address,city}' AS city_json,
       data #>> '{address,city}' AS city_text
FROM users;

Extracts the city from the address in JSON format and as plain text from the users table.

Execution Table

Step	Operation	Input JSON	Path	Operator	Output
1	Start with JSON column	{"name":"John","address":{"city":"NY","zip":"10001"}}			{"name":"John","address":{"city":"NY","zip":"10001"}}
2	Apply #> operator	{"name":"John","address":{"city":"NY","zip":"10001"}}	{address,city}	#>	"NY" (JSON string)
3	Result of #> operator				"NY" (JSON string)
4	Apply #>> operator	{"name":"John","address":{"city":"NY","zip":"10001"}}	{address,city}	#>>	NY (text)
5	Result of #>> operator				NY (text)
6	End of extraction				Extraction complete

💡 Extraction stops after returning JSON or text value from the specified path.

Variable Tracker

Variable	Start	After #> extraction	After #>> extraction	Final
data	{"name":"John","address":{"city":"NY","zip":"10001"}}	"NY" (JSON)	NY (text)	NY (text)

Key Moments - 2 Insights

Why does #> return JSON but #>> returns text?

Because #> extracts the JSON value at the path preserving its type (like string, object), while #>> converts the JSON value to plain text. See execution_table rows 3 and 5.

What happens if the path does not exist in the JSON?

Both operators return NULL if the path is missing. This is important to handle in queries to avoid errors.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output of the #> operator at step 3?

A NULL

B NY as plain text

C "NY" as JSON string

D {"city":"NY"}

💡 Hint

Check the 'Output' column at step 3 in execution_table.

At which step does the #>> operator return the city as plain text?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Look for #>> operator usage and output in execution_table rows.

If the path '{address,city}' does not exist, what would be the output of #>>?

A NULL

B Empty string

C Error

D Original JSON

💡 Hint

Refer to key_moments about missing path behavior.

Concept Snapshot

Path extraction operators in PostgreSQL JSON:
#> extracts JSON value at path (returns JSON)
#>> extracts text at path (returns text)
Path is an array of keys, e.g. '{address,city}'
If path missing, returns NULL
Use to get nested JSON data easily

Full Transcript

This visual execution shows how PostgreSQL JSON path extraction operators #> and #>> work. Starting with a JSON column, #> extracts the JSON value at the given path, preserving its JSON type. Then #>> extracts the text value at the same path, converting JSON to plain text. The execution table traces each step with input JSON, path, operator used, and output. Variables track the JSON data and extracted values. Key moments clarify why #> returns JSON and #>> returns text, and what happens if the path is missing. The quiz tests understanding of outputs at specific steps and behavior on missing paths. The snapshot summarizes syntax and behavior for quick reference.

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