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;