JSON_MODIFY does not work with variable parameter

Question

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 the whole expression as a variable:

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",
        "answer": {
            "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"
 }

What am I doing wrong here?

Perhaps something is wrong with the code that you didn't post. Is there anything useful that you could add? Like what the json looks like and the unexpected result? — Salman Arshad
– Salman Arshad, Commented Dec 7, 2021 at 13:59
It can easily be tested with creating a new table with columns Id and Data and insert NewGuid and JSON sample — Lasse O
– Lasse O, Commented Dec 7, 2021 at 14:21
Please have one more look at the actual result. Is the last key populated correctly (and other ones aren't)? — Salman Arshad
– Salman Arshad, Commented Dec 7, 2021 at 14:51

Salman Arshad · Accepted Answer · 2021-12-07 15:04:21Z

1

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.

edited Dec 7, 2021 at 15:04

answered Dec 7, 2021 at 12:49

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lasse O Over a year ago

But the problem is not json or ProData, but that ProKey is not working as a parameter in JSON_MODIFY.

Salman Arshad Over a year ago

I have no clue then. There is an obvious flaw in your code, it'll only update the last key. There is no way for it to update the parent. $..foo or null should throw an exception instead of modifying root object. May be your JSON is nested two lavels deep which you did not post.

Collectives™ on Stack Overflow

JSON_MODIFY does not work with variable parameter

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related