怎样在SQL Server 2019中通过OPENJSON解析复杂数组-SQL-PHP中文网

结论：在SQL Server 2019中，OPENJSON是解析复杂数组的唯一可靠方式；JSON_VALUE仅能提取单个标量值，无法展开数组成行集，且不支持通配符路径，故不能用于遍历数组。

怎样在sql server 2019中通过openjson解析复杂数组

直接说结论：在 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 对象）

示例：

Expanso yaml-to-json

使用 Expanso Edge 管道将 YAML 输入转换为 JSON 格式。

下载

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 + 后续转换