SQL Server 2016: can't reference JSON array path using JSON_MODIFY

Viewed 2124

I have tried for a few hours to get my issue working, but I am unsuccessful.

I pass a complex JSON object to a stored procedure and after I insert the main record, I then get that new record ID, and use it to change a couple other ID's within the JSON object.

I am successful using JSON_MODIFY like this.. when the value is not within an array.

SET @json = JSON_MODIFY(@json, '$.Weather.RoundID', @round_identity) 

but when the value is within an array, I can't seem to get it to work.

This works when I hardcode the index of the path:

SET @json = JSON_MODIFY(@json, '$.Scores[0].RoundID', @round_identity);

This doesn't work:

SET @json = JSON_MODIFY(@json, '$.Scores['+ STR(@Score_Counter) + '].RoundID', @round_identity);

The error is:

The argument 2 of the "JSON_MODIFY" must be a string literal

The value @Score_Counter is generated by a while loop to index the current array object.

Any help would be appreciated.

Thank you

Bill

Update: With the help of Bogdan, I was able to successfully change the json in the arrays. Here is what I am using:

--Loop and populate all scores to set RoundID using Dynamic SQL
Set @Score_Counter = 0;
Set @Score_Count = (SELECT COUNT(*) FROM OPENJSON(@json, N'lax $.Scores'))

WHILE @Score_Counter < @Score_Count
BEGIN
    SELECT  @SqlStatement = CONCAT(N'SELECT @json = JSON_MODIFY    (@json, ''$.Scores[', @Score_Counter, N'].RoundID'', ' + cast(@round_identity as nvarchar(10))+')')
    EXEC    sp_executesql @SqlStatement, N'@json NVARCHAR(MAX) OUTPUT', @json = @json OUTPUT
    SET @Score_Counter = @Score_Counter + 1;
END;
0 Answers
Related