PG16：SQL/JSON 构造与检查

PostgreSQL 从 9.x 时代起就是 JSON 支持最强大的关系数据库之一，但它的 JSON 函数族（json_build_object()、json_agg()、jsonb_set() 等）一直是 PostgreSQL 专属方言——功能够用，却不符合 SQL 标准（SQL/JSON，ISO/IEC 9075-16），跨数据库迁移与工具链兼容都有摩擦。PostgreSQL 16 迈出了补齐标准的第一步：引入了 SQL 标准语法的 JSON 构造函数 JSON_ARRAY() / JSON_OBJECT()、JSON 聚合函数 JSON_ARRAYAGG() / JSON_OBJECTAGG()，以及 IS JSON 谓词——它能在查询里直接判断一个值「是否是合法 JSON、是标量还是数组还是对象、键是否唯一」。这三组能力全部新增于 PG16。

需要预先澄清一个常见误解：很多资料把 JSON()、JSON_SCALAR()、JSON_SERIALIZE() 与 JSON_TABLE() 也算作 PG16 的新特性，这是不对的——按 PostgreSQL 官方 release notes，这三个构造函数（与 JSON_TABLE()、JSON_EXISTS()、JSON_VALUE()、JSON_QUERY()）都属于 PostgreSQL 17。本页严格只写 PG16 确凿引入的特性，并在文末给出 PG17 展望的边界说明。

这些标准构造函数的价值不在「能做新事情」——旧的 PostgreSQL 函数几乎都能做到同样的事——而在三件事：标准语法可移植（Oracle、MySQL、SQLite 2.0+ 都支持同构语法）、ON NULL / UNIQUE KEYS 等语义选项首次可用、以及为后续版本的 JSON_TABLE 等标准设施铺路。本页会逐一讲解语法、与旧函数的对照和迁移建议。

前置知识

建议先阅读：时序数据模式

基础概念

SQL/JSON 标准的三块拼图

SQL/JSON 标准把 JSON 能力划分为几块，PG16 补上的是其中「构造」与「检查」两块：

能力块	作用	PG16 引入	说明
构造函数	从 SQL 值构造 JSON	`JSON_ARRAY()`、`JSON_OBJECT()`	标准语法，带 `RETURNING` / `ON NULL` / `UNIQUE KEYS` 选项
聚合函数	多行聚合为 JSON	`JSON_ARRAYAGG()`、`JSON_OBJECTAGG()`	标准语法的 `json_agg` / `json_object_agg`
检查谓词	校验 JSON 合法性	`IS JSON`	判断类型（标量/数组/对象）与键唯一性
查询函数	在 JSON 里做路径查询	（PG17）`JSON_EXISTS()` / `JSON_VALUE()` / `JSON_QUERY()`	PG16 仍用 `jsonb_path_query` 等
表转换	JSON 转关系表	（PG17）`JSON_TABLE()`	PG16 无法用

新旧两套函数的定位

PG16 引入的标准函数与既有的 PostgreSQL 专属函数并存且互补：

PG16 新增（标准）	既有（PostgreSQL 专属）	关系
`JSON_ARRAY(...)`	`json_build_array()` / `to_json(array)`	语法升级，语义选项更丰富
`JSON_OBJECT(...)`	`json_build_object()` / `jsonb_build_object()`	语法升级，支持键唯一性检查
`JSON_ARRAYAGG(...)`	`json_agg()` / `jsonb_agg()`	聚合的标准写法
`JSON_OBJECTAGG(...)`	`json_object_agg()` / `jsonb_object_agg()`	聚合的标准写法
`IS JSON` 谓词	无直接对应（需 `pg_input_is_valid` 或异常捕获）	全新能力

既有函数不会被废弃，存量代码无需立即迁移；新代码在「需要可移植性或语义选项」时优先标准写法即可。

json 与 jsonb 的关系不变

标准构造函数默认返回 json 类型（文本形式），通过 RETURNING 子句可以指定返回 jsonb、bytea 或文本类型。json 与 jsonb 的存储与操作差异见JSONB 构造——一句话回顾：json 保留输入的精确文本，jsonb 解析后以二进制存储、支持索引与包含运算。标准构造函数的 RETURNING jsonb 是把「标准语法」与「PG 强类型存储」结合起来的推荐姿势。

标准函数的能力边界

PG16 的四件套只负责「构造」与「校验」，与既有 JSON 生态的分工要摆正：

