PostgreSQL JSONB — 文档存储与高效查询(重点)

这是本系列的重头戏。JSONB 是 PostgreSQL 区别于 MySQL 最闪光的特性——它让你像 MongoDB 一样存灵活文档,又能享受 SQL 的全部能力(事务、JOIN、复杂查询)。当业务字段经常变(埋点事件、商品属性、配置项),JSONB 让你不用每加一个字段就 ALTER TABLE。配合 GIN 索引,百万行文档也能毫秒查询。

1. 为什么需要 JSONB?

传统关系数据库有一个痛点:表结构是固定的。每加一个字段都要 ALTER TABLE,在繁忙的生产表上可能有锁表风险。但现代业务里,数据形态经常是半结构化的:

• 埋点事件:不同事件有不同的属性(click 事件有 page、view 事件有 duration)。
• 商品属性:图书有作者,电子产品有续航,服装有尺码——不同 SKU 的字段完全不同。
• 用户配置:每个用户的偏好、订阅、标签都不同。
• 第三方 API 数据:不同接口返回的字段不统一。

这些场景如果硬要建关系表,要么列爆炸(列名都叫 attr1, attr2, ...),要么拆成"实体-属性-值"三表(查询巨复杂)。JSONB 提供了第三条路:一列存下灵活文档,且能高效查询和索引。

2. JSON vs JSONB:差一个 B,本质完全不同

Postgres 同时支持 JSON 和 JSONB 两种类型,初学者常常困惑。先讲清它们的区别:

-- JSON vs JSONB:看似只差一个 B,本质完全不同

-- 1. JSON:存原始文本
--    优点:保留输入顺序(对象键的顺序)、空格
--    缺点:每次查询都要解析,无法高效索引,无法直接操作
CREATE TABLE raw_logs (
    payload JSON       -- 存原始字符串
);

-- 2. JSONB:存解析后的二进制(类似 BSON)
--    优点:查询飞快、支持 GIN 索引、操作符丰富
--    缺点:会丢失键的顺序、占稍多空间、写入稍慢(需要解析)
CREATE TABLE events (
    data JSONB         -- 存解析后的二进制
);

-- 99% 的场景都应该用 JSONB!
-- 只有"日志归档、只存不查、需要保留原始格式"时才考虑 JSON

-- 转换:JSON → JSONB,JSONB → JSON
SELECT '{"a":1}'::json::jsonb;      -- 把 json 字符串转成 jsonb
SELECT '{"a":1}'::jsonb::json;      -- 反过来(失去二进制优势)

-- JSONB 会自动去重并排序键
SELECT '{"b":1,"a":2,"b":3}'::jsonb;
-- 结果:{"a": 2, "b": 3}(重复的 b 被合并)

一句话原则:99% 的场景用 JSONB。只有"日志归档、只存不查、需要保留原始文本格式"时才考虑 JSON。

3. 基础:存取 JSONB

先建一张带 JSONB 列的表,然后插入数据:

-- 建表:JSONB 列存半结构化数据
CREATE TABLE events (
    id    SERIAL PRIMARY KEY,
    data  JSONB NOT NULL
);

-- 插入:JSON 字符串字面量会被自动解析
INSERT INTO events (data) VALUES
    ('{"type":"click","page":"home","uid":1}'),
    ('{"type":"view","page":"list","uid":2}'),
    ('{"type":"click","page":"cart","uid":1,"amount":88.5}'),
    ('{"type":"view","page":"home","uid":3,"meta":{"referer":"google","lang":"zh"}}');

-- 单条插入(用 ::jsonb 类型转换更明确)
INSERT INTO events (data) VALUES
    ('{"type":"scroll"}'::jsonb);

JSON 字符串字面量在 INSERT 时会被自动解析为 JSONB。建议显式加 ::jsonb 类型转换,既明确又能在出错时给出更清晰的报错。

4. 核心操作符(JSONB 的瑞士军刀)

JSONB 的强大来自其丰富的操作符集合。下面这些是日常用得最多的:

-- === JSONB 核心操作符 ===

-- 1. ->  按 key 取值,返回 JSONB(还能继续钻)
-- 2. ->> 按 key 取值,返回 TEXT(直接当字符串用)
SELECT data->'type'        FROM events LIMIT 1;   -- "click"(JSONB,带引号)
SELECT data->>'type'       FROM events LIMIT 1;   -- click(TEXT,无引号)
SELECT data->'meta'->>'lang' FROM events WHERE id = 4;   -- zh(链式取值)

-- 3. #>  按 path 数组取值(类似 MongoDB 的 dot notation)
-- 4. #>> 按 path 数组取值,返回 TEXT
SELECT data#>'{meta,referer}'  FROM events WHERE id = 4;  -- "google"
SELECT data#>>'{meta,referer}' FROM events WHERE id = 4;  -- google

-- === 包含与存在操作符(JSONB 的灵魂)==

-- 5. @>  左边 JSONB 是否"包含"右边这个结构(命中 GIN 索引,极快)
SELECT * FROM events WHERE data @> '{"type":"click"}';
SELECT * FROM events WHERE data @> '{"type":"click","uid":1}';

