SQL存储过程中解析JSON格式参数输入的实现方法

在 SQL Server 存储过程中处理 JSON 参数时，许多开发人员的首要反应是直接用 JSON_VALUE 或 OPENJSON 提取数据，结果花费大量时间调试却发现返回的全是 NULL，甚至误以为源数据本身有问题。实际上，问题的根源往往出现在参数声明与数据校验这两个最容易忽略的环节——只要不按规定操作，SQL Server 连错误都不会提示，而是直接静默地吞噬结果。

必须使用 NVARCHAR(MAX) 类型声明参数，并且在解析前务必调用 ISJSON() 进行格式校验——只要遗漏其中任何一个环节，JSON_VALUE 和 OPENJSON 都会静默返回 NULL，而不会抛出明确的错误信息。

参数声明必须是 NVARCHAR(MAX)

SQL Server 没有原生的 JSON 数据类型，所有 JSON 数据都必须依赖字符串来承载。如果使用 VARCHAR，中文键名（如 "收货地址"）、emoji 表情或者 Base64 字段容易发生乱码或截断；而使用 TEXT 或 XML 类型则会直接导致 JSON 函数不兼容。
因为路径和内容在内部按 Unicode 解析，所以 NVARCHAR 是强制要求。
长度不能写成 NVARCHAR(4000) ——即便你“确信”不会超长，实际运行时包含图片 Base64 的 JSON 很容易突破这个上限。

• 正确：@json_param NVARCHAR(MAX)
• 错误：@json_param VARCHAR(8000)、@json_param TEXT、@json_param NVARCHAR(4000)

JSON_VALUE 只能取标量，路径写错或类型不匹配就返回 NULL

它只接受指向字符串、数字、布尔值或 null 的路径；如果路径指向对象（例如 $.address）或数组（例如 $.tags），结果就会是 NULL——这不是函数失效，而是语义不匹配。
中文键名必须转义：使用 '$.["收货地址"]'，不能写成 '$.收货地址'。
数组元素用方括号：'$[0].amount' 提取第一个订单金额。
返回值默认是 NVARCHAR(4000)，如果字段可能超长，需要显式转换：CAST(JSON_VALUE(@json, '$.desc') AS NVARCHAR(MAX))。

• 安全用法：JSON_VALUE(@json, '$.name') → 返回字符串
• 危险用法：JSON_VALUE(@json, '$.address') → 若 address 是对象，返回 NULL，不要误以为“字段为空”
• 避免在 WHERE 中裸用：WHERE JSON_VALUE(data, '$.status') = 'done' ——没有索引时每次全表解析 JSON，性能会急剧下降

多字段或嵌套结构优先用 OPENJSON + WITH 子句

从同一段 JSON 提取 3 个以上字段，或者字段跨不同层级（如 $.user.name 和 $.order.items[0].price），硬写一堆 JSON_VALUE 不仅难以维护，CPU 开销也会很高。
OPENJSON 必须配合 WITH 才能映射成可直接使用的列；不带 WITH 只会返回 key/value/type 三列，全部是字符串，无法直接用于业务逻辑。
路径必须以 $ 开头：'$.user.name' 正确，'user.name' 会静默失败。
嵌套数组要用 CROSS APPLY 分层展开：先 OPENJSON(@json, '$.orders')，然后对每行 CROSS APPLY OPENJSON(value, '$.items')。

• 正确：OPENJSON(@json) WITH (name NVARCHAR(50) '$.user.name', price DECIMAL(10,2) '$.order.items[0].price')
• 错误：OPENJSON(@json) 不加 WITH，或 WITH 里写 'user.name'（缺少 $）
• 调试技巧：临时在关键 JSON_VALUE 路径后加上 ERROR ON ERROR，比如 JSON_VALUE(@json, '$.user.profile.phone' ERROR ON ERROR)，一旦出错就会立即中断执行

别把 JSON_QUERY 当 JSON_VALUE 用

如果 JSON 中某个字段是对象或数组（例如 "tags": ["a","b"]），用 JSON_VALUE 提取会返回 NULL；必须使用 JSON_QUERY 才能保留原始结构。
JSON_VALUE(@j, '$.tags') → NULL
JSON_QUERY(@j, '$.tags') → ["a","b"]（原样字符串，无额外引号）
在拼接新 JSON 时尤其需要注意：CONCAT('{ "list": ', JSON_QUERY(@j, '$.tags'), ' }') 是安全的；如果误用 JSON_VALUE，会导致变成 "list": "["a","b"]"，使得前端的 JSON.parse() 直接报错。

• 标量值（字符串/数字/布尔）→ 用 JSON_VALUE
• 对象或数组 → 必须用 JSON_QUERY
• 复杂点在于嵌套层级越深，CROSS APPLY 链越长，容易漏掉某一层的 OPENJSON 展开