PostgreSQL / 系统课时
设计 JSONB 文档结构
设计 JSONB 文档结构
JSONB 是否好查,往往在写第一条查询之前就已经决定了。本课从真实查询路径反推字段边界,设计对象、数组、标量和嵌套层级,并判断哪些字段应关系化、哪些动态属性适合进入 JSONB。
学习目标
- 根据过滤、排序、关联和扩展需求划分关系型列与 JSONB
- 正确选择对象、数组和标量,并保持键名与 JSON 类型稳定
- 控制嵌套深度,识别不应继续塞进 JSONB 的数据
从查询路径反推字段边界
设计 JSONB 时,不要先问“业务对象能不能序列化成 JSON”,而要先问“数据库以后会怎样查它”。频繁参与唯一约束、关联、排序、范围过滤或统计的字段,通常更适合普通列;稀疏、可扩展且不要求固定列集合的属性,才更适合 JSONB。
例如商品的 id、sku、created_at 结构稳定,并经常参与约束或排序,应保留为关系型列;颜色、材质、渠道参数等可能持续扩展的属性,可以集中到 attributes。下面的主示例同时演示对象、数组、标量和类型验证。
CREATE TABLE product_catalog (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku text NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now(),
attributes jsonb NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO product_catalog (sku, attributes)
VALUES (
'SKU-1001',
'{"color":"black","dimensions":{"width":30,"height":20},"tags":["travel","lightweight"]}'::jsonb
);
SELECT
sku,
attributes->>'color' AS color,
attributes#>>'{dimensions,width}' AS width,
attributes->'tags' AS tags,
jsonb_typeof(attributes->'color') AS color_type,
jsonb_typeof(attributes->'tags') AS tags_type,
jsonb_typeof(attributes->'dimensions') AS dimensions_type
FROM product_catalog
WHERE sku = 'SKU-1001';预期结果
CREATE TABLE
INSERT 0 1
sku | color | width | tags | color_type | tags_type | dimensions_type
----------+-------+-------+-------------------------------+------------+-----------+-----------------
SKU-1001 | black | 30 | ["travel", "lightweight"] | string | array | object
(1 row)->> 和 #>> 返回文本,适合直接读取叶子值;-> 保留 JSONB 类型,因此 tags 仍是数组。jsonb_typeof 用于核对实际 JSON 类型;如果类型不符合约定,应优先修正写入模型,而不是让每条查询都承担转换成本。

对象、数组和标量怎么选
对象适合表达“子键具有独立语义”的组合,例如 dimensions.width 与 dimensions.height。后续需要按子字段读取或过滤时,对象比把整段尺寸编码成字符串更清楚,也更容易建立针对路径的查询条件。
数组适合同一语义下的多值集合,例如标签。数组元素最好保持同一类型;如果每个元素都有独立身份和生命周期,还需要频繁更新、排序、约束或关联其他表,它通常已经不是简单属性集合,应拆成关系表。
标量应保存真实 JSON 类型。数值写成 JSON 数字,布尔值写成 true 或 false,不要为了方便统一保存成字符串,否则比较、排序和类型转换都会变复杂。
控制嵌套层级和字段语义
嵌套应该表达真实从属关系,而不是照搬应用对象。dimensions.width 很自然,因为宽度属于尺寸;但如果核心过滤条件经常要穿过五六层路径才能取得,文档通常已经过度嵌套,查询表达式、索引路径和局部更新都会更难维护。
同一业务含义还应保持稳定键名和稳定类型。不要让 customer_id 在部分记录中是数字、部分记录中是字符串,也不要同时出现 color、colour、productColor 三种同义键。JSONB 不会自动提供固定表结构那样的模式约束,字段约定需要由数据模型、写入校验或必要的数据库约束共同保证。
不要把整个业务对象塞进一列
只保留主键和巨大的 payload jsonb,再把状态、租户、时间、外键、动态属性和审计信息全部塞进去,是常见反模式。这样虽然写入灵活,却会让约束、关联、排序、统计和局部更新都依赖 JSON 路径,数据库也更难直接治理关键字段。
判断边界可以使用一个简单原则:稳定且数据库需要直接治理的字段关系化,稀疏且扩展速度快的字段文档化。主键、外键、唯一键、经常参与范围查询的时间和数值,以及频繁用于连接或排序的字段,通常应保留为普通列。
JSONB 更适合承担“整体结构有规律,但字段集合需要持续扩展”的部分,而不是替代整张关系模型。先围绕真实查询路径建立清晰文档边界,后续使用路径查询、包含操作符或 GIN 索引时,才更容易获得稳定且可治理的查询结构。