JSONB 与全文搜索

7.1 JSONB 操作符全览

PostgreSQL 的 JSONB 操作符是一套完整的 JSON 查询语言，配合 GIN 索引可以实现接近 MongoDB 的文档查询性能。

操作符	返回类型	说明	示例
-> 'key'	jsonb	获取对象字段（返回 jsonb）	'{"a":1}'::jsonb -> 'a' → 1
->> 'key'	text	获取对象字段（返回 text）	'{"a":"b"}'::jsonb ->> 'a' → b
-> 2	jsonb	获取数组元素（0-based）	'[1,2,3]'::jsonb -> 2 → 3
#> '{a,b}'	jsonb	路径访问（返回 jsonb）	'{"a":{"b":1}}'::jsonb #> '{a,b}' → 1
#>> '{a,b}'	text	路径访问（返回 text）	'{"a":{"b":"c"}}'::jsonb #>> '{a,b}' → c
@> '{...}'	bool	左边包含右边	'{"a":1,"b":2}'::jsonb @> '{"a":1}' → true
<@ '{...}'	bool	左边被右边包含	'{"a":1}'::jsonb <@ '{"a":1,"b":2}' → true
? 'key'	bool	顶层键是否存在	'{"a":1}'::jsonb ? 'a' → true
?| array	bool	任意一个键存在	'{"a":1}'::jsonb ?| ARRAY['a','b'] → true
?& array	bool	所有键都存在	'{"a":1,"b":2}'::jsonb ?& ARRAY['a','b'] → true
|| '{...}'	jsonb	合并两个 jsonb 对象	'{"a":1}'::jsonb || '{"b":2}' → {"a":1,"b":2}
- 'key'	jsonb	删除键	'{"a":1,"b":2}'::jsonb - 'a' → {"b":2}
#- '{a,b}'	jsonb	按路径删除	'{"a":{"b":1,"c":2}}'::jsonb #- '{a,b}' → {"a":{"c":2}}

7.2 JSONB 函数

SQL-- jsonb_set：修改指定路径的值
SELECT jsonb_set(
  '{"name":"Alice","score":90}'::JSONB,
  '{score}',         -- 路径
  '95'::JSONB,       -- 新值（必须是 jsonb）
  true               -- 如果路径不存在是否创建（默认 true）
);
-- 结果: {"name": "Alice", "score": 95}

-- jsonb_insert：在数组指定位置插入元素
SELECT jsonb_insert(
  '{"skills":["Go","Python"]}'::JSONB,
  '{skills,1}',       -- 在 index=1 之前插入
  '"Rust"'::JSONB
);
-- 结果: {"skills": ["Go", "Rust", "Python"]}

-- jsonb_strip_nulls：删除所有 null 值的键
SELECT jsonb_strip_nulls('{"a":1,"b":null,"c":{"d":null,"e":2}}'::JSONB);
-- 结果: {"a": 1, "c": {"e": 2}}

-- jsonb_each / jsonb_each_text：展开对象为键值对行
SELECT key, value
FROM jsonb_each('{"name":"Alice","age":30,"city":"Beijing"}'::JSONB);
-- 结果:
--  key  |  value
-- ------+---------
--  name | "Alice"
--  age  | 30
--  city | "Beijing"

-- jsonb_array_elements：展开数组为多行
SELECT value FROM jsonb_array_elements('["Go","Python","Rust"]'::JSONB);

-- jsonb_object_keys：获取所有顶层键
SELECT jsonb_object_keys('{"a":1,"b":2,"c":3}'::JSONB);

-- jsonb_agg：将多行聚合为 JSON 数组
SELECT
  category_id,
  jsonb_agg(jsonb_build_object('id', id, 'name', name, 'price', price)
    ORDER BY price
  ) AS products_json
FROM products
GROUP BY category_id;

7.3 GIN 索引加速 JSONB 查询

SQL-- 创建 GIN 索引（jsonb_ops：支持所有操作符，但体积较大）
CREATE INDEX idx_profiles_gin ON user_profiles USING GIN (profile);

-- jsonb_path_ops：只支持 @> 但索引更小，查询更快（推荐用于只需要 @> 的场景）
CREATE INDEX idx_profiles_path_ops ON user_profiles
  USING GIN (profile jsonb_path_ops);

-- 使用 GIN 索引的查询（必须用 @> 操作符）
EXPLAIN ANALYZE
SELECT * FROM user_profiles
WHERE profile @> '{"level": 5}';
-- 输出应看到 Bitmap Index Scan on idx_profiles_path_ops（命中 GIN 索引）

-- 对特定 JSONB 字段建立 B-Tree 索引（当只查询某个字段时更高效）
CREATE INDEX idx_profiles_level ON user_profiles
  ((profile->>'level'));  -- 对 level 字段的文本值建 B-Tree 索引

