如何在SQL中用CROSS APPLY处理JSON字段与表的关联？

SQL Server中需用CROSS APPLY配合OPENJSON()解析JSON数组，因OPENJSON()是依赖左侧行值的表值函数，不可用INNER JOIN替代；典型写法为SELECT ... FROM table CROSS APPLY OPENJSON(json_col) WITH (...)；注意NULL处理、路径写法、性能优化及跨库语法差异。

SQL Server里用CROSS APPLY解析JSON字段的常见写法

SQL Server 2016+ 支持原生 JSON，但OPENJSON()必须配合CROSS APPLY才能把 JSON 数组展开成行。直接在WHERE或SELECT里调用JSON_VALUE()只能取单值，没法“炸开”数组。

典型场景：一张订单表orders，其中details列存的是类似[{"item":"A","qty":2},{"item":"B","qty":1}]的 JSON 数组，你想把每项明细变成一行关联到主表。

正确写法是：

SELECT o.id, j.item, j.qtyFROM orders oCROSS APPLY OPENJSON(o.details)WITH (    item NVARCHAR(50) '$.item',    qty INT '$.qty') AS j;

注意：OPENJSON()返回的是表值函数，必须用CROSS APPLY；WITH子句定义列名和类型，路径用$开头表示从当前数组元素起始。

为什么不能用INNER JOIN替代CROSS APPLY？

INNER JOIN要求右侧是完整表或可独立执行的子查询，而OPENJSON()依赖左侧的o.details值——它不是静态数据源，每次调用都绑定当前行。用JOIN会报错：Invalid use of a side-effecting operator 'OPENJSON' within a function（如果封装成函数）或直接语法错误。

容易踩的坑：

• CROSS APPLY对NULL或无效 JSON 的行为：整行被过滤掉（相当于INNER JOIN语义），如果想保留主表空 JSON 的行，得改用OUTER APPLY
• OPENJSON()默认只解析顶层数组，如果 JSON 是对象（如{"items":[...]}），得先用JSON_QUERY()提取数组：CROSS APPLY OPENJSON(JSON_QUERY(o.data, '$.items'))
• 没写WITH子句时，OPENJSON()返回key、value、type三列，类型全是NVARCHAR(MAX)，后续转换易出错

CROSS APPLY + OPENJSON()的性能影响点

JSON 解析是 CPU 密集型操作，尤其当字段含大量嵌套或长文本时。执行计划里会出现Table-valued function算子，且无法走索引（除非你建了计算列+索引）。

实操建议：

• 避免在WHERE条件里对 JSON 字段做OPENJSON()筛选（比如WHERE j.item = 'X'），这会导致全表解析后再过滤；应尽量先用JSON_VALUE()做粗筛：WHERE JSON_VALUE(o.details, '$[0].item') = 'X'
• 如果频繁按某个 JSON 字段查询，建持久化计算列：ALTER TABLE orders ADD item_name AS JSON_VALUE(details, '$[0].item');，再给它加索引
• OPENJSON()不支持参数化路径（比如'$[' + @idx + '].item'），拼接字符串有注入风险，别这么干

PostgreSQL 或 MySQL 用户别硬套这个模式

这是 SQL Server 特有语法。CROSS APPLY在 PostgreSQL 中对应LATERAL，MySQL 8.0+ 用JSON_TABLE()，但行为和接口差异很大。

例如 PostgreSQL：

SELECT o.id, j.item, j.qtyFROM orders o,LATERAL json_to_recordset(o.details) AS j(item TEXT, qty INT);

而 MySQL 需要：

SELECT o.id, j.item, j.qtyFROM orders oJOIN JSON_TABLE(o.details, '$[*]'    COLUMNS (item VARCHAR(50) PATH '$.item', qty INT PATH '$.qty')) AS j;

跨数据库迁移时，CROSS APPLY + OPENJSON这整套逻辑几乎都要重写，别指望脚本通用。

JSON 字段关联真正麻烦的从来不是语法，而是 schema 变更后WITH子句漏改、空值处理不一致、还有那个永远没人测的超长 JSON 边界情况。