16. JSON 与 JSONB 类型

有时候字段不固定，比如用户的标签、配置项、第三方回传的复杂报文。硬拆成多列很别扭，加一列又一列又跟不上变化。这时用 JSON 类型最合适——把整段半结构化数据塞进一个字段，又能按里面的键查询。

json 与 jsonb 的区别

PostgreSQL 提供两种：json 和 jsonb。

• json：原样存文本，每次查询都重新解析，保留空格和键的顺序。
• jsonb：以二进制格式存储，写入时解析一次，查询快，支持索引和丰富的包含运算。

SELECT '{"a":1,"b":2}'::json  = '{"b":2,"a":1}'::json;   -- false（顺序不同）
SELECT '{"a":1,"b":2}'::jsonb = '{"b":2,"a":1}'::jsonb;  -- true（jsonb 不关心顺序）

jsonb 还会去掉重复的键、忽略空白，所以它「相等判断更自然」。两者的输入写法完全一样，差别只在存储格式。

建表与插入

给 users 加一个 profile 字段存扩展信息：

CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(120),
  age SMALLINT,
  profile JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO users (username, profile)
VALUES ('alice', '{"city":"北京", "tags":["vip","new"], "age": 20}');

注意插入的 JSON 字符串必须符合 JSON 语法：键和字符串值都要用双引号，单引号不行。下面这种写法会报错，因为 JSON 要求键和字符串都必须用双引号：

INSERT INTO users (username, profile) VALUES ('bob', '{city:"上海"}');
-- ERROR: invalid input syntax for type json
-- DETAIL: Token "city" is invalid.

查询字段：-> 与 ->>

-> 取出来还是 json 对象（带引号）；->> 取出来是纯文本（不带引号）。这是最常用的一对操作符，务必分清。

SELECT username, profile -> 'city'  FROM users;  -- 结果带引号："北京"
SELECT username, profile ->> 'city' FROM users;  -- 结果纯文本：北京

->> 拿到的是文本，所以可以做条件过滤、字符串比较：

SELECT * FROM users WHERE profile ->> 'city' = '北京';

数组元素用数字下标（从 0 开始）：

SELECT username, profile -> 'tags' -> 0 FROM users;  -- 取第一个标签
SELECT username, profile -> 'tags' ->> 0 FROM users; -- 第一个标签的文本

嵌套对象继续用 -> 往下取：

SELECT profile -> 'address' ->> 'city' FROM users;

还有 #> 用路径数组取深层字段，#>> 取文本：

SELECT profile #> '{address, city}' FROM users;     -- jsonb 对象
SELECT profile #>> '{address, city}' FROM users;    -- 纯文本

包含判断（@>）

jsonb 支持「包含」运算符 @>，判断左边是否包含右边的结构。这是 jsonb 的杀手锏。

SELECT * FROM users WHERE profile @> '{"city":"北京"}';

意思是「profile 里存在 city 等于北京这一项」。右边可以写任意子结构，不要求完全相等。查某个标签是否存在：

SELECT * FROM users WHERE profile @> '{"tags":["vip"]}';

键存在性用 ?，判断某个顶层键在不在：

SELECT * FROM users WHERE profile ? 'tags';

还有 ?|（任一键存在）、?&（全部键存在）、<@（被包含）等运算符，按需要取用。

修改 jsonb

用 jsonb_set 改某个键的值：

UPDATE users
SET profile = jsonb_set(profile, '{city}', '"上海"')
WHERE username = 'alice';

jsonb_set 的第三个参数必须是合法 jsonb 值，字符串要带引号。要新增不存在的键，第四个参数传 true：

UPDATE users
SET profile = jsonb_set(profile, '{phone}', '"13800000000"', true)
WHERE username = 'alice';

|| 运算符合并、新增键值对（左边优先，键冲突时保留左边）：

UPDATE users
SET profile = profile || '{"vip": true}'
WHERE username = 'alice';

删键用 - 操作符，删数组元素用 - 加下标：

UPDATE users SET profile = profile - 'phone' WHERE username = 'alice';
UPDATE users SET profile = profile - '{tags, 0}' WHERE username = 'alice';  -- 删第一个标签

用 GIN 索引加速

jsonb 的一大优势是能建 GIN 索引，加速包含查询。没有索引时，@> 要逐行扫描，大数据量很慢。

CREATE INDEX idx_users_profile ON users USING GIN (profile);

之后 WHERE profile @> ... 这类查询就能用上索引，大表尤其明显。如果只按某个固定键查询，还可以建表达式索引更省空间、更快：

CREATE INDEX idx_users_city ON users USING btree ((profile ->> 'city'));

半结构化数据为什么需要它

关系表的列是固定的，但现实里常有「结构不固定」的数据：用户的个性化配置、第三方回传的报文、商品的动态属性。硬拆成几十列既难维护又大量为空。JSON 类型让「一整段灵活结构」住进一个字段，同时 PostgreSQL 还能按里面的键做查询、建索引。

不过要清醒：jsonb 不是用来替代关系建模的。如果某个字段你经常要拿来做关联、做聚合统计，那它更该是独立列。jsonb 适合「整体读写、偶尔按个别键过滤」的扩展信息。想清楚这一点，才不会把表搞成一团乱麻。

常用 jsonb 函数

除了操作符，PostgreSQL 还提供一批 jsonb 函数。比如 jsonb_pretty 把内容格式化得好看，jsonb_object_keys 列出顶层所有键：

SELECT jsonb_pretty(profile) FROM users WHERE username='alice';
SELECT jsonb_object_keys(profile) FROM users WHERE username='alice';

判断某个键是否存在还有函数版 jsonb_exists：

SELECT * FROM users WHERE jsonb_exists(profile, 'city');

json 与 jsonb 怎么选的再提醒

再强调一次取舍：json 保留原始文本（键顺序、空格），每次查询重新解析，适合「原样存档、很少按内部字段查」的场景；jsonb 写入即解析、查询快、能建 GIN 索引、操作符丰富，适合「要按内部字段搜索、过滤、聚合」的场景。日常 99% 情况用 jsonb。

索引怎么选

jsonb 上两种索引最常用：GIN 适合「包含查询」@>，能覆盖任意结构；btree 表达式索引 ((profile ->> 'city')) 适合「只按某个固定键等值或范围查」，更小更快。

CREATE INDEX idx_profile_gin ON users USING GIN (profile);
CREATE INDEX idx_city_btree ON users ((profile ->> 'city'));

两张索引不冲突，可以共存，让不同查询各取所需。

什么时候不该用 JSON

jsonb 虽好，但不是万能的。如果数据结构固定、需要频繁按字段做关联或聚合，老老实实拆成独立列性能更好、约束更强。jsonb 适合「结构灵活、查询简单」的扩展字段，别拿它替代正常的关系建模。