SQL存储过程解析JSON参数教程
在SQLServer存储过程中使用JSON_VALUE函数解析JSON参数时，需确保参数声明为NVARCHAR(MAX)以避免Unicode字符问题。提取标量值要求路径精确匹配，中文键名需转义。解析前必须用ISJSON()函数校验输入字符串的合法性，防止静默失败。当需要提取多个或嵌套字段时，建议使用OPENJSON函数以提高代码可维护性和解析性能。
SQL Server存储过程解析JSON：避开那些“静默失败”的坑
核心结论：在SQL Server存储过程中，JSON_VALUE函数可以直接解析传入的参数。但有时结果返回NULL，看似“未生效”，问题通常不在函数本身，而在于输入的JSON字符串格式不合法，或指定的路径不正确。
存储过程参数必须声明为 NVARCHAR(MAX)
关键前提：SQL Server没有原生的JSON数据类型接收参数，所有JSON数据都需通过字符串传入。若使用VARCHAR类型，当JSON字符串包含中文键名、Emoji表情或其他Unicode字符时，可能发生截断或乱码。
- 因此，必须使用
NVARCHAR类型。JSON_VALUE函数内部按Unicode处理路径和内容。 - 长度建议使用
MAX，避免因长度限制截断包含长文本（如Base64图片）的JSON数据。 - 注意：
TEXT或XML类型与JSON函数不兼容，不可用于传递JSON参数。
JSON_VALUE 提取标量值时路径必须精确匹配
JSON_VALUE函数仅用于提取标量值（字符串、数字、布尔值、null等）。若路径指向一个对象（{}）或数组（[]），函数将静默返回NULL，不会报错，这种“静默失败”易被误解为解析成功。
- 路径正确：例如路径
'$.name'可正确提取字符串值'Alice'。 - 路径错误：若路径为
'$'（指向整个JSON根对象）或'$.address'（而address本身是一个对象），则返回NULL。此时应使用JSON_QUERY函数。 - 中文键名需转义：路径中的中文键名必须用双引号转义，例如
'$.["收货地址"]'。直接写'$.收货地址'无效。 - 数组元素使用下标：提取数组中第一个订单的金额，路径应写为
'$.orders[0].amount'。
必须先用 ISJSON() 校验再解析
外部输入的JSON字符串可能存在格式错误，如缺少引号、逗号位置错误或包含控制字符。JSON_VALUE遇到非法JSON时仍静默返回NULL，这与“路径正确但值为空”的情况难以区分。
- 前置校验：建议在存储过程开头添加判断：
IF ISJSON(@json_param) = 0 BEGIN RAISERROR('Invalid JSON', 16, 1) RETURN END，提前拒绝非法格式。 ISJSON()返回1表示字符串为合法JSON，可安全解析；返回0或NULL则不可信。- 注意：
JSON_VALUE解析错误不会抛出异常，因此无法通过TRY...CATCH捕获。
嵌套深、字段多时优先用 OPENJSON 而非连写多个 JSON_VALUE
当需要从同一JSON中提取大量字段时，连续使用多个JSON_VALUE会导致代码冗长、难以维护，且存在性能问题。
- 若需提取5个以上字段，或字段分布在不同的嵌套层级（如同时需要
$.user.name和$.order.items[0].price），连续使用JSON_VALUE会使代码复杂。 - 此时，
OPENJSON是更优选择。示例：OPENJSON(@json_param) WITH (name NVARCHAR(50) '$.user.name', price DECIMAL(10,2) '$.order.items[0].price')。一次解析，结构清晰，所有字段一目了然。 OPENJSON还能自动处理null值映射，而JSON_VALUE在键不存在和键值为null时均返回NULL，无法区分这两种情况。- 注意：
OPENJSON是行集函数，需配合SELECT或INSERT INTO #temp使用，不能直接赋值给变量。这一调整对提升代码可维护性是值得的。
最后，一个易被忽略但对性能影响关键的细节：SQL Server的所有JSON函数均基于字符串解析，没有缓存机制。若在同一JSON上反复调用JSON_VALUE多次，相当于进行了多次完整解析。对于此类场景，正确做法是使用OPENJSON一次性将JSON展开到临时表或表变量中，后续所有操作基于该临时表查询，这是提升效率的关键。