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

-> 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 customerFROM 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 customerFROM orders;

customer--------John DoeLily BushJosh WilliamMary Clark

You can see more details in the link belowhttp://www.postgresqltutorial.com/postgresql-json/

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:

1. contains a JSONB object and the only field member is null
2. contains a JSONB object and the only field member has the numeric value 12
3. 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_nullfrom 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        |+----+--------+---------------+---------------+-----------+-------------------+-------------------+--------------+

Notes:

• for {"member": null}:
  • val -> 'member' IS NULL is false
  • val ->> 'member' IS NULL is true
• is_json_null can be used to get only the json-null condition