SQL/JSON

SQL/JSON 是 SQL:2016 引入的一个全新部件（Part 6: SQL/JSON），目标是在传统关系型数据库里用原生 SQL 语法“一站式”生成、查询、修改 JSON 文档，而不再依赖外部脚本或存储过程。Oracle 19c、PostgreSQL 12+、MySQL 8.0、SQL Server 2022 等主流引擎已陆续落地该标准。下面用约一千五百字的篇幅，从数据类型、路径语言、构造函数、查询函数、修改算子、索引策略、兼容性对照、性能陷阱与实战案例九个维度，为你拆解 SQL/JSON 的“来龙去脉”。

一、为什么需要 SQL/JSON
1. 微服务与前端驱动：REST/GraphQL 天然 JSON，后端不想再“行转列”。
2. 敏捷迭代：字段频繁增减，JSON 的半结构化降低 DDL 成本。
3. 分析下沉：把 JSON 解析放在数据库层，可复用索引、权限、事务，避免“拉数据→应用解析→回写”的往返延迟。
4. 标准化：过去各厂商的 JSON 函数命名迥异（->、JSON_VALUE、JSON_EXTRACT），SQL/JSON 提供统一关键字，降低迁移门槛。

二、数据类型与存储格式
标准只定义了“概念类型” JSON，并未强制物理格式；各引擎可自选：
• PostgreSQL 提供 json（纯文本）与 jsonb（二进制、去空格、去重复键、支持倒排索引）两种实现；jsonb 查询快但写入多一次解析。
• Oracle 用 BLOB 内部编码 UTF-8 存储，对外暴露 JSON 数据类型；同时保留 12c 就有的 VARCHAR2/CLOB 存放模式。
• MySQL 8.0 用内部二进制 DOM，支持部分原地更新；SQL Server 用 NVARCHAR 压缩存储，配合 JSON_COMPRESSION 选项。
选择原则：读多写少选二进制，写多读少选文本；需要跨版本备份导出则留一份文本。

三、路径语言（SQL/JSON Path）
SQL/JSON 的核心是“路径表达式”，语法类似 JavaScript 但增加 SQL 语义：
• 根对象：$
• 成员访问：$.name、$."last name"（带空格需双引号）
• 数组：$[*]、$[0]、$[last]、$[0 to 4]
• 过滤：$[*] ? (@.age > 18)、$ ? (exists(@.phone))
• 通配与递归：$**.price（找任意层级的 price）
路径返回三种序列：单值、数组、对象表；SQL/JSON 函数可指定 wrapper 行为（WITHOUT ARRAY WRAPPER、WITH CONDITIONAL/UNCONDITIONAL ARRAY）来控制结果集形状。

四、构造函数（JSON_OBJECT、JSON_ARRAY、JSON_OBJECTAGG、JSON_ARRAYAGG）
1. JSON_OBJECT([KEY] foo VALUE bar [, …])
支持 NULL ON NULL / ABSENT ON NULL 决定空值是否出现；支持 RETURNING 子句指定 VARCHAR2/JSON/BLOB。
2. JSON_ARRAYAGG(col ORDER BY … RETURNING JSON)
把分组列聚合成数组，再结合窗口函数可实现“行转 JSON 数组”而不依赖自连接。
3. PostgreSQL 扩展：json[b]_build_object、json[b]_agg，语义与标准兼容，但名字更短。

五、查询函数（JSON_VALUE、JSON_QUERY、JSON_TABLE）
1. JSON_VALUE(expr, path RETURNING datatype DEFAULT ‘’ ON ERROR)
提取标量，可映射到 DATE、NUMBER、BOOLEAN；出错策略 ERROR / NULL / DEFAULT。
2. JSON_QUERY(expr, path …)
返回 JSON 片段（对象或数组），用于嵌套子文档。
3. JSON_TABLE(expr, path COLUMNS (col type PATH ‘$.x’ …))
把 JSON“拆表”，实现行转列，可直接 JOIN、GROUP BY；是 SQL/JSON 的“杀手级”特性。
示例：
SELECT jt.*
FROM orders,
JSON_TABLE(doc, ‘$.items[*]’
COLUMNS (item_id INT PATH ‘$.id’, qty INT PATH ‘$.qty’, price NUMERIC PATH ‘$.price’)) AS jt;
一条语句即可把订单明细数组展开成关系行集，无需再跑 ETL。

