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.