Problem
In SQL, what’s the difference between ->> and ->?
The answerer basically proposes utilizing in this thread (Check if field exists in json type column postgresql).
json->'attribute' is not null
instead of,
json->>'attribute' is not null
Why did you choose a single arrow over a double arrow? Both perform the same thing in my limited experience.
Asked by tim_xyz
Solution #1
->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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
Answered by Clodoaldo Neto
Solution #2
PostgreSQL has two native operators for querying JSON data: -> and ->>.
The operator -> converts a JSON object field to a JSON object field. The operator ->> returns a text representation of a JSON object field.
To get all customers in JSON format, use the operator -> in the following query:
SELECT
info -> 'customer' AS customer
FROM
orders;
customer
--------
"John Doe"
"Lily Bush"
"Josh William"
"Mary Clark"
The following query utilizes the operator ->> to retrieve all customers as text:
SELECT
info ->> 'customer' AS customer
FROM
orders;
customer
--------
John Doe
Lily Bush
Josh William
Mary Clark
More information is available at http://www.postgresqltutorial.com/postgresql-json/.
Answered by Eddy Bayonne
Solution #3
To retrieve a JSON member, Postgres provides two operators:
We must also recognize that there are now two types of null:
On jsfiddle, I made an example.
Let’s start by making a basic table with a JSONB field:
create table json_test (
id integer,
val JSONB
);
as well as some test data:
INSERT INTO json_test (id, val) values
(1, jsonb_build_object('member', null)),
(2, jsonb_build_object('member', 12)),
(3, null);
In sqlfiddle, the following is the output:
id | val
----+-----------------
1 | {"member": null}
2 | {"member": 12}
3 | (null)
Notes:
Let’s look at the types and null-checks to better grasp the differences:
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:
Answered by TmTron
Post is based on https://stackoverflow.com/questions/38777535/what-is-the-difference-between-and-in-postgres-sql