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.
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.
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.
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.
->
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
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/
Postgres offers 2 operators to get a JSON member:
->
returns type JSON or JSONB->>
returns type textWe must also understand that we now have 2 different kinds of null:
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:
member
is null
member
has the numeric value 12
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:
{"member": null}
:
val -> 'member' IS NULL
is false
val ->> 'member' IS NULL
is trueis_json_null
can be used to get only the json-null conditionIf you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With