JSONB 查询与索引

这一课你将能做到

1. 熟练使用 ->、->>、@>、?、#>> 对 JSONB 做投影与过滤
2. 用 jsonb_set 更新嵌套字段，并理解「包含查询」为何适合索引
3. 为 JSONB 列创建 GIN 索引，用 EXPLAIN 观察是否走索引

为什么要学

产品常有「先上线核心列，再往扩展袋塞配置」的需求：课程标签、是否精选、A/B 开关、统计快照。PostgreSQL 的 JSONB 把文档放进关系行里，还能用运算符查询，并用 GIN 加速包含条件。

这是 PG 相对本站 MySQL JSON 的强项之一：MySQL 也有 JSON 函数，但 @> + GIN 这一套是 PG 日常半结构化方案的标准答案（见官方 JSON Types / JSONB Indexes）。

先建立图像

metadata JSONB 示例：
{
  "tags": ["sql", "pg"],
  "level": "intermediate",
  "featured": true,
  "stats": { "views": 100 }
}

运算符直觉：
  ->   取出仍是 jsonb（还能继续 ->）
  ->>  取出文本标量（给人看、做简单比较）
  @>   左边是否「包含」右边这份 jsonb
  ?    顶层是否有这个键
  #>>  按路径取文本

写法	结果类型	例子
metadata -> 'stats'	jsonb	{"views":100}
metadata ->> 'level'	text	intermediate
metadata @> '{"featured": true}'	boolean	过滤条件
metadata ? 'featured'	boolean	键存在
metadata #>> '{stats,views}'	text	100

跟着做

步骤 1：准备数据

确保存在 app.cf_track.metadata（见 pg-types）：

ALTER TABLE app.cf_track
  ADD COLUMN IF NOT EXISTS metadata JSONB NOT NULL DEFAULT '{}'::jsonb;

UPDATE app.cf_track SET metadata = '{
  "tags": ["sql", "backend"],
  "featured": false,
  "stats": {"views": 10}
}'::jsonb WHERE slug = 'sql-essentials';

UPDATE app.cf_track SET metadata = '{
  "tags": ["sql", "pg"],
  "featured": true,
  "level": "intermediate",
  "stats": {"views": 100}
}'::jsonb WHERE slug = 'postgresql-essentials';

步骤 2：投影与过滤

-- 取嵌套对象（仍是 jsonb）
SELECT slug, metadata -> 'stats' AS stats
FROM app.cf_track;

-- 取文本，便于展示
SELECT slug, metadata ->> 'level' AS level_text
FROM app.cf_track
WHERE metadata ? 'level';

-- 包含：带 sql 标签的课程（注意右边是 jsonb）
SELECT slug
FROM app.cf_track
WHERE metadata @> '{"tags": ["sql"]}'::jsonb;

-- 路径取文本
SELECT slug, metadata #>> '{stats,views}' AS views_text
FROM app.cf_track;

步骤 3：更新嵌套字段

UPDATE app.cf_track
SET metadata = jsonb_set(
  metadata,
  '{stats,views}',   -- 路径
  '101'::jsonb,      -- 新值必须是 jsonb
  true               -- create_missing：不存在则创建
)
WHERE slug = 'postgresql-essentials';

SELECT metadata #>> '{stats,views}' FROM app.cf_track
WHERE slug = 'postgresql-essentials';

整袋替换用 SET metadata = '…'::jsonb；局部改用 jsonb_set / || 合并。

步骤 4：创建 GIN 并看计划

CREATE INDEX IF NOT EXISTS idx_track_metadata_gin
  ON app.cf_track USING GIN (metadata);

EXPLAIN
SELECT slug FROM app.cf_track
WHERE metadata @> '{"featured": true}'::jsonb;

数据量小时优化器可能仍选 Seq Scan——正常。记住：可走索引的典型谓词是 @>、?、路径存在类，不是任意 ->>'x' = … 都能自动用上同一个 GIN（表达式索引是进阶话题）。

步骤 5：插入第三行做筛选练习

INSERT INTO app.cf_track (slug, title, category, level, sort_order, metadata)
VALUES (
  'redis-essentials',
  'Redis 入门',
  'language',
  'beginner',
  30,
  '{"tags": ["cache"], "featured": false}'::jsonb
)
ON CONFLICT (slug) DO UPDATE
SET metadata = EXCLUDED.metadata;

SELECT slug FROM app.cf_track
WHERE metadata @> '{"tags": ["cache"]}'::jsonb;

步骤 6：设计笔记

假设未来 cf_lesson 有 content_meta JSONB，写出 3 个值得放进 JSONB 的键，以及哪些查询会用 @>。只写键名与查询意图即可。

讲清楚要点

1. -> 保结构，->> 变文本：链式取嵌套用 ->，最后要比较字符串再用 ->>。
2. @> 是「包含」不是「相等」：左边可以有更多键；这是 GIN 最常见的匹配方式。
3. 因为 GIN 为倒排式多值设计，所以 适合 JSONB / 数组 / 全文，不适合替代 slug = ? 的 B-tree。
4. 更新要产生新 jsonb 值：行版本会变（MVCC）；高频改巨大 JSONB 要想拆列或分区策略。
5. 键命名要稳定：featured 与 isFeatured 混用会导致查询漏数据——约定胜于补丁。
6. 与 MySQL 对照：MySQL 有 JSON_EXTRACT / ->> 风格函数；PG 的 containment + GIN 更适合「文档里找包含关系」。

你可能会懵的地方

• @> '{"tags":["sql"]}' 为何要求数组里正好包含该元素？
  containment 按 JSON 结构匹配。标签数组包含 "sql" 即可，不要求数组完全相等。

• 为什么 ->>'featured' = 'true' 和 @> '{"featured":true}' 不一样？
  前者比的是文本；后者是 jsonb 布尔。索引友好度也不同，优先 containment。

• GIN 建了但 EXPLAIN 仍 Seq Scan？
  表太小、统计信息旧、或条件不可索引。ANALYZE app.cf_track; 后再看；小表 Seq Scan 可能更快。

• jsonb_set 第四参数忘记传？
  默认不创建缺失路径；要「没有就建」显式 true。

• 能不能替代所有扩展表？
  核心过滤字段仍建议真实列；JSONB 放真正易变、少 JOIN 的扩展。

错法对照

错法	问题	正解
右边写成普通字符串却用 @>	类型错误	'…'::jsonb
用 B-tree 指望加速 @>	用错索引种类	USING GIN (metadata)
把高频等值条件只放 JSONB	难约束、计划不稳	提升为真实列
每次更新整袋巨大文档	写放大	jsonb_set 局部更新或拆分
混用 JSON 与 JSONB 列	运算符/索引行为不一致	统一 JSONB

自测三问

1. 要取出 stats.views 的文本，写哪一种路径运算符？
2. 为什么「按是否精选过滤」更推荐 @> '{"featured": true}'？
3. GIN 与 B-tree 各擅长哪类谓词？

小结一句话

JSONB + 运算符 + GIN，是 PostgreSQL 处理半结构化扩展字段的标准组合。

官方延伸

• PostgreSQL：JSON Types
• PostgreSQL：JSONB Indexes