PostgreSQL JSONB 怎么用？如何建立索引？

简化版

PostgreSQL 支持 json 和 jsonb。json 更接近原始文本保存，jsonb 会解析成二进制格式，更适合查询和索引。实际业务中如果要按 JSON 字段查询，通常优先用 jsonb，并根据查询方式建立 GIN 索引或表达式索引。但 JSONB 不应该滥用来替代清晰的关系模型。

详细版

JSONB 常见用法包括：

存储扩展属性、配置、埋点上下文；
用 ->、->> 取字段；
用 @> 做包含查询；
用 GIN 索引加速 JSONB 包含、键存在等查询；
用表达式索引加速某个固定路径字段查询。

例如：

CREATE INDEX idx_order_extra_gin ON orders USING gin(extra);

SELECT * FROM orders
WHERE extra @> '{"channel":"app"}';

如果经常按 extra->>'channel' 过滤，也可以考虑：

CREATE INDEX idx_order_channel ON orders ((extra->>'channel'));

完整版教学

一、json 和 jsonb 的区别

PostgreSQL 同时提供 json 和 jsonb：

json 保存输入文本，保留格式细节；
jsonb 保存解析后的二进制表示；
jsonb 查询和索引能力更强；
jsonb 通常不保留输入时的空格、键顺序等文本细节。

如果只是原样保存、很少查询，json 可以满足；如果要频繁按内部字段过滤、判断包含、建立索引，通常选择 jsonb。

二、JSONB 适合保存半结构化扩展信息

JSONB 适合字段不固定、变化快、不是核心关系约束的数据，例如：

商品扩展属性；
用户偏好配置；
事件埋点上下文；
第三方接口返回的附加信息；
少量低频筛选的灵活字段。

但如果字段是核心业务字段，比如订单状态、用户 ID、支付金额，不建议只塞进 JSONB。它们应该作为明确列存在，方便约束、索引、统计和 JOIN。

三、常见查询操作

假设订单表有 extra jsonb：

SELECT extra->'buyer' FROM orders;
SELECT extra->>'channel' FROM orders;
SELECT * FROM orders WHERE extra @> '{"channel":"app"}';

其中：

-> 返回 JSON 值；
->> 返回文本；
@> 判断左侧 JSONB 是否包含右侧 JSONB。

面试时可以强调：不同操作符能否使用索引，取决于索引类型和表达式是否匹配。

四、GIN 索引适合 JSONB 包含查询

如果经常做包含查询，可以创建 GIN 索引：

CREATE INDEX idx_orders_extra_gin ON orders USING gin(extra);

它适合：

SELECT * FROM orders
WHERE extra @> '{"channel":"app"}';

GIN 索引能提高 JSONB 查询能力，但也会增加写入和更新成本。如果 JSONB 字段很大、写入非常频繁，索引维护成本要提前评估。

五、表达式索引适合固定路径查询

如果业务经常按某一个 JSONB 路径过滤，例如：

SELECT * FROM orders
WHERE extra->>'channel' = 'app';

可以建立表达式索引：

CREATE INDEX idx_orders_channel ON orders ((extra->>'channel'));

这类索引更轻、更精准，但只服务特定表达式。SQL 写法变化后，优化器未必能匹配到这个索引。

六、JSONB 的设计边界

JSONB 很灵活，但不能把它当成“万能字段”。滥用 JSONB 会带来：

约束难表达；
数据质量难保证；
查询条件隐藏在结构内部；
统计信息不如普通列直接；
后期迁移和治理成本高。

推荐原则是：核心字段列化，扩展字段 JSONB；高频过滤字段列化，低频灵活字段 JSONB。

七、常见误区与追问

查询写法	更匹配的索引	说明
`extra @> '{"channel":"app"}'`	GIN on `extra`	包含查询
`extra->>'channel' = 'app'`	表达式索引	固定路径文本过滤
高频 JOIN/排序字段	普通列 + B-tree	核心关系字段不要藏在 JSONB

易错点：JSONB 是半结构化补充，不是偷懒建模工具。越是核心、稳定、高频过滤、需要约束的字段，越应该列化。

假设订单表 5000 万行，extra 平均 2KB，只是偶尔查 channel。如果全字段建 GIN，写入和更新都要维护较重索引；如果 90% 查询只按 extra->>'channel' 过滤，一个表达式索引可能更小、更准。反过来，如果要查多个灵活键是否包含，GIN 才更合适。

误区：jsonb 一定比 json 好。 如果只是原样保存、不查询内部字段，json 也可以；需要查询和索引时通常选 jsonb。
误区：建了 GIN 索引，所有 JSONB 查询都会变快。 SQL 操作符和索引类型要匹配，固定路径比较可能需要表达式索引。
误区：把所有扩展字段放 JSONB 就不需要改表了。 这样会牺牲约束、统计、索引治理和数据质量，后期治理成本可能更高。
追问：-> 和 ->> 有什么区别？ -> 返回 JSON 值，->> 返回文本；表达式索引和比较类型要按实际写法匹配。
追问：什么时候把 JSONB 字段列化？ 高频过滤、排序、JOIN、需要唯一/非空/CHECK 约束、报表统计稳定使用时，应考虑迁移为普通列。
追问：JSONB 更新有什么成本？ JSONB 字段较大时更新可能产生更多行版本和索引维护成本，频繁更新要评估表膨胀和 VACUUM 压力。

八、加强记忆

JSONB 的记忆线是：要查询和索引就优先 JSONB，包含查询配 GIN，固定路径查询配表达式索引，核心业务字段不要全塞 JSONB。它是关系模型的补充，不是关系模型的替代品。

简化版

详细版