SQL Server 2016中JSON_MODIFY动态索引报错问题求助

问题情况 #

• 环境：SQL Server 2016 (SP2)
• 现象：首次插入JSON数据正常，但更新JSON值时触发错误：Change Existing JSON value "JSON_MODIFY" must be a string literal
• 触发原因：使用动态拼接的索引作为JSON_MODIFY的路径参数；改用静态索引（如$.RatedRecipes[0].Rate）时执行正常。

原问题代码 #

DECLARE @Index INT = (
SELECT rn - 1 
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, value
    FROM OPENJSON(@CurrentJson, '$.RatedRecipes')
) AS numbered
WHERE JSON_VALUE(value, '$.Id') = @ArticleId);

IF @Index IS NOT NULL
BEGIN
   SET @CurrentJson = JSON_MODIFY(@CurrentJson, '$.RatedRecipes[' + CAST(@Index AS NVARCHAR(5)) + '].Rate', @Rate);
END
ELSE
BEGIN
   DECLARE @RateRecipe NVARCHAR(MAX);
    SET @RateRecipe = CONCAT('{"Id": "', @ArticleId, '", "Rate": ', @Rate, '}');
    
    SET @CurrentJson = JSON_MODIFY(@CurrentJson, 'append $.RatedRecipes', JSON_QUERY(@RateRecipe));
END

正常的静态索引写法 #

SET @CurrentJson = JSON_MODIFY(@CurrentJson, '$.RatedRecipes[0].Rate', @Rate);

测试用JSON示例 #

{"FollowedAuthors": [], "Recipes": [{"Id": "B88EEE77-B779-491C-97C3-CDE8C1E7DF9F", "Title": "Tart", "Url": "/recipe-tart", "Type": "Liked"}], "RatedRecipes": [{"Id": "499D486C-0CF5-4708-A818-2F86E23A9C65", "Rate": 5},{"Id": "B8522F7C-08C9-4DB9-86A3-5494461451DF", "Rate": 2}]}

解决方案 #

SQL Server 2016的JSON_MODIFY不支持动态路径参数（该特性从SQL Server 2017开始支持），因此需要改用动态SQL实现：

修改更新逻辑，用sp_executesql执行动态语句，通过参数传递变量避免SQL注入风险：

DECLARE @Index INT = (
SELECT rn - 1 
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, value
    FROM OPENJSON(@CurrentJson, '$.RatedRecipes')
) AS numbered
WHERE JSON_VALUE(value, '$.Id') = @ArticleId);

IF @Index IS NOT NULL
BEGIN
    DECLARE @JsonPath NVARCHAR(100) = '$.RatedRecipes[' + CAST(@Index AS NVARCHAR(5)) + '].Rate';
    DECLARE @Sql NVARCHAR(MAX);
    
    -- 构造动态SQL语句
    SET @Sql = N'SET @CurrentJson = JSON_MODIFY(@CurrentJson, @JsonPath, @Rate);';
    
    -- 执行动态SQL，传递参数并输出更新后的JSON
    EXEC sp_executesql 
        @Sql,
        N'@CurrentJson NVARCHAR(MAX) OUTPUT, @JsonPath NVARCHAR(100), @Rate INT', -- 注意@Rate类型要和实际字段匹配
        @CurrentJson OUTPUT,
        @JsonPath,
        @Rate;
END
ELSE
BEGIN
   DECLARE @RateRecipe NVARCHAR(MAX);
    SET @RateRecipe = CONCAT('{"Id": "', @ArticleId, '", "Rate": ', @Rate, '}');
    
    SET @CurrentJson = JSON_MODIFY(@CurrentJson, 'append $.RatedRecipes', JSON_QUERY(@RateRecipe));
END

注意事项 #

• 确保@Rate的数据类型与JSON中Rate字段类型一致，比如如果是小数就把参数类型改成DECIMAL(18,2)。
• 动态SQL中必须通过参数传递变量，不要直接拼接变量值，防止SQL注入。

内容的提问来源于stack exchange，提问作者mach