-> vs ->> in PostgreSQL: Key Differences and Usage

In PostgreSQL, the -> operator extracts a JSON object field as JSON, while the ->> operator extracts the field as text. Use -> when you want to keep the JSON format, and ->> when you need plain text output.

Quick Comparison

This table summarizes the main differences between -> and ->> operators in PostgreSQL JSON handling.

Feature	->	->>
Return Type	JSON or JSONB	Text
Extracts	JSON object field or array element	Text value of JSON field
Use Case	When JSON structure is needed	When plain text is needed
Example Output	{"name": "John"}	John
Supports Nested Access	Yes	Yes
Can be used in JSONB and JSON	Yes	Yes

Key Differences

The -> operator in PostgreSQL extracts a JSON object field or array element and returns it as JSON or JSONB type. This means the output preserves the JSON formatting, including quotes, braces, or brackets. It is useful when you want to work further with JSON data or extract nested JSON objects.

On the other hand, the ->> operator extracts the same JSON field but returns it as plain text. This means it strips away the JSON formatting and returns only the raw string or number value. This is helpful when you want to use the extracted value directly in text comparisons, display, or other operations that require text.

Both operators support nested access by chaining, for example data->'address'->>'city'. However, the key difference is the output type: JSON for -> and text for ->>. Choosing between them depends on whether you need to keep the JSON structure or just want the raw value.

Code Comparison

sql

SELECT
  data->'name' AS name_json,
  data->'age' AS age_json
FROM (VALUES ('{"name": "Alice", "age": 30}'::json)) AS t(data);

Output

 name_json | age_json 
-----------+----------
 "Alice"  | 30       
(1 row)

->> Equivalent

sql

SELECT
  data->>'name' AS name_text,
  data->>'age' AS age_text
FROM (VALUES ('{"name": "Alice", "age": 30}'::json)) AS t(data);

Output

 name_text | age_text 
-----------+----------
 Alice     | 30       
(1 row)

When to Use Which

Choose -> when you want to extract a JSON object or array element and keep its JSON format for further JSON processing or nested queries. Use ->> when you need the extracted value as plain text for display, filtering, or string operations. If you want to compare or manipulate the value as a string, ->> is the better choice. For JSON structure handling, -> is preferred.

Key Takeaways

Use -> to extract JSON fields as JSON objects or arrays.

Use ->> to extract JSON fields as plain text values.

-> preserves JSON formatting; ->> returns raw text.

Both operators support nested JSON access by chaining.

Choose based on whether you need JSON structure or plain text output.