需求	用什么	是否 PG16 新增
构造 JSON 文档	`JSON_OBJECT` / `JSON_ARRAY`	是
多行聚合	`JSON_ARRAYAGG` / `JSON_OBJECTAGG`	是
校验文本合法性	`IS JSON` 谓词	是
修改已有 JSON	`jsonb_set`、`
路径查询	`jsonb_path_query` / jsonpath	否（既有）
JSON 转关系行	`jsonb_to_recordset` 等	否（`JSON_TABLE` 属 PG17）
按行生成接口文档	`json_object(...)` 逐行构造	是
高频解析大文档	jsonb 存储 + GIN，避免重复解析	否

一句话：新建文档用标准函数，查询与修改 JSON 继续用 PG 生态。两者拼在一起才是完整的 JSON 工作流——标准函数解决「SQL 标准的可移植书写」，jsonb 设施解决「高性能存储与索引」。

语法与用法

JSON_ARRAY：从一组值构造 JSON 数组

sql

-- 标准语法：JSON_ARRAY( 值 [, 值 ...] [ ON NULL 子句 ] [ RETURNING 类型 ] )
SELECT json_array(1, true, null, 'abc');
 json_array
--------------
 [1, true, null, "abc"]

-- NULL ON NULL（默认）：null 保留在数组中
SELECT json_array(1, null, 2 NULL ON NULL);
 [1, null, 2]

-- ABSENT ON NULL：null 项被跳过
SELECT json_array(1, null, 2 ABSENT ON NULL);
 [1, 2]

-- RETURNING jsonb：直接产出 jsonb
SELECT json_array(1, 2, 3 RETURNING jsonb);
 [1, 2, 3]

JSON_ARRAY 还有一个子查询形态——参数不是一组值，而是一条返回单列的 SELECT：

sql

SELECT json_array(SELECT u.email FROM users u WHERE u.status = 'active');
 ["a@example.com", "b@example.com"]

-- 子查询形态下 NULL 永远被忽略（等价 ABSENT ON NULL）
SELECT json_array(SELECT u.nickname FROM users u);

子查询形态是多行收集进数组的「非聚合」写法，与 JSON_ARRAYAGG 的区别在下面聚合一节详述。

JSON_OBJECT：从键值对构造 JSON 对象

sql

-- 键与值的两种写法：KEY ... VALUE ... 或 键: 值
SELECT json_object('code' VALUE 'P123', 'title' : 'Jaws');
        json_object
-----------------------------
 {"code" : "P123", "title" : "Jaws"}

-- ON NULL 选项：值为 null 的键是否保留
SELECT json_object('a' : null, 'b' : 1 NULL ON NULL);    -- 默认，null 保留
 {"a" : null, "b" : 1}
SELECT json_object('a' : null, 'b' : 1 ABSENT ON NULL);
 {"b" : 1}

-- UNIQUE KEYS 选项：重复键报错（默认 WITHOUT UNIQUE KEYS，即不检查）
SELECT json_object('a' : 1, 'a' : 2 WITHOUT UNIQUE KEYS);
 {"a" : 1, "a" : 2}
SELECT json_object('a' : 1, 'a' : 2 WITH UNIQUE KEYS);
ERROR:  duplicate JSON object key "a"

-- RETURNING jsonb
SELECT json_object('id' : 42, 'name' : 'alice' RETURNING jsonb);
 {"id": 42, "name": "alice"}

键表达式必须能转为 text 且不能为 NULL；WITH UNIQUE KEYS 在「程序拼接键、键可能重复」的防御性场景里非常有用。

JSON_ARRAYAGG / JSON_OBJECTAGG：聚合为 JSON

sql

-- JSON_ARRAYAGG：多行的某一列（或多个表达式）聚合成数组
SELECT json_arrayagg(u.email ORDER BY u.id)
FROM users u WHERE u.status = 'active';
 ["a@example.com", "b@example.com"]

-- JSON_OBJECTAGG：两列聚合为「键: 值」对象
SELECT json_objectagg(u.id VALUE u.email)
FROM users u;
 {"1" : "a@example.com", "2" : "b@example.com"}

-- 同样支持 ON NULL / UNIQUE KEYS / RETURNING
SELECT json_objectagg(u.id VALUE u.email ABSENT ON NULL RETURNING jsonb)
FROM users u;

JSON_ARRAYAGG 支持聚合函数通用的 ORDER BY、DISTINCT、FILTER (WHERE ...) 子句，用法与 json_agg 一致（聚合的通用语义见聚合函数基础）。

IS JSON：校验谓词

sql

-- 语法：表达式 IS [ NOT ] JSON [ VALUE | SCALAR | ARRAY | OBJECT ] [ WITH | WITHOUT UNIQUE KEYS ]
SELECT 'null'::text IS JSON;                          -- t  （JSON null 是合法标量）
 t
SELECT '[1, 2, 3]'::text IS JSON;
 t
SELECT '[1, 2, 3]'::text IS JSON ARRAY;               -- 是数组？
 t
SELECT '{"a": 1}'::text IS JSON OBJECT;               -- 是对象？
 t
SELECT '42'::text IS JSON SCALAR;                     -- 是标量？
 t
SELECT '{"a": 1, "a": 2}'::text IS JSON OBJECT WITHOUT UNIQUE KEYS;   -- 允许重复键
 t
SELECT '{"a": 1, "a": 2}'::text IS JSON OBJECT WITH UNIQUE KEYS;      -- 要求键唯一
 f

完整的行为矩阵见「详细说明」一节。IS JSON 的输入是文本类型（text/varchar/bytea/json/jsonb 均可），这正是它的价值：校验「一段文本是不是合法 JSON」以前需要自定义异常捕获，现在是一个普通谓词，可以直接用在 CHECK 约束、WHERE 过滤与 CASE 分支里。

详细说明

JSON_ARRAY 的完整语法与求值规则

PG16 文档中 json_array 的签名：

text

json_array ( [ { value_expression [ FORMAT JSON ] } [, ...] ]
             [ { NULL | ABSENT } ON NULL ]
             [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ] )

json_array ( [ query_expression ]
             [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ] )

要点：

FORMAT JSON：声明表达式已经是 JSON（例如本身就是 json 列或 JSON 文本），不再二次编码。没有它，一个 '{"a":1}'::text 会被编码成 JSON 字符串（"{\"a\":1}"）而不是对象。
NULL ON NULL（默认）：SQL NULL 编码为 JSON null；ABSENT ON NULL：跳过。注意子查询形态下永远等价 ABSENT ON NULL——因为「SELECT 出一行 NULL」无法与「没有这一行」区分。
RETURNING：默认 json；可以是 jsonb、bytea（FORMAT JSON ENCODING UTF8 时）或文本类型。RETURNING jsonb 是写入 jsonb 列场景的推荐搭配。

与旧函数 json_build_array() 的一个微妙差别：json_build_array() 对 NULL 永远保留（无 ON NULL 选项），而 JSON_ARRAY 的默认行为也是保留（NULL ON NULL）——两者默认一致，但只有标准函数能「跳过 NULL」。

JSON_OBJECT 的键与值规则

text

json_object ( [ { key_expression { VALUE | ':' } value_expression
                  [ FORMAT JSON [ ENCODING UTF8 ] ] } [, ...] ]
              [ { NULL | ABSENT } ON NULL ]
              [ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
              [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ] )

键表达式会被转换为 text；不能是 NULL，也不能是带有到 json 类型转换的类型（例如直接拿 json 列当键会报错）——这条规则避免了键的歧义。
WITH UNIQUE KEYS：出现重复键直接报错。默认 WITHOUT UNIQUE KEYS（后键覆盖前键，与旧函数 json_build_object 行为一致）。
值为 SQL NULL 时的去留由 ON NULL 控制，与数组版一致。
旧函数 json_object(text[])（键值对平铺数组）继续可用，与标准版同名不同签名，PostgreSQL 按参数类型分派——注意标准版的第一个参数是表达式而不是数组，两者不会混淆。

JSON_ARRAYAGG / JSON_OBJECTAGG 的聚合语义

text

json_arrayagg ( [ { value_expression [ FORMAT JSON ] } [, ...] ]
                [ { NULL | ABSENT } ON NULL ]
                [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ] )

json_objectagg ( [ { key_expression { VALUE | ':' } value_expression
                     [ FORMAT JSON [ ENCODING UTF8 ] ] } [, ...] ]
                 [ { NULL | ABSENT } ON NULL ]
                 [ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
                 [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ] )

它们是真正的聚合函数：输入多行、输出一行 JSON。聚合函数的通用设施全部可用：

sql

-- ORDER BY 控制数组元素顺序（聚合内排序，不影响分组）
SELECT json_arrayagg(e.level ORDER BY e.occurred_at) FROM events e GROUP BY e.user_id;

-- FILTER 分条件聚合
SELECT json_arrayagg(e.name FILTER (WHERE e.severity = 'error')) FROM events e;

-- DISTINCT 去重
SELECT json_arrayagg(DISTINCT t.tag) FROM tags t;

与「子查询形态的 JSON_ARRAY」的对比：json_array(SELECT ...) 是标量子查询（每行外层结果触发一次子查询），json_arrayagg(...) 是分组聚合（每个分组一个数组）。两者在简单场景结果相同，但聚合形态在 GROUP BY、窗口函数与优化器层面行为更规范，多行收集一律优先 JSON_ARRAYAGG。

IS JSON 的行为矩阵

IS JSON 谓词的判定规则：

输入文本	`IS JSON`	`IS JSON SCALAR`	`IS JSON ARRAY`	`IS JSON OBJECT`
`42` / `"str"` / `true` / `null`	t	t	f	f
`[1, 2]`	t	f	t	f
`{"a": 1}`	t	f	f	t
`1, 2`（两段）	f	f	f	f
`{'a': 1}`（单引号键）	f	f	f	f
空字符串 / `NULL` 输入	NULL	NULL	NULL	NULL

补充规则：

输入为 SQL NULL 时结果是 NULL（谓词三值逻辑），IS NOT JSON 同理——WHERE NOT (payload IS JSON) 与 WHERE payload IS NOT JSON 的空值行为不同，过滤脏数据时用后者。
null、42、"str"、true 是合法 JSON（标量），IS JSON 为真；而 SQL 的 NULL 输入值是第三态。
WITH UNIQUE KEYS / WITHOUT UNIQUE KEYS：递归检查输入中所有层级的所有对象是否有重复键。默认 WITHOUT（不检查，重复键也认为合法）。
输入可以是 bytea（按 UTF-8 解析）。
IS NOT JSON 直接取反：脏数据清洗的惯用过滤是 DELETE FROM staging WHERE raw IS NOT JSON。

与旧函数逐项对照

用同一个需求「把订单及其明细变成 JSON」对照新旧写法：

sql

-- 旧：PostgreSQL 专属函数
SELECT o.id,
       json_build_object(
           'id', o.id,
           'user_id', o.user_id,
           'items', (SELECT json_agg(json_build_object('sku', i.sku, 'qty', i.qty))
                     FROM order_items i WHERE i.order_id = o.id)
       ) AS doc
FROM orders o;

-- 新：PG16 标准构造 + 标准聚合
SELECT o.id,
       json_object(
           'id' VALUE o.id,
           'user_id' VALUE o.user_id,
           'items' VALUE (SELECT json_arrayagg(json_object('sku' VALUE i.sku, 'qty' VALUE i.qty))
                          FROM order_items i WHERE i.order_id = o.id)
       RETURNING jsonb) AS doc
FROM orders o;

两者性能在同一量级（标准函数在内部复用同一套 JSON 生成代码）；可移植性、ON NULL / UNIQUE KEYS 语义选项与 RETURNING jsonb 的便利是新版的主要收益。更多构造函数背景见JSONB 构造，聚合的通用语义见集合聚合函数。

迁移清单：旧函数 → 标准函数

按「机械替换 → 行为差异」分层迁移，避免一刀切：

旧写法	标准写法	注意
`json_build_object('a', 1, 'b', 2)`	`json_object('a' : 1, 'b' : 2)`	键规则收紧（不能 NULL、不能带 json 转换的类型）
`json_build_array(1, 2)`	`json_array(1, 2)`	新增 ON NULL 选项
`json_agg(t.*)`	逐列展开的 `json_arrayagg(...)`	`t.*` 整行形态标准函数不支持
`jsonb_agg(...)`	`json_arrayagg(... RETURNING jsonb)`	显式 RETURNING jsonb
`json_object_agg(k, v)`	`json_objectagg(k VALUE v)`	新增 UNIQUE KEYS
异常捕获校验 JSON	`IS JSON` 谓词	可直接进 CHECK 约束

json_agg(t.*) 这类「整行转 JSON」的标准替代不存在——逐列展开是唯一路径，整行场景保留旧函数即可。迁移优先级建议：CHECK 约束校验（IS JSON）> 新代码用构造函数 > 存量构造函数顺手替换 > 整行聚合（不迁移）。

另注意 RETURNING text 的语义：返回的是「重新序列化后的文本」，与原始文本可能不同（空格、键序都会变）；做文本级 diff 前先 RETURNING jsonb 再取文本，避免「看起来变了」的假差异。

FORMAT JSON 与 ENCODING 的语义

FORMAT JSON [ ENCODING UTF8 ] 出现在两个位置：

输入侧：声明该表达式「已经是 JSON」，直接内联而不是作为 JSON 字符串编码。这是构造嵌套 JSON 时最常缺的一个开关：

sql

-- 缺 FORMAT JSON：meta 被当成字符串编码
SELECT json_object('meta' VALUE '{"tier": "gold"}');
 {"meta" : "{\"tier\": \"gold\"}"}

-- 加 FORMAT JSON：内联为对象
SELECT json_object('meta' VALUE '{"tier": "gold"}' FORMAT JSON);
 {"meta" : {"tier": "gold"}}

输出侧（RETURNING ... FORMAT JSON ENCODING UTF8）：指定返回 bytea 时的 UTF-8 编码；RETURNING bytea 不带 ENCODING 会报错。

三种输入类型在「加与不加」下的行为对照，值得做成一张卡片贴在手边：

输入类型	不加 `FORMAT JSON`	加 `FORMAT JSON`
数值 / 布尔标量	按 JSON 标量编码	同左（无附加效果）
文本	编码为 JSON 字符串（`"..."`）	按 JSON 原样内联（内容必须合法）
json / jsonb 类型	直接内联	同左（无附加效果）

记忆口诀：类型化的 JSON 自动内联，裸文本必须声明。上面示例里的 '{"tier": "gold"}' 是无类型文本所以要声明；若写成 json '{"tier": "gold"}' 则不声明也会内联。

SQL/JSON 路径的数值字面量增强（新增于 PG16）

PG16 同时放宽了 SQL/JSON 路径语言（jsonb_path_query 用的 jsonpath）里的数值字面量：支持十六进制、八进制、二进制整数与数字间的下划线分隔（例如 0x1F、1_000_000）。这对直接在路径里做位运算式过滤的场景是纯增益；jsonpath 的完整语法见JSONPath。

sql

SELECT jsonb_path_query_array('[15, 16, 255]', '$[*] ? (@ == 0x0F)');   -- 15
SELECT jsonb_path_query_array('[1000, 2000]', '$[*] ? (@ > 1_000)');    -- [2000]

JSON_ARRAY 子查询形态的执行语义

sql

SELECT o.id, json_array(SELECT i.sku FROM order_items i WHERE i.order_id = o.id)
FROM orders o;

标量子查询形态对外层每一行执行一次子查询，等于相关子查询的 N 次往返——订单数大时是典型的 N+1 形态。两种替代按序评估：

需要聚合语义时改 JSON_ARRAYAGG + GROUP BY（一次分组扫描替代 N 次子查询）；
子查询有索引支撑且外层行数可控时，维持原样并确认计划走 Index Only Scan。

EXPLAIN 里看到 SubPlan 且 loops 等于外层行数，就是该形态在逐行执行——这是判断要不要改写的一眼判据。

IS JSON 作为 CHECK 约束的细节

CHECK (raw IS JSON) 与应用层校验相比有四个行为细节值得知道：

约束不回溯既有行：给存量表加约束时 PostgreSQL 不校验已存在的数据，脏数据继续存在——先跑盘点查询（见示例五）再加约束；
NOT VALID / VALIDATE 两步走：ADD CONSTRAINT ... NOT VALID 立即约束新写入，低峰 VALIDATE CONSTRAINT 补验存量，避免长锁；
按消费方契约选级别：IS JSON 只保「合法」，IS JSON OBJECT WITH UNIQUE KEYS 保「对象且键唯一」；约束越严，写入方容错要求越高。
谓词与转换分离：IS JSON 判断「能否解析」，::jsonb 才是「真的转换」——校验通过再转换，避免转换异常打断批量任务。

sql

ALTER TABLE legacy_webhooks
  ADD CONSTRAINT raw_is_json CHECK (raw IS JSON) NOT VALID;
-- 低峰执行
ALTER TABLE legacy_webhooks VALIDATE CONSTRAINT raw_is_json;

与 JSONB 查询的组合工作流

标准函数负责「进」，既有 JSONB 设施负责「存与查」，一条完整链路：

sql

-- 1) 构造：PG16 标准函数生成事件文档
INSERT INTO events (uid, payload)
SELECT e.uid,
       json_object(
           'kind'   VALUE e.kind,
           'at'     VALUE e.occurred_at,
           'actor'  VALUE json_object('id' VALUE e.actor_id, 'name' VALUE e.actor_name),
           RETURNING jsonb
       )
FROM raw_events e;

-- 2) 存：jsonb 列 + GIN 索引（见 JSONB 索引页）
CREATE INDEX ON events USING gin (payload);

-- 3) 查：既有 jsonb 路径与包含运算
SELECT uid FROM events WHERE payload @? '$.actor.id ? (@ == 42)';

构造期就确定文档结构（键名、嵌套层级、NULL 语义），比「先存文本再补结构」省一次全量重写；RETURNING jsonb 让构造结果直接获得 jsonb 的去重排序与索引能力。嵌套的子文档（如 actor）是 json 类型的表达式，无需 FORMAT JSON 即被内联为 JSON 值——这是「json 类型输入隐式识别」规则的直接应用。

性能：JSON 字符串解析的向量化（新增于 PG16）

PG16 的 JSON 解析器在扫描字符串内容时改用 SIMD 向量运算定位转义与边界（官方 release notes：Allow JSON string parsing to use vector operations）。它对所有「文本 → json/jsonb」的解析路径生效，是无需任何改动的透明加速。业务上值得知道的结论是：大 JSON 文档的入库解析成本在 PG16 有可感知的下降，但解析仍是 O(文档长度) 的——真正的性能杠杆依然是「列式拆分 + 只对需要索引的部分保留 jsonb」。

实战示例

示例一：API 响应构造（订单 + 明细 + 客户）

sql

SELECT json_object(
    'order' VALUE json_object(
        'id'       VALUE o.id,
        'status'   VALUE o.status,
        'amount'   VALUE o.amount,
        'created'  VALUE o.created_at
    ),
    'customer' VALUE json_object(
        'id'    VALUE u.id,
        'email' VALUE u.email
    ),
    'items' VALUE (
        SELECT json_arrayagg(
                   json_object('sku' VALUE i.sku, 'qty' VALUE i.qty, 'price' VALUE i.price)
                   ORDER BY i.sku)
        FROM order_items i
        WHERE i.order_id = o.id
    )
    RETURNING jsonb
) AS doc
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.id = 1001;

嵌套的标准构造函数让文档结构与业务对象一一对应；RETURNING jsonb 后可以直接存入 jsonb 列（文档快照 / 事件溯源场景）。

示例二：IS JSON 做入库校验（CHECK 约束 + 清洗）

sql

-- 防线一：表级 CHECK 约束，非法 JSON 根本进不来
CREATE TABLE webhook_events (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source   text NOT NULL,
    raw      text NOT NULL CHECK (raw IS JSON),
    received timestamptz NOT NULL DEFAULT now()
);

-- 防线二：外部导入的暂存区允许脏数据，用谓词分流
CREATE TABLE webhook_staging (source text, raw text);

INSERT INTO webhook_staging VALUES
  ('gh',   '{"action": "opened"}'),
  ('gh',   '{"action": "opened"}'),        -- 重复键
  ('sv',   'not json at all'),
  ('sv',   '[1, 2, 3');

-- 清洗：合法（含键唯一）进事件表，非法进隔离区
INSERT INTO webhook_events (source, raw)
SELECT source, raw FROM webhook_staging WHERE raw IS JSON OBJECT WITH UNIQUE KEYS;

SELECT source, raw FROM webhook_staging WHERE raw IS NOT JSON;

旧实现需要在应用层 try-catch ::jsonb 转换，或写 pg_input_is_valid 的函数包装；IS JSON 让校验回到纯 SQL，且 WITH UNIQUE KEYS 提供了以前没有的键唯一性检查。

示例三：JSON_ARRAYAGG 生成配置快照

sql

-- 把每个角色的权限聚合成 JSON 数组，快照存入审计表
INSERT INTO permission_snapshots (role_id, snapshot, taken_at)
SELECT r.id,
       json_object(
           'role'   VALUE r.name,
           'perms'  VALUE json_arrayagg(p.code ORDER BY p.code) ABSENT ON NULL,
           'grants' VALUE json_objectagg(p.code VALUE p.granted WITH UNIQUE KEYS)
       ) RETURNING jsonb,
       now()
FROM roles r
JOIN role_permissions p ON p.role_id = r.id
GROUP BY r.id, r.name;

JSON_ARRAYAGG ... ORDER BY 保证数组顺序稳定（快照 diff 不产生假差异）；JSON_OBJECTAGG ... WITH UNIQUE KEYS 保证权限码重复时直接失败——这两个选项都是旧函数没有的。

示例四：键值配置表聚合为单个 JSON 对象

应用配置常以「一行一键」存储，下发到前端时聚合成单对象：

sql

CREATE TABLE app_settings (
    scope text NOT NULL,
    key   text NOT NULL,
    value jsonb NOT NULL,
    PRIMARY KEY (scope, key)
);

INSERT INTO app_settings VALUES
  ('checkout', 'timeout_ms', '3000'),
  ('checkout', 'currency',   '"CNY"'),
  ('search',   'engine',     '"fts"');

sql

-- 第一层：每个 scope 聚合为对象
WITH scoped AS (
    SELECT scope,
           json_objectagg(s.key VALUE s.value RETURNING jsonb) AS conf
    FROM app_settings s
    GROUP BY scope
)
-- 第二层：把各 scope 的对象再聚合成整体
SELECT json_objectagg(scope VALUE conf RETURNING jsonb) AS config
FROM scoped;

text

{"checkout" : {"timeout_ms": 3000, "currency": "CNY"}, "search" : {"engine": "fts"}}

两个要点藏在例子里：聚合函数不能嵌套，多层聚合必须用 CTE/子查询分步（这是 SQL 聚合的通用规则，不是新函数的限制）；RETURNING jsonb 写在每一个构造层上，外层聚合的输入已是 jsonb 时会作为 JSON 值内联，不需要再加 FORMAT JSON。

示例五：存量文本列的 JSON 化盘点

把历史遗留的文本列改造为 jsonb 之前，先盘点存量质量：

sql

SELECT
    count(*)                                                    AS total,
    count(*) FILTER (WHERE raw IS JSON)                         AS valid,
    count(*) FILTER (WHERE raw IS JSON OBJECT)                  AS valid_object,
    count(*) FILTER (WHERE raw IS JSON ARRAY)                   AS valid_array,
    count(*) FILTER (WHERE raw IS JSON OBJECT WITH UNIQUE KEYS) AS strict_ok,
    count(*) FILTER (WHERE raw IS NOT JSON)                     AS invalid
FROM legacy_webhooks;

text

 total | valid | valid_object | valid_array | strict_ok | invalid
-------+-------+--------------+-------------+-----------+---------
 84213 | 84100 |        82001 |        2099 |     81950 |     113

报告直接回答三个问题：能否直接 raw::jsonb（invalid 为 0 才行）、重复键有多少（valid_object 与 strict_ok 的差值）、对象/数组比例（决定目标列类型与索引策略）。清理 invalid 后执行 ALTER TABLE ... ALTER COLUMN raw TYPE jsonb USING raw::jsonb，把改造风险前移到 SQL 层；再加 IS JSON 的 CHECK 约束守住未来写入（细节见上文 CHECK 小节）。

常见报错速查

报错	触发写法	原因与修正
键不能为 NULL 类错误	`json_object(NULL VALUE 1)`	键不能为 NULL，用 `COALESCE` 兜底
键类型不能转 json 类错误	`json_object(json_col VALUE 1)`	键不能是可转 json 的类型，先 `::text`
duplicate key 错误	`WITH UNIQUE KEYS` 下出现重复键	去掉 UNIQUE KEYS 或清洗数据
`syntax error at or near "VALUE"`	PG15 及以下跑新语法	版本边界，升级或退回旧函数
聚合函数嵌套报错	`json_objectagg(... json_arrayagg(...))`	CTE 分层聚合
键重复静默覆盖	`WITHOUT UNIQUE KEYS`（默认）下重复键	需要唯一性就用 `WITH UNIQUE KEYS`
`RETURNING bytea` 报缺编码	`RETURNING bytea` 不带 ENCODING	补 `FORMAT JSON ENCODING UTF8`

前两条是「键规则收紧」的两种形态，也是旧代码迁移时最常撞上的——json_build_object 时代键几乎任何标量都能当，标准函数把「键必须是合法 JSON 字符串来源」写成了硬约束。把这张表贴进迁移工单，能省掉一轮轮试错。

注意事项

不要混淆 PG16 与 PG17 的 SQL/JSON 边界。 JSON()、JSON_SCALAR()、JSON_SERIALIZE()、JSON_TABLE()、JSON_EXISTS()、JSON_VALUE()、JSON_QUERY() 均为 PG17 引入；在 PG16 上使用会直接报语法错误。文档、教程与 ORM 生成代码里若出现这些函数，先确认目标版本。

RETURNING 的默认类型是 json 而不是 jsonb。 写入 jsonb 列时忘记 RETURNING jsonb 会触发一次隐式 json → jsonb 转换（每行一次解析），批量场景有可测的开销；显式 RETURNING jsonb 更好。

ABSENT ON NULL 会改变文档结构。 上游消费者如果依赖「键存在但值为 null」的语义，ABSENT ON NULL 会直接删键而不是置 null——API 契约敏感的场景要与消费方确认。

WITH UNIQUE KEYS 是运行时检查。 它在键重复时抛错而不是「取最后一个」，从旧代码迁移到 WITH UNIQUE KEYS 等于把「静默覆盖」变成「报错」，务必评审调用方对异常的容错。

IS JSON 的三值逻辑。 输入 NULL 时结果是 NULL 而不是 false；NOT (x IS JSON) 对 NULL 行同样返回 NULL，过滤时要显式处理空值（WHERE x IS NOT JSON 才会把 NULL 行排除在「合法」之外）。

标准聚合不支持窗口形态。 JSON_ARRAYAGG 是普通聚合函数，没有 OVER 形态的官方语义保证；需要「窗口内收集 JSON」时仍用 json_agg() OVER (...)。

json 输出列宽与客户端。 标准函数默认返回 json 文本，psql 显示时会带缩进差异（jsonb 规范化后键排序、去空格）；以文本比对做回归测试时注意 json/jsonb 的显示差异，必要时统一 RETURNING jsonb 再比较。

聚合函数不能嵌套，多层聚合用 CTE 分步。 json_objectagg(k VALUE json_arrayagg(...)) 直接报错；按「内层 CTE 先聚合、外层再聚合」拆开写，RETURNING jsonb 每层都要声明。

回归测试用布尔比较而不是文本比较。 json/jsonb 的显示格式受客户端折行与空格影响，肉眼比对不可靠；统一 RETURNING jsonb 后用 payload = expected 的相等判断，才是可靠的断言方式。

WITH UNIQUE KEYS 只对「对象」有意义。 json_objectagg 的该选项检查的是对象键重复；数组没有键的概念，json_arrayagg 不提供这个选项——别在错误的函数上找它。

最佳实践

新代码优先标准构造函数，存量代码按需迁移。 JSON_OBJECT / JSON_ARRAY / JSON_ARRAYAGG / JSON_OBJECTAGG 在可读性与可移植性上全面优于 json_build_*；迁移以「顺路改造」为宜，不必专项重构。
写入 jsonb 列必带 RETURNING jsonb。 避免每行一次的 json→jsonb 隐式解析。
嵌套 JSON 文本记得 FORMAT JSON。 json/jsonb 类型的表达式会自动内联（如 json_array(1, json '{"a":null}') → [1, {"a":null}]）；但「文本形式的 JSON」想内联而不被二次编码为字符串，必须在表达式后加 FORMAT JSON。
API 文档构造用 JSON_OBJECT/JSON_ARRAYAGG 组合。 「父对象 + 子数组」的标准模板（见示例一）可以稳定生成嵌套文档，ORDER BY 放进聚合内保证输出确定性。
用户输入的 JSON 校验交给 IS JSON。 表级 CHECK (raw IS JSON) 是零成本防线；含重复键风险的数据用 WITH UNIQUE KEYS。
多行收集用 JSON_ARRAYAGG 而不是标量子查询版 JSON_ARRAY。 聚合形态在 GROUP BY、执行计划与语义上都更规范。
jsonpath 数值字面量用下划线提升可读性。 1_000_000 比 1000000 在长路径过滤里更不易错；十六进制用于位掩码比较。
把版本边界写进代码评审清单。 PG16 可用集（构造四件套 + IS JSON）与 PG17 可用集（JSON() / JSON_TABLE 等）分列成表，评审里一眼判别「这个函数目标版本有没有」。
大文档入库评估解析成本。 PG16 的向量化解析带来普遍加速，但超大 JSON 的行内存储依然昂贵——能拆列就拆列，jsonb 留给真正的半结构化部分。
以 PG17 的 JSON_TABLE 为迁移路标。 现在 JSON→行的转换还要靠 jsonb_to_recordset / json_array_elements；升级到 PG17 后可以逐步替换为标准 JSON_TABLE（届时参考 PG17 章节对应页面）。
监控 json/jsonb 的使用占比。 \dt+ 与 pg_total_relation_size 中 jsonb 列占比过高的表，优先评估列式拆分与 TOAST 策略，而不是只依赖解析层的性能改进。

继续学习：PG16：聚合与窗口函数增强

PG16：SQL/JSON 构造与检查 ​

基础概念 ​

SQL/JSON 标准的三块拼图 ​

新旧两套函数的定位 ​

json 与 jsonb 的关系不变 ​

标准函数的能力边界 ​

语法与用法 ​

JSON_ARRAY：从一组值构造 JSON 数组 ​

JSON_OBJECT：从键值对构造 JSON 对象 ​

JSON_ARRAYAGG / JSON_OBJECTAGG：聚合为 JSON ​

IS JSON：校验谓词 ​

详细说明 ​

JSON_ARRAY 的完整语法与求值规则 ​

JSON_OBJECT 的键与值规则 ​

JSON_ARRAYAGG / JSON_OBJECTAGG 的聚合语义 ​

IS JSON 的行为矩阵 ​

与旧函数逐项对照 ​

迁移清单：旧函数 → 标准函数 ​

FORMAT JSON 与 ENCODING 的语义 ​

SQL/JSON 路径的数值字面量增强（新增于 PG16） ​

JSON_ARRAY 子查询形态的执行语义 ​

IS JSON 作为 CHECK 约束的细节 ​

与 JSONB 查询的组合工作流 ​

PG16：SQL/JSON 构造与检查

基础概念

SQL/JSON 标准的三块拼图

新旧两套函数的定位

json 与 jsonb 的关系不变

标准函数的能力边界

语法与用法

JSON_ARRAY：从一组值构造 JSON 数组

JSON_OBJECT：从键值对构造 JSON 对象

JSON_ARRAYAGG / JSON_OBJECTAGG：聚合为 JSON

IS JSON：校验谓词

详细说明

JSON_ARRAY 的完整语法与求值规则

JSON_OBJECT 的键与值规则

JSON_ARRAYAGG / JSON_OBJECTAGG 的聚合语义

IS JSON 的行为矩阵

与旧函数逐项对照

迁移清单：旧函数 → 标准函数

FORMAT JSON 与 ENCODING 的语义

SQL/JSON 路径的数值字面量增强（新增于 PG16）

JSON_ARRAY 子查询形态的执行语义

IS JSON 作为 CHECK 约束的细节

与 JSONB 查询的组合工作流