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

Sql Problem Overview

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

In this thread (https://stackoverflow.com/questions/19422233/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.

Sql Solutions

Solution 1 - Sql

-> returns json (or jsonb) 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

Solution 2 - Sql

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/

Solution 3 - Sql

Postgres offers 2 operators to get a JSON member:

the arrow operator: -> returns type JSON or JSONB
the double arrow operator: ->> returns type text

We must also understand that we now have 2 different kinds of null:

(null) postgres null type
null json/b null type

I created an example on jsfiddle

Let's create a simple table with a JSONB field:

create table json_test (
  id integer,
  val JSONB
);

and insert some test-data:

INSERT INTO json_test (id, val) values
(1, jsonb_build_object('member', null)),
(2, jsonb_build_object('member', 12)),
(3, null);

Output as we see it in sqlfiddle:

id	| val
----+-----------------
 1  | {"member": null}
 2  | {"member": 12}
 3  | (null)

Notes:

contains a JSONB object and the only field member is null
contains a JSONB object and the only field member has the numeric value 12
is (null): i.e. the whole column is (null) and does not contain a JSONB object at all

To better understand the differences, let's look at the types and null-checks:

SELECT id,
  val -> 'member'  as arrow,
  pg_typeof(val -> 'member')  as arrow_pg_type,
  val -> 'member' IS NULL as arrow_is_null,
  val ->> 'member' as dbl_arrow,
  pg_typeof(val ->> 'member')  as dbl_arrow_pg_type,
  val ->> 'member' IS NULL as dbl_arrow_is_null,
  CASE WHEN jsonb_typeof(val -> 'member') = 'null' THEN true ELSE false END as is_json_null
from json_test;

Output:

id	arrow	arrow_pg_type	arrow_is_null	dbl_arrow	dbl_arrow_pg_type	dbl_arrow_is_null	is_json_null
1	null	jsonb	false	(null)	text	true	true
2	12	jsonb	false	12	text	false	false
3	(null)	jsonb	true	(null)	text	true	false