-- 6. <@  反向:左边是否被右边包含
SELECT * FROM events WHERE '{"type":"click"}' <@ data;

-- 7. ?  是否存在某个 key(或顶层元素)
SELECT * FROM events WHERE data ? 'amount';

-- 8. ?| 是否存在任一 key(数组形式)
SELECT * FROM events WHERE data ?| array['amount','meta'];

-- 9. ?& 是否同时存在所有 key
SELECT * FROM events WHERE data ?& array['type','uid'];

-- 10. || 拼接两个 JSONB(Postgres 9.5+ 之前需要 jsonb_concat)
UPDATE events SET data = data || '{"ts":"2026-08-05"}' WHERE id = 1;

-- 11. -  删除某个 key
UPDATE events SET data = data - 'amount' WHERE id = 3;

-- 12. #- 按 path 删除(深度删除)
UPDATE events SET data = data #- '{meta,referer}' WHERE id = 4;

重点记忆这五个:

• ->:按 key 取值,返回 JSONB(可继续钻)。
• ->>:按 key 取值,返回 TEXT(直接当字符串)。
• @>:包含查询(JSONB 的灵魂,命中 GIN 索引)。
• ?:是否存在某个 key。
• ||:拼接(用于新增/覆盖字段)。

5. 查询 JSONB 的几种姿势

查询 JSONB 有多种写法,各有适用场景:

-- === 查询 JSONB 的几种姿势 ===

-- 1. 直接用 ->> 提取并 WHERE
SELECT id, data->>'type' AS type, data->>'page' AS page
FROM events
WHERE data->>'type' = 'click'
ORDER BY id;
--  id | type  | page
-- ----+-------+------
--   1 | click | home
--   3 | click | cart

-- 2. 用 @> 包含查询(推荐!可命中 GIN 索引)
SELECT * FROM events
WHERE data @> '{"type":"click","uid":1}';
--  id |                              data
-- ----+------------------------------------------------------------------
--   1 | {"type": "click", "page": "home", "uid": 1}
--   3 | {"type": "click", "page": "cart", "uid": 1, "amount": 88.5}

-- 3. 提取所有键 / 值
SELECT jsonb_object_keys(data) FROM events WHERE id = 4;
--  type
--  page
--  uid
--  meta

SELECT * FROM jsonb_each(data) WHERE id = 4;
--   key   |        value
-- --------+----------------------
--  type   | "view"
--  page   | "home"
--  ...

-- 4. 展开成行表
SELECT id, (jsonb_each(data)).key, (jsonb_each(data)).value
FROM events;

-- 5. 数组展开(如果某个 key 是数组)
SELECT * FROM jsonb_array_elements('["a","b","c"]'::jsonb);
--  value
-- -------
--  "a"
--  "b"
--  "c"

-- 6. 路径查询(Postgres 12+ jsonb_path_query,SQL/JSON 标准)
SELECT * FROM jsonb_path_query(events, '$.uid ? (@ > 1)');

-- 7. 模糊匹配(LIKE on ->>text)
SELECT * FROM events WHERE data->>'page' LIKE 'h%';

选型建议:

• 等值匹配单个字段:用 ->> 提取后 =。
• 多字段组合查询:用 @>(可命中 GIN 索引)。
• 展开成行表做统计:用 jsonb_each / jsonb_array_elements。
• 复杂路径查询:用 SQL/JSON 路径表达式(jsonb_path_query,Postgres 12+)。

6. GIN 索引让 JSONB 飞起来

这是 JSONB 区别于 MySQL JSON 的关键。没建索引的 JSONB 查询永远走全表扫描——百万行表查询会慢到秒级。建了 GIN 索引后,同样的查询能毫秒返回。

-- === GIN 索引让 JSONB 查询飞起来 ===

-- 不建索引时,任何 @> / ? 查询都是 Seq Scan(全表扫描)
-- 百万行的表查询会从毫秒级降到秒级

-- 1. 建 GIN 索引(支持 @> ? ?| ?& 等所有包含/存在操作符)
CREATE INDEX idx_events_data ON events USING GIN (data);

-- 2. 验证索引是否被使用
EXPLAIN ANALYZE
SELECT * FROM events WHERE data @> '{"type":"click","uid":1}';
-- 输出:Bitmap Heap Scan ... Index Cond: (data @> ...)
--      Execution Time: 0.05 ms(索引命中,飞快)

-- 3. 如果只查特定 key,可以建"表达式索引"(更省空间)
CREATE INDEX idx_events_type ON events ((data->>'type'));
CREATE INDEX idx_events_uid  ON events (((data->>'uid')::int));
-- 这两个索引用于 ->> 等值查询,但 @> 查询用不上

-- 4. jsonb_path_ops:更小更快的 GIN 索引(只支持 @>)
CREATE INDEX idx_events_data_path ON events USING GIN (data jsonb_path_ops);
-- 体积比默认 GIN 小 50% 以上,但只支持 @> 一个操作符
-- 纯 @> 查询场景推荐用这个

