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

JSON_VALUE返回NULL的主因是路径错误或类型不匹配；它仅提取标量值，要求JSON合法（ISJSON=1）、路径以$开头且严格匹配大小写和结构，指向数组或对象时静默返回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 类型。真正复杂的结构校验、深度遍历、模式匹配，还是得靠应用代码——存储过程只适合做轻量级透传、条件筛选或单层组装。