PostgreSQL / 系统课时
JSON 与 JSONB 类型
JSON 与 JSONB 类型
当字段结构会随业务变化,但又不值得立即拆成多张表时,可以使用 JSON 类型保存扩展属性。本课将比较 json 与 jsonb 的差异,并完成 JSONB 字段查询和索引示例。
学习目标
- 区分
json与jsonb的存储和查询特点 - 使用 JSONB 运算符读取与筛选数据
- 判断扩展字段何时适合使用 JSONB
json 与 jsonb 的本质差异
json 保存输入文本,能够保留空格、键顺序和重复键。每次查询时,数据库需要重新解析文本,更适合只保存、很少检索原始 JSON 的场景。
jsonb 会把内容转换为二进制结构,不保留原始排版,并在重复键出现时保留最后一个值。它支持更多运算符和 GIN 索引,通常是业务查询的默认选择。
查询 JSONB 字段
下面使用临时表保存商品扩展属性。->> 返回文本,@> 判断左侧 JSONB 是否包含右侧结构。
sql
CREATE TEMP TABLE product_profiles (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
attrs jsonb NOT NULL
);
INSERT INTO product_profiles (attrs)
VALUES (
'{"brand":"Acme","tags":["portable","usb-c"],"weight":1.2}'::jsonb
);
CREATE INDEX idx_product_profiles_attrs
ON product_profiles USING GIN (attrs);
SELECT
id,
attrs ->> 'brand' AS brand,
attrs @> '{"tags":["usb-c"]}'::jsonb AS supports_usb_c
FROM product_profiles;预期结果
text
id | brand | supports_usb_c
----+-------+----------------
1 | Acme | tGIN 索引已经创建。数据量较大时,包含查询、键存在查询等操作可能使用该索引;可通过 EXPLAIN 查看实际执行计划。
常用查询方式
使用 -> 可以返回 JSON 或 JSONB 值,使用 ->> 可以返回文本。需要继续访问嵌套对象时保留 JSONB 类型,最终用于比较或展示时再转为文本。
? 可以判断顶层键是否存在,@> 适合判断包含关系。查询条件应尽量固定,否则即使建立索引,也可能因为表达式不一致而无法命中。
适用边界
JSONB 适合保存可选配置、第三方回调原文、变化较快的扩展属性。经常参与关联、排序、唯一约束或金额计算的核心字段,应拆成普通列。
不要把整张业务表塞进一个 JSONB 字段。结构过度自由会削弱字段约束、数据质量和查询可维护性。稳定且重要的属性应使用明确类型,并通过 NOT NULL、CHECK 或外键保证规则。
按自己的节奏,确认真正理解。