-- 5. 默认 GIN vs jsonb_path_ops
--    默认:支持 @> ? ?| ?& 等,体积大
--    path_ops:只支持 @>,体积小,查询快
--    按业务场景选

-- 6. 部分索引(只为特定子集建索引)
CREATE INDEX idx_events_click ON events USING GIN (data)
WHERE data @> '{"type":"click"}';
-- 只有 click 事件被索引,体积更小

几个关键点:

• GIN 索引支持 @> ? ?| ?& 等所有包含/存在操作符,但不支持 ->> 等值查询。
• jsonb_path_ops 是更精简的 GIN:体积小 50%+,但只支持 @>。纯包含查询场景推荐。
• 表达式索引用于 ->> 等值:如 ((data->>'type')),比全 GIN 更省空间。
• 部分索引:只为高频子集建索引,体积更小。

7. 更新 JSONB(增删改字段)

JSONB 是不可变的——你不能"原地修改"某个字段,而是用一个表达式生成新的 JSONB 再整体替换。常用操作:

-- === 更新 JSONB(增删改字段) ===

-- 1. 顶层新增 / 覆盖字段(|| 拼接)
UPDATE events SET data = data || '{"ts":"2026-08-05","uid":99}' WHERE id = 1;
-- 结果:ts 和 uid 被添加/覆盖

-- 2. 删除某个 key(- 操作符)
UPDATE events SET data = data - 'amount' WHERE id = 3;

-- 3. 按 path 深度更新(jsonb_set 函数)
-- 语法:jsonb_set(target, path, new_value, create_if_missing)
UPDATE events
SET data = jsonb_set(data, '{meta,referer}', '"bing"', true)
WHERE id = 4;
-- 把 data.meta.referer 改成 "bing",不存在则创建

-- 4. 按 path 深度删除(#- 操作符)
UPDATE events SET data = data #- '{meta,referer}' WHERE id = 4;

-- 5. 数字原子加减(jsonb_build_object 配合)
UPDATE events
SET data = jsonb_set(data, '{amount}',
    to_jsonb((data->>'amount')::numeric + 10))
WHERE id = 3;

-- 6. 向数组里追加元素
UPDATE events
SET data = jsonb_set(data, '{tags}',
    COALESCE(data->'tags', '[]'::jsonb) || '"new_tag"')
WHERE id = 1;

jsonb_set 是深度更新的核心函数,可以钻到任意层级改值。create_if_missing 参数控制路径不存在时是否创建。

性能注意:JSONB 更新会重写整行(因为它是不可变的)。高频更新的字段建议单独拎出来做列,JSONB 存"低频变更"的属性。

8. JSONB vs MongoDB

这是经典对比。结论是:大多数场景,JSONB 已经能替代 MongoDB。

• 查询能力:Postgres 的 SQL 远比 MongoDB 的查询语言强大(支持 JOIN、复杂聚合、窗口函数、CTE)。
• 事务:Postgres 完整 ACID,MongoDB 直到 4.0 才有跨文档事务。
• 索引:GIN 索引的包含查询性能不输 MongoDB。
• 混合能力:Postgres 能同时存关系数据和 JSONB 文档,业务表 join 半结构化数据, MongoDB 做不到。
• MongoDB 的优势:分片集群更成熟、文档模型更纯粹(没有"表"的概念)、Schema 演化更灵活。

新项目如果已经在用 Postgres,且 JSONB 能满足需求,就没必要再引入 MongoDB——多一种数据库就多一份运维成本。

9. 典型应用场景

• 埋点事件表:所有事件存一张表,type 字段区分,具体属性存 JSONB。
• 商品 SKU:base 信息是关系列,扩展属性(颜色、尺寸、续航)存 JSONB。
• 用户配置 / 偏好:通知设置、隐私选项、订阅标签,字段经常变。
• API 请求 / 响应日志:存原始 payload,便于排查问题。
• 多语言文案:用 {"zh":"你好","en":"Hello"} 一列搞定。
• AB 测试参数:每个实验的配置不同,JSONB 灵活适配。

10. 何时不该用 JSONB?

JSONB 不是万能药。以下场景建议拆成关系列:

• 需要单独建索引的字段:虽然能建 GIN,但 B-tree 索引更高效。
• 需要外键约束:JSONB 内部无法引用其他表。
• 需要精确数值类型:JSONB 数字统一是 numeric,没有 int / float 区分。
• 经常 JOIN 的字段:JSONB 内部字段做 JOIN 性能差。
• 字段集合稳定:不会再变的关系数据,直接建表更规范。

经验法则:核心稳定字段建关系列,经常变的属性放 JSONB。两者配合使用最佳。

小结

这一章你深入掌握了 JSONB——Postgres 的杀手锏。要点:JSONB 不是 JSON 的别名而是二进制优化版;@> + GIN 索引是性能关键;JSONB 让 Postgres 兼具关系数据库和文档库的能力。下一篇我们看 索引 的完整体系。

← 上一篇 PostgreSQL 函数与运算符

下一篇 PostgreSQL 索引 →