SQL存储过程处理JSON数据的编写方法

在 SQL Server 中处理 JSON 数据时，很多开发者会遇到一些隐蔽的陷阱。尤其是在存储过程中使用 JSON_VALUE 返回 NULL，或者拼接 JSON 数组时数据意外丢失，这些问题往往源于对 JSON 函数行为边界理解不足。本文将聚焦几个高频问题场景，帮助您避开这些坑。

SQL Server 2016 及以上版本虽然原生支持 JSON 操作，但在存储过程中直接进行解析、构造和验证时，必须高度关注各函数的行为边界以及类型隐式转换。一旦疏忽，可能导致返回 NULL 或静默失败，排查起来极为棘手。

JSON_VALUE 提取字段时为什么总返回 NULL？

最常见的原因在于路径表达式错误或数据类型不匹配。JSON_VALUE 仅能提取标量值（字符串、数字、布尔值、null），并且路径必须严格匹配大小写和嵌套结构。它不会抛出错误，而是静默地返回 NULL，这一点尤其需要警惕。

那么，问题到底出在哪？以下几个关键点值得留意：

• 确保 JSON 字符串本身格式合法：用 ISJSON(@json) 先校验，返回 1 才能继续往下操作
• 路径中的键名必须用双引号包裹，比如 '$.user.name'，不要写成 '$."user".name'（除非键名本身就包含特殊字符）
• 如果目标字段是数组类型（比如 '$.tags'），JSON_VALUE 会直接返回 NULL——此时应改用 JSON_QUERY
• 数字字段若在原始 JSON 中被引号包裹（比如 "age": "25"），JSON_VALUE 返回的是字符串，需要手动 TRY_CAST(... AS INT) 才能转换

如何安全地把多行结果拼成 JSON 数组？

FOR JSON 是最可靠的方式，但 WITHOUT_ARRAY_WRAPPER 和根层级控制很容易被忽略，导致输出结果不是预期格式。

• 单行结果默认包含外层花括号：SELECT 1 AS a FOR JSON PATH 会得到 {"a":1}
• 多行结果需要添加 ROOT('items') 或者把 WITHOUT_ARRAY_WRAPPER 设为 0，才能生成数组；否则每行输出一个独立对象，中间连逗号都没有
• 避免在子查询里再套一层 FOR JSON——嵌套过深很容易触发 2MB 的默认大小限制，改用 JSON_QUERY 注入已序列化的片段会更稳妥
• NULL 值默认会被忽略，如果希望保留，记得加上 INCLUDE_NULL_VALUES

存储过程里修改 JSON 字段要用 JSON_MODIFY，但嵌套更新很脆弱

JSON_MODIFY 不支持动态路径拼接，也不能一次性修改多个键；路径写错的话，它会直接返回原始 JSON，连一个错误提示都没有。

• 更新数组元素必须使用数组下标，比如 '$.items[0].price'；用变量拼接路径（比如 '$.items[' + CAST(@i AS VARCHAR) + '].price'）会直接报语法错误
• 新增键时，父路径必须存在，否则更新操作会失败（返回原值）；可以通过 JSON_MODIFY(JSON_MODIFY(...), ...) 多层嵌套来逐层构造
• 删除键要用 JSON_MODIFY(@json, 'lax $.key', NULL)，lax 模式能容忍缺失路径，strict 模式则会报错
• 对大 JSON（超过 2MB）反复调用 JSON_MODIFY 性能很差，建议拆到应用层处理

必须承认的是，JSON 在 SQL Server 里始终不是“一等公民”——所有函数本质上都是基于字符串操作，没有真正的 JSON 类型。真正复杂的结构校验、深度遍历、模式匹配，还是得靠应用代码来完成。存储过程只适合做轻量级透传、条件筛选或者单层组装，别指望它包办一切。