结论：在sql server 2019中，openjson是解析复杂数组的唯一可靠方式；json_value仅能提取单个标量值，无法展开数组成行集，且不支持通配符路径，故不能用于遍历数组。
直接说结论：在 SQL Server 2019 中，OPENJSON 是解析复杂数组的唯一可靠方式；用 JSON_VALUE 或嵌套 JSON_QUERY 配合字符串拼接，基本走不通。
为什么不能只用 JSON_VALUE 解析数组元素
JSON_VALUE 只能返回单个标量值（比如 "name" 字段的字符串），它不生成行集，对数组本身无感知。你传一个 JSON 数组 '[{"id":1},{"id":2}]' 给 JSON_VALUE，指定路径 '$[0].id'，它最多只能取第一个元素——没法“展开”整个数组成多行。
常见错误现象：
– 写 SELECT JSON_VALUE(json_col, '$.items[0].name')，结果只有第一行有值，其余全为 NULL；
– 尝试用 JSON_VALUE(json_col, '$.items[*].name')，报错：路径语法不支持通配符（SQL Server 2019 不支持 ANSI SQL 路径表达式）。
-
JSON_VALUE适合提取“已知位置”的字段，不是用来遍历数组的 - 数组必须靠
OPENJSON转成行集，再配合CROSS APPLY层层下钻 - SQL Server 2019 不支持
JSON_CONTAINS或JSON_ARRAY_LENGTH等新函数，别指望捷径
解析嵌套数组的标准写法：两层 OPENJSON + CROSS APPLY
典型场景：表里有个 nvarchar(max) 字段存着类似 {"orders":[{"id":101,"items":[{"sku":"A","qty":2},{"sku":"B","qty":1}]}]} 的结构。要查出每件商品的 sku 和 qty。
关键点在于路径定位和层级拆解：
- 第一层
OPENJSON(@json, '$.orders')把订单数组转成行，每行对应一个订单对象 - 第二层
CROSS APPLY OPENJSON(order.value, '$.items')对每个订单再展开其items数组 - 路径必须写全，比如
'$.items[0].sku'是错的；要用'$.sku'（因为第二层输入已是单个 item 对象）
示例：
DECLARE @json NVARCHAR(MAX) = N'{
"orders": [
{ "id": 101, "items": [{ "sku": "A", "qty": 2 }, { "sku": "B", "qty": 1 }] },
{ "id": 102, "items": [{ "sku": "C", "qty": 5 }] }
]
}';
<p>SELECT
orders.id,
items.sku,
items.qty
FROM OPENJSON(@json, '$.orders')
WITH (id INT '$.id') AS orders
CROSS APPLY OPENJSON(orders.[value], '$.items')
WITH (sku VARCHAR(10) '$.sku', qty INT '$.qty') AS items;</p>
WITH 子句里路径写错的三个高发坑
显式 WITH 是精准解析的核心，但路径稍偏就全错：
- 漏写根路径前缀：比如写
'sku'而不是'$.sku'，OPENJSON直接忽略该列，返回NULL - 数组路径混淆：若源是
[{"a":1},{"a":2}]，用OPENJSON(@json, '$')后，WITH中路径应为'$.a'；若误写'$[0].a'，所有行都取第一个元素，结果重复 - 类型不匹配不报错但值为空：比如 JSON 里
"age": "25"（字符串），WITH写age INT '$.age'，结果就是NULL；得先确认数据类型或改用varchar+ 后续转换
建议：先用默认模式跑一次 SELECT * FROM OPENJSON(@json, '$.path')，看原始 key/value 输出，再决定 WITH 怎么写。
性能与 NULL 处理的实际约束
SQL Server 2019 的 OPENJSON 是纯 T-SQL 实现，没有索引加速，大数据量时明显变慢：
- 单次解析超过 10MB JSON 文本，CPU 占用陡增；建议在应用层分批传入
- 路径不存在时默认返回
NULL，不会中断执行；但若依赖该字段做JOIN或WHERE，需提前IS NOT NULL过滤 - 数组里某元素缺失字段（如
{"sku":"A"}没有qty），对应列就是NULL，无法跳过该行——这是设计行为，不是 bug
最易被忽略的一点：OPENJSON 默认按 lax 模式解析（路径不存在就返 NULL），但 SQL Server 2019 不支持显式写 lax $.path 或 strict $.path；它只认 $.path，且行为固定为 lax —— 别试图加关键字，会语法错误。
