tim_xyz tim_xyz - 7 months ago 21
JSON Question

What is the difference between `->>` and `->` in Postgres SQL?

What is the difference between

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