Menu

PostgreSQL JSONB 教程:操作符、查询与索引

用 jsonb 在 PostgreSQL 中存储和查询 JSON:-> 和 ->> 操作符、@> 包含查询、jsonb_set 等更新方法、把数组展开成行、GIN 索引,以及 json 和 jsonb 的区别。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

jsonb 是 PostgreSQL 的二进制 JSON 列类型。用 ->(返回 jsonb)和 ->>(返回 text)读取字段,用包含操作符 @> 查找行,用 jsonb_set 或 || 修改字段。GIN 索引能让这些查询变快。

订单 1 和 3 符合条件。data->'customer' 以 jsonb 形式返回嵌套对象,->>'name' 把名字取成文本,->> 再加一个类型转换,就把 total 变成了数字。

从 JSONB 中取值

操作符返回示例
-> 键或下标jsonbdata->'customer', data->'tags'->0
->> 键或下标textdata->>'status'
#> 路径jsonbdata#>'{customer,city}'
#>> 路径textdata#>>'{customer,city}'
[...] 下标语法(PostgreSQL 14+)jsonbdata['customer']['city']

数组下标从 0 开始,负数下标从末尾开始数(-> -1 是最后一个元素)。键不存在时得到 SQL NULL,而不是报错,所以上面的订单 3 的 city 是 NULL。

和文本比较或做类型转换时用 ->>:data->>'status' = 'paid'。要继续往下取时用 ->。一个常见错误是写成 data->'status' = 'paid'。PostgreSQL 会把 'paid' 当作 JSON 解析,而没加引号的单词不是合法的 JSON,查询会报错“invalid input syntax for type json”。

按 JSON 内容查找行

@> 判断左边的值是否包含右边的值。它在任意深度和数组内部都能用,并且有 GIN 索引支持:

商品 1 和 2 有 M 码并且有价格;只有 2 的价格超过 30。查找用的操作符:

  • @> 包含,<@ 被包含。
  • ? 键(或数组中的字符串)存在,?| 任意一个键存在,?& 所有键都存在。
  • @? 和 @@ 执行 JSON 路径查询(PostgreSQL 12+),用于在文档内部做比较。

更新 JSONB 中的字段

PostgreSQL 每次更新都会重写整个值,但用下面这些函数,你只需要描述改动的部分:

  • jsonb_set(target, path, new_value) 替换某个路径上的一个值。
  • - 删除一个键,#- 删除嵌套路径,比如 '{notify,sms}'。
  • || 添加或覆盖顶层的键。

从 PostgreSQL 14 开始,也可以通过下标赋值:SET prefs['notify']['email'] = 'false'。

两个陷阱:

  • 新值是 SQL NULL 时,jsonb_set 返回 NULL,这时 UPDATE 会把整列清空。可以传 COALESCE(to_jsonb(x), 'null'),或者用 jsonb_set_lax(PostgreSQL 13+),它默认存入 JSON null。
  • jsonb_set 会补上缺失的最后一个键,但不会补缺失的父级:在 {} 上设置 '{a,b}' 不会有任何变化。下标赋值则会创建它们。

把 JSON 数组展开成行

jsonb_array_elements 为数组的每个元素返回一行,jsonb_to_recordset 把对象数组变成带类型的列。从 PostgreSQL 17 开始,标准的 JSON_TABLE 一处就能同时做到这两件事:

A-1 卖出 3 件,B-2 卖出 1 件。反过来,jsonb_build_object('id', id, 'name', name) 用列构造一个对象,jsonb_agg 把多行收集成一个数组(见 string_agg 和 array_agg)。

用 GIN 给 JSONB 建索引

建在整列上的 GIN 索引可以用于 @>、?、?|、?&、@? 和 @@:

CREATE INDEX orders_data_idx ON orders USING gin (data);

-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);

-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));

查询要写成索引支持的形式。WHERE data @> '{"status": "paid"}' 可以用上 GIN 索引。WHERE data->>'status' = 'paid' 用不上,它需要表达式索引。像 (data->>'total')::numeric > 50 这样的范围判断,需要在完全相同的表达式上建表达式索引。

JSON 和 JSONB 的区别

jsonjsonb
存储形式输入的原始文本解析后的二进制形式
空白、键顺序、重复键保留去掉;重复键以最后一个为准
写入稍快(只做校验)输入时解析
读取和查询每次访问都重新解析快
@>、? 和 GIN 索引不支持支持

json 原样返回文本;jsonb 只保留 "a": 3,并重新排列了键的顺序。除非你必须逐字节返回原始文本,比如存下来的 webhook 请求体,否则都用 jsonb。每一行都有的字段,或者需要外键的字段,放在普通列里。官方 JSON 函数参考列出了所有操作符和函数。

常见问题

PostgreSQL 中 -> 和 ->> 有什么区别?

-> 返回 jsonb 类型的值,所以可以继续往下取(data->'customer'->'city')。->> 返回 text,用来和字符串比较或转换成数字:(data->>'total')::numeric。

怎么判断 JSONB 数组里是否包含某个值?

用包含操作符:data->'tags' @> '["gift"]',或者用 data @> '{"tags": ["gift"]}' 从文档顶层开始判断。对于字符串数组,data->'tags' ? 'gift' 也可以。在 data 上建 GIN 索引能加快第二种写法。

JSONB 会保留键的顺序吗?

不会。jsonb 按自己的顺序存储键,去掉空白,重复的键只保留最后一个。如果需要原样取回原始文本,请用 json 类型。

怎么判断 JSONB 中是否存在某个键?

当顶层对象有这个键时,data ? 'key' 为真,即使它的值是 JSON null。?| 接收一个数组,只要有任意一个键存在就为真;?& 要求所有键都存在。

能给 JSONB 里的字段加外键或 NOT NULL 约束吗?

不能直接加。CHECK 约束可以检查某个字段(CHECK (data ? 'email')),但外键需要真正的列。每一行都有、并且被其他表引用的字段,应该放在普通列里。

Coddy 编程语言插图

和 Coddy 一起学编程

开始