-- 查询时使用相同表达式
SELECT * FROM user_profiles
WHERE (profile->>'level')::integer > 5;  -- 可能不走上面的索引，需转换类型

-- 更好：对转换后的整型建索引
CREATE INDEX idx_profiles_level_int ON user_profiles
  (((profile->>'level')::integer));

7.4 全文搜索基础

PostgreSQL 内置了完整的全文搜索引擎，核心是两个类型：tsvector（已分词的文档向量）和 tsquery（搜索查询）。

• tsvector 存储文档的词位列表（lexemes），每个词带有位置信息和权重（A/B/C/D）。to_tsvector() 函数将文本转换为 tsvector，会自动完成分词、词干化（stemming）、去除停用词。
• tsquery 搜索查询，支持 &（AND）、|（OR）、!（NOT）、<->（相邻词）操作符。to_tsquery() 将文本转为规范化的查询，plainto_tsquery() 将普通短语转为 AND 查询，websearch_to_tsquery() 支持 Google 风格查询。
• @@ 操作符 tsvector 与 tsquery 的匹配操作符，返回布尔值。这是全文搜索的核心操作，可被 GIN 索引加速。

SQL-- 基础全文搜索
SELECT to_tsvector('english', 'PostgreSQL is the world's most advanced open source database');
-- 结果: 'advanc':7 'databas':10 'open':8 'postgresql':1 'sourc':9 'world':4
-- 注意：is/the/most 是英语停用词，被去除；advanced → advanc（词干化）

SELECT to_tsquery('english', 'PostgreSQL & database');
-- 结果: 'postgresql' & 'databas'

-- 执行全文搜索
SELECT
  title,
  to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'PostgreSQL') AS matches
FROM articles;

-- 使用 websearch_to_tsquery（推荐，用户友好）
SELECT title, body FROM articles
WHERE to_tsvector('english', title || ' ' || body)
   @@ websearch_to_tsquery('english', 'PostgreSQL database -MySQL');
-- websearch 语法：空格=AND, "短语"=短语匹配, -词=排除

7.5 生产环境全文搜索方案

SQL-- 建文章表，用生成列存储 tsvector（自动维护）
CREATE TABLE articles (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title        TEXT NOT NULL,
  body         TEXT NOT NULL,
  author_id    BIGINT,
  published_at TIMESTAMPTZ,
  -- 生成列：自动更新 tsvector，title 权重 A（最高），body 权重 B
  search_vec   TSVECTOR GENERATED ALWAYS AS (
    setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(body, '')), 'B')
  ) STORED
);

-- 对生成列创建 GIN 索引
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vec);

-- 查询（带相关性评分和高亮）
SELECT
  id,
  title,
  ts_rank(search_vec, query) AS rank,
  ts_headline(
    'english', body, query,
    'MaxWords=30, MinWords=10, ShortWord=3, HighlightAll=false, MaxFragments=2, FragmentDelimiter=" … "'
  ) AS snippet
FROM articles,
     websearch_to_tsquery('english', 'PostgreSQL performance') query
WHERE search_vec @@ query
ORDER BY rank DESC
LIMIT 10;

7.6 中文全文搜索

PostgreSQL 内置的文本搜索不支持中文分词（中文没有空格分隔词语）。需要安装扩展：

SQL-- 安装 zhparser（需先在系统级安装 scws）
CREATE EXTENSION zhparser;

-- 创建中文文本搜索配置
CREATE TEXT SEARCH CONFIGURATION chinese (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese
  ADD MAPPING FOR n,v,a,i,e,l WITH simple;
-- n=名词, v=动词, a=形容词, i=习语, e=叹词, l=习用语

-- 测试中文分词
SELECT to_tsvector('chinese', 'PostgreSQL是世界上最先进的开源关系型数据库');
-- 结果: 'postgresql':1 '世界':3 '先进':5 '关系':8 '数据库':9 '开源':7

-- 建表使用中文全文搜索
CREATE TABLE cn_articles (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      TEXT NOT NULL,
  content    TEXT NOT NULL,
  search_vec TSVECTOR GENERATED ALWAYS AS (
    setweight(to_tsvector('chinese', title),   'A') ||
    setweight(to_tsvector('chinese', content),  'B')
  ) STORED
);

CREATE INDEX idx_cn_articles_fts ON cn_articles USING GIN (search_vec);

-- 中文搜索查询
SELECT
  title,
  ts_rank(search_vec, query) AS rank
FROM cn_articles,
     to_tsquery('chinese', '数据库 & 性能') AS query
WHERE search_vec @@ query
ORDER BY rank DESC;