How to update JSON using logic equivalent to WHERE clause
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,'&lt;null&gt;')+'. '; -- 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.