JSON_MODIFY does not work with variable parameter-CodePudding

I have a SQL query which modifies some JSON. I'm iterating through the data and modifies part of the JSON based on the iteration.

For that i need to pass a variable parameter into JSON_MODIFY, but for some reason, it doesn't work!

SET @json = JSON_MODIFY(@ProData, '$.'   @ProKey   '.hasAnswer', CAST(1 as BIT))

I also tried passing a variable instead:

DECLARE @hasAnswerPath VARCHAR(100);
SET @hasAnswerPath = '$.'   @ProKey   '.hasAnswer';
SET @json = JSON_MODIFY(@ProData, @hasAnswerPath, CAST(1 as BIT))

But it has the same output, the hasAnswer is added to the root of the JSON and not in the element specified by @ProKey.

This works just fine:

SET @json = JSON_MODIFY(@ProData, '$.SomeName1.hasAnswer', CAST(1 as BIT))

It's like the @ProKey is disregarded.

Complete query:

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
    DECLARE @json NVARCHAR(max);

    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(@ProData, '$.'   @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

Sample JSON:

 {
     "SomeName1": {
        "header": "Some text": {
            "type": "specified",
            "numberValue": 1.0
        }
     },
     "SomeName2": {
        "header": "Some text",
        "answer": {
            "type": "specified",
            "numberValue": 4.0
         }
     },
     "SomeName3": {
         "header": "Some text",
         "answer": {
             "type": "specified",
             "numberValue": 2.0
        }
     }
 }

Expected result:

},
    "SomeName1": {
        "header": "Some text",
        "answer": {
            "type": "specified",
            "numberValue": 1.0
        }
        "hasAnswer": true,
        "questionType": "intro",
     }
}

Actual result:

},
    "SomeName1": {
            "header": "Some text",
            "answer": {
                "type": "specified",
                "numberValue": 1.0
            }
         }
     },
     "hasAnswer":true,
     "questionType":"intro"
 }

Am i doing something wrong here?

SOLVED:

I changed it from:

SET json=JSON_MODIFY(ProData, '$.'   ProKey   '.hasAnswer', CAST(1 as BIT)) 
SET json=JSON_MODIFY(json,'$.'   ProKey   '.questionType','intro')

To:

SET @json = @ProData;


SET json=JSON_MODIFY(json, '$.'   ProKey   '.hasAnswer', CAST(1 as BIT))
SET @json=JSON_MODIFY(json,'$.'   ProKey   '.questionType','intro')

Turns out, the data was overwritten!

CodePudding user response：

You seem to be overwriting your variables unintentionally:

-- first iteration

SET @json=JSON_MODIFY(@ProData, '$.'   @ProKey   '.hasAnswer', CAST(1 as BIT))
-- @json contains the @prodata after modification to first key (@prodata itself is not changed)

SET @json=JSON_MODIFY(@json,'$.'   @ProKey   '.questionType','intro')
-- @json (its first key to be more precise) is modified further

But on next iteration, this line will revert the modified @json back to the original value of @prodata. Only the last key will retain the modifications:

-- second iteration

SET @json=JSON_MODIFY(@ProData, '$.'   @ProKey   '.hasAnswer', CAST(1 as BIT))
-- you just overwrote your modifications with the value inside @prodata

The solution is to re-arrange the code a little, may be initialize @json outside the loop.