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;
OPENJSONorJSON_QUERYin combination withWHEREto get the objects to update as rows, thenJSON_MODIFYit without referring to an array index? You could shred rows into a temporary table first, and build the result back withFOR JSON. If all else fails you could, of course, build the wholeJSON_MODIFYexpression with dynamic SQL, but that should be a last resort (and doesn't play nice with variables in an outer scope, regardless).