jsonb 是 PostgreSQL 的二进制 JSON 列类型。用 ->(返回 jsonb)和 ->>(返回 text)读取字段,用包含操作符 @> 查找行,用 jsonb_set 或 || 修改字段。GIN 索引能让这些查询变快。
订单 1 和 3 符合条件。data->'customer' 以 jsonb 形式返回嵌套对象,->>'name' 把名字取成文本,->> 再加一个类型转换,就把 total 变成了数字。
从 JSONB 中取值
| 操作符 | 返回 | 示例 |
|---|---|---|
-> 键或下标 | jsonb | data->'customer', data->'tags'->0 |
->> 键或下标 | text | data->>'status' |
#> 路径 | jsonb | data#>'{customer,city}' |
#>> 路径 | text | data#>>'{customer,city}' |
[...] 下标语法(PostgreSQL 14+) | jsonb | data['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+),它默认存入 JSONnull。 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 的区别
json | jsonb | |
|---|---|---|
| 存储形式 | 输入的原始文本 | 解析后的二进制形式 |
| 空白、键顺序、重复键 | 保留 | 去掉;重复键以最后一个为准 |
| 写入 | 稍快(只做校验) | 输入时解析 |
| 读取和查询 | 每次访问都重新解析 | 快 |
@>、? 和 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')),但外键需要真正的列。每一行都有、并且被其他表引用的字段,应该放在普通列里。