六、修改算子（JSON_VALUE 的 ON EMPTY/ON ERROR、JSON_SERIALIZE、JSON_SCALAR）
标准还提供 JSON_MERGE_PATCH（RFC 7396 语义合并）、JSON_REMOVE、JSON_ARRAY_APPEND 等，但各引擎实现进度不一。
Oracle 21c 推出 JSON_TRANSFORM，支持 SET/REMOVE/APPEND/RENAME 一次语句完成多处更新，且支持 RETURNING 子句返回新旧映像，可用于审计触发器。
PostgreSQL 15 引入 jsonb subscripting，允许 UPDATE t SET doc[‘age’] = 30 WHERE id = 1，写法与 JS 一致，并记录 WAL 增量。

七、索引与执行计划
1. PostgreSQL GIN 索引：CREATE INDEX idx ON t USING gin ((doc->‘items’)); 对 @>、?、jsonb_path_ops 有加速。
2. Oracle 函数索引：CREATE INDEX idx ON t (JSON_VALUE(doc, ‘$.status’ RETURNING CHAR(1) ERROR ON ERROR));
3. MySQL 多值索引：ALTER TABLE t ADD INDEX mv ((CAST(doc->‘$.tags’ AS CHAR(30) ARRAY)));
4. SQL Server 持久化计算列 + BTREE。
经验：把高选择度、经常 WHERE/JOIN 的字段抽到函数索引，避免全文档扫描；写路径时尽量用唯一键前缀，减少 selectivity 估算失准。

八、兼容性对照速查
功能 Oracle 19c PostgreSQL 15 MySQL 8.0 SQL Server 2022
JSON_OBJECT √ √ √ √
JSON_TABLE √ √ √ √
JSON_VALUE √ √ √ √
JSON_QUERY √ √ √ √
JSON_TRANSFORM 21c 起 — — —
JSON_MERGE_PATCH 19c jsonb 内置 √ √
JSON_SCALAR 19c jsonb_build √ √
Path 过滤 √ √ 部分 部分
数组多值索引 — GIN 多值索引 计算列
二进制存储 BLOB jsonb 内部二进制 NVARCHAR
UTF-8 强制 √ √ √ √
注：打“—”表示需自行用函数组合实现。

九、性能陷阱与最佳实践
1. 路径表达式过深：$**.a.b.c 会退化为全表逐行递归解析，百万行即可把 CPU 打满；应把热点字段抽到虚拟列并索引。
2. 返回大数组：JSON_QUERY 返回 10 MB 数组时，网络包膨胀，可考虑 JSON_SERIALIZE 压缩或分页路径。
3. NULL 与空串：Oracle 里 JSON_VALUE 返回空串时与 NULL 混淆，需显式 DEFAULT 子句；PostgreSQL jsonb 严格区分 ‘null’::jsonb 与 NULL。
4. 触发器里写 JSON 更新：行级触发器对 doc 整体赋值会生成完整行 WAL，易打爆磁盘；推荐 JSON_TRANSFORM 或 jsonb subscripting 增量更新。
5. 版本升级：PostgreSQL 升级后 jsonb 内部格式可能变化，需 REINDEX GIN；Oracle 跨大版本需检查 JSON_VALUE 返回精度是否改变。

十、实战案例：电商订单宽表
背景：订单表 5000 万行，明细以 JSON 数组存于 doc 列，前端直接吐给网页。
步骤：
1. 建表：CREATE TABLE orders (id bigint PRIMARY KEY, doc jsonb, …);
2. 建索引：CREATE INDEX idx_gin ON orders USING gin ((doc->‘items’));
3. 查询热点：
SELECT JSON_VALUE(doc, ‘$.user_id’ RETURNING BIGINT) AS user_id,
SUM(JSON_VALUE(jt.total, ‘$’ RETURNING NUMERIC)) AS amount
FROM orders, JSON_TABLE(doc, ‘$.items[*]’ COLUMNS (total NUMERIC PATH ‘$.qty * $.price’)) AS jt
WHERE doc @> ‘{“status”:”paid”}’
GROUP BY user_id;
4. 执行计划：GIN 索引先过滤 status，JSON_TABLE 展开数组，HashAggregate 汇总，全程 1.2 s 返回。
5. 数据修改：UPDATE orders SET doc = JSONB_SET(doc, ‘{status}’, ‘”refunded”‘) WHERE id = 123; 仅生成 56 字节 WAL。

结语
SQL/JSON 把“关系”与“文档”缝合进同一查询空间，既保留了 ACID、索引、优化器、权限体系，又提供了前端友好的 JSON 语义。掌握路径语言、构造函数、查询与修改算子，再结合引擎特定的索引策略，你就能在 PostgreSQL、Oracle、MySQL、SQL Server 之间写出可移植、高性能、可维护的“混合范式”应用。随着 SQL:2023 增强 JSON_TRANSFORM、JSON_SERIALIZE、JSON_SORT 等新特性，未来数据库将真正成为“一份数据，多种模型”的统一平台，而 SQL/JSON 就是打开这扇大门的钥匙。