Sql-server – How to update JSON using logic equivalent to WHERE clause

jsonsql-server-2016t-sql

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;

Best Answer

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.

Related Question