SQL中JSON_MODIFY使用变量路径参数不生效问题咨询

你遇到的问题由两个常见错误共同导致：

1. 循环修改逻辑错误
   你在循环遍历@ProKey的过程中，每次调用JSON_MODIFY都基于原始未修改的@ProData，而非上一次修改后的@json变量。这会导致前面所有键的修改都被覆盖，仅最后一次循环的修改会被保留，也就出现了属性最终被加到根节点的异常表现。
2. SQL Server版本对动态路径的支持限制
   SQL Server 2016版本中，JSON_MODIFY的路径参数必须是静态字符串字面量，不支持传入动态拼接的变量作为路径。如果使用的是2016版本，动态拼接的路径会被识别为无效，导致新属性默认添加到JSON根节点。

适用SQL Server 2017及以上版本（支持动态路径变量） #

首先修正循环赋值逻辑，初始时将@json赋值为原始@ProData，后续所有修改都基于@json本身迭代，同时给动态键加上双引号避免特殊字符导致路径解析失败：

BEGIN TRAN

DECLARE @ProID as uniqueidentifier;
DECLARE @ProData as nvarchar(max);
DECLARE @ProKey as varchar(200);

DECLARE ProCursor CURSOR FOR
    SELECT Id, [Data] FROM [dbo].[PRO]

OPEN ProCursor;
FETCH NEXT FROM ProCursor INTO @ProID, @ProData;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 初始化@json为原始数据，后续所有修改都基于这个变量迭代
    DECLARE @json NVARCHAR(max) = @ProData;

    DECLARE DataCursor CURSOR FOR
        SELECT [key] FROM OPENJSON(@ProData) WHERE type = 5; --5 is object data
        
    OPEN DataCursor;
    FETCH NEXT FROM DataCursor INTO @ProKey;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 路径中的键用双引号包裹，避免键名含特殊字符导致解析错误
        SET @json=JSON_MODIFY(@json, '$."' + @ProKey + '".hasAnswer', CAST(1 as BIT))
        SET @json=JSON_MODIFY(@json,'$."' + @ProKey + '".questionType','intro')
        
        FETCH NEXT FROM DataCursor INTO @ProKey;
    END;

    UPDATE [dbo].[PRO]
    SET [Data] = @json
    WHERE Id = @ProID

    PRINT @json

    CLOSE DataCursor;
    DEALLOCATE DataCursor;

    FETCH NEXT FROM ProCursor INTO @ProID, @ProData;
END
CLOSE ProCursor;
DEALLOCATE ProCursor;

ROLLBACK

适用SQL Server 2016版本（不支持动态路径变量） #

需要通过动态SQL执行修改逻辑，内层循环部分替换为如下代码即可：

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'
        SET @json = JSON_MODIFY(@json, ''$."'+@ProKey+N'".hasAnswer'', CAST(1 as BIT))
        SET @json = JSON_MODIFY(@json, ''$."'+@ProKey+N'".questionType'', ''intro'')
    '
    EXEC sp_executesql @sql, N'@json NVARCHAR(MAX) OUTPUT', @json = @json OUTPUT

    FETCH NEXT FROM DataCursor INTO @ProKey;
END

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