Postgres SQL 中的 `->>` 和 `->` 有什么区别？

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

提问by tim_xyz

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

SQL 中的->>和 有什么区别->？

In this thread (Check if field exists in json type column postgresql), the answerer basically recommends using,

在此线程中（检查 json 类型列 postgresql 中是否存在字段），回答者基本上建议使用，

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.

为什么使用单箭头而不是双箭头？以我有限的经验，两者都做同样的事情。

采纳答案by Clodoaldo Neto

->returns json(b)and ->>returns text:

->返回json(b)和->>返回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

回答by Eddy Bayonne

PostgreSQL provides two native operators ->and ->>to help you query JSON data.

PostgreSQL 提供了两个本机运算符->并->>帮助您查询 JSON 数据。

The operator ->returns JSON object field as JSON. The operator ->>returns JSON object field as text.

运算符->以 JSON 形式返回 JSON 对象字段。运算符->>以文本形式返回 JSON 对象字段。

The following query uses operator ->to get all customers in form of JSON:

以下查询使用运算符->以 JSON 的形式获取所有客户：

And the following query uses operator ->>to get all customers in form of text:

以下查询使用运算符->>以文本形式获取所有客户：

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

您可以在以下链接中查看更多详细信息 http://www.postgresqltutorial.com/postgresql-json/

回答by TmTron

Postgres offers 2 operators to get a JSON member:

Postgres 提供 2 个运算符来获取 JSON 成员：

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

• 箭头运算符：->返回类型 JSON 或 JSONB
• 双箭头运算符：->>返回类型文本

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

我们还必须明白，我们现在有两种不同的null：

• (null)postgres null type
• nulljson/b null type

• (null)postgres null 类型
• nulljson/b 空类型

I created an example on jsfiddle

我在jsfiddle上创建了一个例子

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

让我们创建一个带有 JSONB 字段的简单表：

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:

我们在 sqlfiddle 中看到的输出：

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

Notes:

笔记：

1. contains a JSONB object and the only field memberis null
2. contains a JSONB object and the only field memberhas the numeric value 12
3. is (null): i.e. the whole column is (null)and does not contain a JSONB object at all

1. 包含一个 JSONB 对象并且唯一的字段member为null
2. 包含一个 JSONB 对象并且唯一的字段member具有数值12
3. is (null): 即整个列是(null)并且根本不包含 JSONB 对象

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 NULLis false
  • val ->> 'member' IS NULLis true
• is_json_nullcan be used to get onlythe json-nullcondition

• 对于{"member": null}：
  • val -> 'member' IS NULL是假的
  • val ->> 'member' IS NULL是真的
• is_json_null可用于仅获取 json- null条件