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  | t

GIN 索引已经创建。数据量较大时,包含查询、键存在查询等操作可能使用该索引;可通过 EXPLAIN 查看实际执行计划。

常用查询方式

使用 -> 可以返回 JSON 或 JSONB 值,使用 ->> 可以返回文本。需要继续访问嵌套对象时保留 JSONB 类型,最终用于比较或展示时再转为文本。

? 可以判断顶层键是否存在,@> 适合判断包含关系。查询条件应尽量固定,否则即使建立索引,也可能因为表达式不一致而无法命中。

适用边界

JSONB 适合保存可选配置、第三方回调原文、变化较快的扩展属性。经常参与关联、排序、唯一约束或金额计算的核心字段,应拆成普通列。

不要把整张业务表塞进一个 JSONB 字段。结构过度自由会削弱字段约束、数据质量和查询可维护性。稳定且重要的属性应使用明确类型,并通过 NOT NULL、CHECK 或外键保证规则。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

JSON 与 JSONB 类型

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通