如何编写SQL存储过程处理JSON数据？

在 SQL Server 中，JSON 的处理一直存在一些尴尬之处——虽然原生支持，但实际使用中容易遇到陷阱。许多开发者在存储过程中遇到 JSON_VALUE 返回 NULL，或者拼接数组时数据丢失，根本原因是对这些函数的行为边界不够了解。本文重点介绍几个常见场景，以供参考。

SQL Server 2016 及以上版本原生支持 JSON 操作，但在存储过程中直接解析、构造、验证 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 类型。复杂的结构校验、深度遍历、模式匹配仍需依赖应用代码。存储过程仅适合轻量级透传、条件筛选或单层组装，不宜期望其处理所有任务。