SQL存储过程如何解析并处理JSON参数输入？

在存储过程中处理 JSON 参数时，许多开发者首先想到直接使用 JSON_VALUE 或 OPENJSON 提取数据，结果调试时发现返回全为 NULL，容易误以为是数据本身的问题。实际根源往往在于参数声明和校验这两个最容易忽略的环节——未按规定操作，SQL Server 不会报错，而是静默返回 NULL。

必须使用 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

JSON_VALUE 仅接受指向字符串、数字、布尔或 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 展开