JSON 类型

PostgreSQL 提供 json 和 jsonb 两种 JSON 类型。两者都校验 JSON 语法，但 json 保留输入文本的空白和键顺序，jsonb 以分解后的二进制形式存储，支持更高效的查询和索引。业务查询通常优先选择 jsonb。

`json` 与 `jsonb`

特性	`json`	`jsonb`
存储	原始文本	分解后的二进制结构
保留空白和键顺序	是	否
重复键	输入可保留，处理时取最后值	不保留重复键
查询和索引	能力有限	支持运算符和 GIN
写入成本	通常较低	需要解析和转换

若必须逐字保留签名、格式化或键顺序，使用 json 或额外保存原文；否则 jsonb 更适合应用数据。

创建和访问

CREATE TABLE profiles (
  profile_id bigint PRIMARY KEY,
  attributes jsonb NOT NULL DEFAULT '{}'::jsonb
);

SELECT attributes->>'nickname' AS nickname,
       attributes->'preferences' AS preferences
FROM profiles;

-> 返回 JSON，->> 返回文本。路径不存在时通常得到 NULL，这与键存在但值为 JSON null 的情况需要业务区分。

路径、包含和修改

SELECT attributes #>> '{address,city}' FROM profiles;
SELECT attributes @> '{"role":"admin"}'::jsonb FROM profiles;

UPDATE profiles
SET attributes = jsonb_set(attributes, '{preferences,theme}', '"dark"')
WHERE profile_id = $1;

@> 是包含判断，数组和对象的语义需通过测试确认。jsonb_set、||、- 等操作符适合小规模修改；高频更新大型 JSON 文档会产生整行版本和 WAL，可能不如拆列或拆表。

索引

CREATE INDEX profiles_attributes_gin_idx
  ON profiles USING gin (attributes);

CREATE INDEX profiles_role_idx
  ON profiles ((attributes->>'role'));

通用 GIN 适合 @> 等包含查询；jsonb_path_ops 索引更紧凑但支持的运算符集合较少。对固定路径的等值、排序或范围过滤，表达式 B-tree 往往更合适。

约束与数据建模

JSON 类型只保证整体是合法 JSON，不会自动保证字段存在、类型正确或值在枚举集合内。可用 CHECK 做轻量约束：

ALTER TABLE profiles
ADD CONSTRAINT profiles_role_check
CHECK (attributes->>'role' IN ('user', 'admin'));

稳定、经常关联、需要外键或聚合的字段应使用普通列；变化频繁、结构不稳定且主要按文档读取的字段才适合放入 JSONB。混合模型通常比“全部 JSON”更容易维护。

数字、日期和 `NULL`

JSON 数字进入数据库后仍可能遇到精度和范围问题；金额不要只依赖 JSON 数字，必要时抽取到 numeric。JSON null、SQL NULL 和缺失键不是一回事，接口契约应明确三者的区别。

常见误区

把 json 和 jsonb 当作完全等价，忽略键顺序和重复键差异。
只建通用 GIN 索引，却没有针对高频路径建立表达式索引。
把所有表字段塞进 JSON，导致外键、约束和统计困难。
用字符串拼接构造 JSON，造成转义错误或注入风险；应使用参数化对象。
以为 JSONB 查询会自动验证业务 schema。

json 与 jsonb​

创建和访问​

路径、包含和修改​

索引​

约束与数据建模​

数字、日期和 NULL​

常见误区​