如何在SQL Server中使用JSON_VALUE函数提取嵌套JSON内容

JSON_VALUE只能提取标量值，无法直接获取多层嵌套对象或数组；必须用完整路径（如'$.data.user.name'）且区分大小写；路径错误、目标非标量或输入非法时均静默返回NULL，需配合ISJSON验证和OPENJSON处理数组。

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

SQL Server 的 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.statusFROM your_tableCROSS 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 和原始字段内容交叉验证，比反复猜路径高效得多。