Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 376
tim_xyz Avatar asked Aug 04 '16 21:08

tim_xyz


People also ask

What does -> mean in PostgreSQL?

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.

How do I get the difference between two timestamps in PostgreSQL?

To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure . The difference will be of the type interval , which means you'll see it in days, hours, minutes, and seconds.

Is PostgreSQL better than MySQL?

Both PostgreSQL and MySQL are time-proven solutions that can compete with enterprise solutions such as Oracle and SQL Server. MySQL has been famous for its ease of use and speed, while PostgreSQL has many more advanced features, which is the reason that PostgreSQL is often described as an open-source version of Oracle.


3 Answers

-> 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
like image 100
Clodoaldo Neto Avatar answered Oct 13 '22 17:10

Clodoaldo Neto


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/

like image 60
Eddy Bayonne Avatar answered Oct 13 '22 18:10

Eddy Bayonne


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

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
like image 27
TmTron Avatar answered Oct 13 '22 17:10

TmTron