Arrow operators (-> and ->>) in PostgreSQL - Step-by-Step Execution

Concept Flow - Arrow operators (-> and ->>)

Start with JSON column

↓

Use -> operator

↓

Extract JSON object or array element

↓

Use ->> operator

↓

Extract JSON text value

↓

Use extracted value in query or output

The flow shows how to start with a JSON column, use -> to get JSON objects or arrays, and ->> to get text values from JSON.

Execution Sample

PostgreSQL

SELECT data->'name' AS name_json, data->>'name' AS name_text
FROM people;

This query extracts the 'name' field from a JSON column 'data' as JSON and as text.

Execution Table

Step	Expression	Evaluation	Result
1	data->'name'	Extract JSON value for key 'name'	{"first": "John", "last": "Doe"}
2	data->>'name'	Extract text value for key 'name'	{"first": "John", "last": "Doe"}
3	data->'age'	Extract JSON value for key 'age'	30
4	data->>'age'	Extract text value for key 'age'	30
5	data->'contacts'->0	Extract first element of 'contacts' array	{"type": "email", "value": "[e-mail]"}
6	data->'contacts'->0->>'value'	Extract text value of 'value' in first contact	[e-mail]
7	End of rows	No more data to extract	Query returns all rows with these extracted values

💡 All JSON arrow extractions completed for each row in the table.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 5	After Step 6	Final
data	{"name": {"first": "John", "last": "Doe"}, "age": 30, "contacts": [{"type": "email", "value": "[e-mail]"}]}	{"first": "John", "last": "Doe"}	{"first": "John", "last": "Doe"}	{"type": "email", "value": "[e-mail]"}	[e-mail]	Used in output columns

Key Moments - 2 Insights

Why does data->'name' return JSON but data->>'name' returns text?

The -> operator returns the JSON object or array as is (see execution_table step 1), while ->> returns the text inside that JSON value (see step 2).

Can ->> be used to extract nested values directly?

No, ->> extracts text only at one level. To get nested text, combine -> to go deeper, then ->> at the last step (see steps 5 and 6).

Visual Quiz - 3 Questions

Look at the execution_table at step 2. What is the type of the result from data->>'name'?

A Text string

B JSON object

C Integer

D Array

💡 Hint

Check the 'Evaluation' and 'Result' columns at step 2 in execution_table.

At which step does the query extract the first element of the 'contacts' array as JSON?

A Step 2

B Step 5

C Step 4

D Step 6

💡 Hint

Look for the step where data->'contacts'->0 is evaluated in execution_table.

If you want to get the text value of 'value' inside the first contact, which operator combination is used?

A -> only

B ->> only

C -> then ->>

D ->> then ->

💡 Hint

See steps 5 and 6 in execution_table for how nested extraction is done.

Concept Snapshot

Arrow operators in PostgreSQL JSON:
-> extracts JSON object or array element
->> extracts text value from JSON
Use -> for JSON output, ->> for plain text
Combine -> and ->> for nested extraction
Example: data->'key'->>'subkey'

Full Transcript

This visual execution shows how PostgreSQL arrow operators work with JSON data. The -> operator extracts JSON objects or arrays, while ->> extracts text values. For example, data->'name' returns a JSON object, but data->>'name' returns the text inside. Nested JSON values require combining -> and ->> operators. The execution table traces each step extracting values from a JSON column named data. Variable tracking shows how the data changes after each extraction. Key moments clarify common confusions about operator differences and nested extraction. The quiz tests understanding of operator results and usage. The snapshot summarizes the syntax and behavior for quick reference.