What is the difference between `->>` and `->` in Postgres SQL?
What is the difference between ->>
and ->
in SQL?
In this thread (Check if field exists in json type column postgresql), the answerer basically recommends using,
json->'attribute' is not null
instead of,
json->>'attribute' is not null
Why use a single arrow instead of a double arrow? In my limited experience, both do the same thing.
->
returns json(b) and ->>
returns text
:
with t (jo, ja) as (values
('{"a":"b"}'::jsonb,('[1,2]')::jsonb)
)
select
pg_typeof(jo -> 'a'), pg_typeof(jo ->> 'a'),
pg_typeof(ja -> 1), pg_typeof(ja ->> 1)
from t
;
pg_typeof | pg_typeof | pg_typeof | pg_typeof
-----------+-----------+-----------+-----------
jsonb | text | jsonb | text
PostgreSQL provides two native operators ->
and ->>
to help you query JSON data.
The operator ->
returns JSON object field as JSON.
The operator ->>
returns JSON object field as text.
The following query uses operator ->
to get all customers in form of JSON:
SELECT
info -> 'customer' AS customer
FROM
orders;
customer
--------
"John Doe"
"Lily Bush"
"Josh William"
"Mary Clark"
And the following query uses operator ->>
to get all customers in form of text:
SELECT
info ->> 'customer' AS customer
FROM
orders;
customer
--------
John Doe
Lily Bush
Josh William
Mary Clark
You can see more details in the link below http://www.postgresqltutorial.com/postgresql-json/