How to update JSON using logic equivalent to WHERE clause

Question

My question is similar to another (and also another for postgresql) except in the other question the person wants to update values for a specific key. In my case, I want to detect elements having a specific key-value pair, and then update the value.

Here is a mock-up of my JSON structure using dummy values:

declare @data nvarchar(max);
set @data = N'{"add":[{"id":-1,"valA":7,"valB":8,"valC":"text","valD":{"3":"text"}},'
+ '{"id":-2,"valA":5,"valB":8,"valC":"Moretext","valD":{"2":"textB","3":"Moretextagain"}}'
+ '{"id":-1,"valA":9,"valB":8,"valC":"SecondCase","valD":{"2":"textX","3":"MoretextagainSigh"}}'
+ ']}';
select * from openjson(@data);

Basically the problem is: if you can find "id":-1 then update it to "id":999.

(The use case is that the data here are linked to other data and none of it has been persisted. The other data is identified by the negative ID values. After persisting the other data I get a "real" ID back from the database. I now need to update this linked data to remove the negative ID and replace it with the one that the database provided.)

From the other question I can see one option is to extract all of this into a table, perform the update and rebuild the JSON. I feel the structure is non-trivial (for example, the object for valC may have different numbers of elements). It's simple, yes, but outputting all this to a table then rebuilding it - I think is not simple.

I really want this pseudocode to work:

update @data 
set 'add.id' = 999 
where 'add.id' = -1;

Answer 1

Following is a worked example.

Caveat: the final result is a JSON fragment which is the content of the '$.add' array, rather than the original @data variable containing that array - but the logic is good for solving the problem

declare @data nvarchar(max);
set @data = N'{"add":[{"id":-1,"valA":7,"valB":8,"valC":"text","valD":{"3":"text"}},'
+ '{"id":-2,"valA":5,"valB":8,"valC":"Moretext","valD":{"2":"textB","3":"Moretextagain"}},'
+ '{"id":-1,"valA":9,"valB":8,"valC":"SecondCase","valD":{"2":"textX","3":"MoretextagainSigh"}}'
+ ']}';
select * from openjson(@data);
declare @idToFind int = -1;
declare @newId int = 999;
declare @arrayElement nvarchar(max);
declare @tempSQL nvarchar(max);
declare @add nvarchar(max);
declare @nDoublequote nvarchar(1) = cast(char(39) as nvarchar(1)); -- double quote character as an nvarchar
select @add = json_query(@data, '$.add');
-- Loop through the elements in the '$.add' array that have an id value we are interested in
declare cur_json cursor local fast_forward for select cast([key] as nvarchar(16)) from openjson(@add) where (select json_value([value],'$.id')) = @idToFind;
open cur_json;
fetch next from cur_json into @arrayElement;
 while @@fetch_status = 0
 begin 
 -- an element in the '$.add' array has an id that needs updating
 set @tempSQL = concat(N'select @add = json_modify(@add, ' , @nDoublequote , N'$[' , @arrayElement , N'].id' , @nDoublequote , N', ' , @nDoublequote , cast(@newId as nvarchar(16)) , @nDoublequote, N')');
 select ' tempSQL: '+isNull(@tempSQL,'<null>')+'. '; -- just for debugging purposes
 exec sp_executeSQL @tempSQL, N'@add NVARCHAR(MAX) OUTPUT', @add = @add OUTPUT
 fetch next from cur_json INTO @arrayElement; -- check for other array elements having the same id value
 end
close cur_json;
deallocate cur_json;
select * from openjson(@add);

The key to this solution is the cursor which iterates through the array but selects only elements that match on our required id value (ie. the WHERE part of my question). This cursor actually only returns the array's [key] value - which is, in other words, the matching element's array index.

The second part of this solution is to build a string containing the SQL that uses json_modify to modify the value, referencing that value using the array index we just obtained from the cursor, and then using exec sp_executeSQL to effect that.

I'll be honest, I'm not 100% sure why the parameters are required with that procedure - I've taken this part of the solution from yet another question.

This approach -

• having the sought and replacement values in parameters
• using a cursor to obtain the array indices for matching elements
• then building dynamic SQL to effect the changes

is reproducible and actually fairly simple once you strip away the comments and excess select statements that illustrate the solution.

• Andriy M: A variation on this would be to generate (and execute) a single SQL batch that would include all the JSON_MODIFYs required, something along these lines. No cursor-related overhead, and the data exchange between the calling query and the dynamic query happens only once. The effect of the former would probably be negligible, but that of the latter might be noticeable depending on the size and complexity of the JSON in question.