3

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;
3
  • Loops are anathema to SQL. Any way you can use OPENJSON or JSON_QUERY in combination with WHERE to get the objects to update as rows, then JSON_MODIFY it without referring to an array index? You could shred rows into a temporary table first, and build the result back with FOR JSON. If all else fails you could, of course, build the whole JSON_MODIFY expression with dynamic SQL, but that should be a last resort (and doesn't play nice with variables in an outer scope, regardless). Commented Jul 11, 2017 at 0:01
  • 1
    I tried the Dynamic SQL route, and still had issues (namely the scope issues). I am baffled that passing in a simple index value can bring the JSON_MODIFY function to its knees. Commented Jul 11, 2017 at 0:54
  • Awesome man, Your Update code work seamlessly for me. You should add that as an answer. Commented Jan 8, 2021 at 6:21

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.