SQL Server中JSON_VALUE如何提取嵌套JSON内容

在使用 SQL Server 的 JSON_VALUE 函数时，很多人都会遇到一个“看似正常却总返回 NULL”的困惑——明明 JSON 结构看起来没问题，路径也写对了，为什么取不到值？其实，这背后藏着几个容易踩的坑，今天一次性说清楚。

JSON_VALUE 无法直接提取多层嵌套对象，必须用路径语法显式指定

JSON_VALUE 的职责很明确：只返回标量值（string、number、true/false/null），对象或数组一概不理。如果你写了 JSON_VALUE(json_col, '$.data')，而 $.data 恰好是个对象，结果不是报错，而是静悄悄地返回 NULL——这很容易让人误以为数据不存在。 要取嵌套字段，路径必须写全，比如 $.data.user.profile.name。路径区分大小写，且中间层级不能省略。举个例子：

• 错误写法：JSON_VALUE(json_col, '$.data')（假设 data 是对象）→ 返回 NULL
• 正确写法：JSON_VALUE(json_col, '$.data.user.name')（直达叶子节点）
• 如果字段名里带了空格或特殊字符，记得用双引号包裹：'$.["user info"].email'

处理 JSON 中的数组元素需配合 OPENJSON，JSON_VALUE 单独搞不定

JSON_VALUE 对数组索引的支持很有限：只能用 [0] 这类静态下标，而且前提是目标路径最终指向标量。比如 '$.items[0].price' 可行，但 '$.items[1]' 如果数组长度不足，函数会返回 NULL，没有报错，没有提示，全靠自己排查。 一旦需要遍历数组或动态取索引，就必须换用 OPENJSON + WITH 子句。例如从 $.orders 数组中提取每个订单的 id 和 status：

SELECT j.id, j.status
FROM your_table
CROSS APPLY OPENJSON(json_col, '$.orders')
WITH (
  id VARCHAR(20) '$.id',
  status VARCHAR(20) '$.status'
) AS j

NULL 值和缺失字段的行为很“安静”，建议加 ISJSON 验证再用 JSON_VALUE

JSON_VALUE 在输入非 JSON 字符串、路径不存在、或目标非标量时，一律返回 NULL。你没法单靠结果区分是“真为 null”还是“路径错了”。更隐蔽的是，如果列里存的是普通字符串（比如 '{a:1}'，缺引号），ISJSON() 返回 0，但 JSON_VALUE 仍会静默返回 NULL，不报错。

• 安全做法：先过滤 WHERE ISJSON(json_col) = 1
• 调试时可临时加一列看原始 JSON：json_col，确认结构是否符合预期路径
• 路径写错时，JSON_VALUE 不报错也不警告，只默默给 NULL——这是最常被忽略的坑

性能注意：JSON_VALUE 是运行时解析，别在 WHERE 中对大字段反复调用

每次调用 JSON_VALUE 都会重新解析整个 JSON 字符串。如果在 WHERE 或 JOIN 条件里多次使用（比如 WHERE JSON_VALUE(col,'$.a') = 'x' AND JSON_VALUE(col,'$.b') > 10），SQL Server 可能重复解析同一列多次。

• 优化方式：用 CROSS APPLY 提前解析一次，生成计算列再引用
• 或者把常用字段持久化为计算列并加索引：ALTER TABLE t ADD name AS JSON_VALUE(data, '$.user.name') PERSISTED
• 注意：PERSISTED 计算列要求 JSON_VALUE 表达式确定性，且 SQL Server 2016+ 才支持

总结一下核心要点：JSON_VALUE 的路径必须精确到叶子标量，且对无效输入沉默返回 NULL。实际写查询时，先用 ISJSON 和原始字段内容交叉验证，比反复猜路径高效得多。