卷 III · 类型CH 14深度 14/24

JSONB：关系库里的文档

「一半的字段是固定的，一半是灵活多变的」——这种半结构化数据，曾经逼人在关系库和文档库之间二选一。PostgreSQL 的 jsonb 给了第三条路：在关系表里存文档，还能索引、能查询。好用到危险，所以这一章也会认真讲「什么时候别用它」。

jsonb-> 与 ->>@> 包含GIN 索引

json 还是 jsonb？几乎总是 jsonb

PostgreSQL 有两个 JSON 类型，别选错：

  • json：存原始文本。原样保留空格、键的顺序、重复的键。每次查询都要重新解析文本。几乎只在「需要一字不差保留原始 JSON」时才用。
  • jsonb：存解析后的二进制。键去重、重排，查询时不用再解析，能建索引，操作符更全。日常一律用 jsonb。

取值：-> 和 ->> 的一字之差

从 jsonb 里取值，最核心、也最容易记混的是这两个操作符：

  • ->：取出来还是 jsonb。可以接着往下钻。
  • ->>：取出来是 text。到头了，能直接比大小、拼字符串、和普通列比较。
-- data = {"title":"Left Hand","author":{"name":"Ursula","country":"US"}}

data -> 'author'                 -- {"name":"Ursula","country":"US"}  ← 还是 jsonb
data -> 'author' ->> 'name'      -- Ursula                            ← text
data ->> 'title'                 -- Left Hand                         ← text
data #>> '{author,country}'      -- US   ← #>> 按路径一步到位，返回 text
◆ 铁律：想继续钻用 ->，想拿最终值用 ->>

记法很简单：箭头越长（->>），越「到底」——它把结果拽出 JSON 世界，变成普通 text。多写的那个 >，就是「最后一步，给我个能用的文本」。中间要一层层往下走，用短箭头 ->。WHERE data ->> 'title' = 'Left Hand' 之所以用 ->>，就是因为要和文本 'Left Hand' 比较。

▶ 动手 · JSONB 路径引擎

下面这台真引擎让你在一个 jsonb 文档上试各种路径表达式。点预设，或自己改。特别注意每个结果下方标的返回类型——同一个字段，-> 给你 jsonb、->> 给你 text。

@> 包含，和让它变快的 GIN 索引

查 JSONB 最有力的操作符是 @>（包含）：判断左边的文档是否包含右边这个片段。

-- 找出 tags 里含 "scifi" 的文档
SELECT * FROM docs WHERE data @> '{"tags": ["scifi"]}';

-- 找出 author.country 是 US 的
SELECT * FROM docs WHERE data @> '{"author": {"country": "US"}}';

关键是：@>、?（键是否存在）这些操作符，能走 GIN 索引——一种专为「一列里有很多可搜索的小项」设计的索引（卷 V 会讲）：

CREATE INDEX idx_docs_data ON docs USING gin (data);
-- 建完，上面的 data @> '...' 查询就能走索引，不用全表扫

这是 jsonb 相对「把 JSON 塞进一个大文本字段」的决定性优势：它不是一团不可搜索的字符串，而是能被索引、能高效查询的结构化数据。

改：jsonb 不能原地改一个键，但有函数

更新 jsonb 里的某个键，用 jsonb_set；合并用 ||；删键用 -：

UPDATE docs SET data = jsonb_set(data, '{author,country}', '"UK"');  -- 改一个路径
UPDATE docs SET data = data || '{"reprinted": true}';               -- 合并（加/覆盖键）
UPDATE docs SET data = data - 'reprinted';                          -- 删一个键

注意这些操作是生成一个新的 jsonb 整体、整列覆盖写回——回到卷 IV 的 MVCC，这意味着一次完整的行更新（旧版本作废、写新版本）。频繁小改一个大 jsonb，成本不低，这也是「别把什么都塞进一个巨型 jsonb」的理由之一。

⚠ 什么时候别用 JSONB

jsonb 好用到容易让人把整张表变成「一个 id + 一个 data 大 jsonb」。别这样。判断标准还是那个：这个字段需要被当真地约束、被高效地查询、被关联吗？

  • 该用列：核心的、稳定的、要参与 JOIN / 约束 / 频繁过滤的字段——价格、状态、外键。它们是关系的骨架，用真正的列，享受类型和约束的「当真」。
  • 该用 jsonb：稀疏的、多变的、因商品 / 因用户而异的属性——第三方回传的原始数据、每个商品不同的规格参数、灵活的配置。这些用列会长出几百个大多为空的字段，用 jsonb 正合适。

一句话：骨架用列，血肉用 jsonb。把该当真的字段藏进 jsonb，等于主动放弃了类型和约束的保护——那可就走回「不当真」的老路了。

⇄ 对照 MySQL
  • MySQL 也有 JSON 类型（5.7+），能存、能用路径取值（JSON_EXTRACT / -> / ->>），语义上和 PostgreSQL 的 jsonb 接近——这一处不是「有无之别」。
  • 但索引方式不同：MySQL 索引 JSON 内部字段，主要靠「生成列 + 普通索引」，或 8.0.17 起的「多值索引」（multi-valued index）。PostgreSQL 的 jsonb + GIN 能直接索引 @> 包含查询和键存在查询，更通用、更成熟。
  • 操作符更全：PostgreSQL 有 @> / ? / ?| / ?& / jsonb_path_query（SQL/JSON 路径语言）等一整套，表达力比 MySQL 的 JSON 函数更强。
  • 取值语法：-> / ->> 两家都有且语义相近，这个直觉可以直接搬过来。

这一章的一句话

jsonb 让关系表能装下半结构化数据，-> 继续钻、->> 拿文本、@> 判包含还能走 GIN 索引；但骨架字段用列、血肉字段才用 jsonb——别把该当真的东西藏进 JSON。

卷 III 到此收尾：类型和约束联手，保证了「每个值、每条规矩」都名副其实。接下来的卷 IV 转向一个全新维度——时间与并发：当很多人同时读写同一份数据，怎么保证谁都不被骗。这是 PostgreSQL 最反直觉、也最精彩的部分。