Update json in same row multiple times with for different JSON paths

Question

I need to update a JSON data present in a row, with multiple updates to the same row. Below is the kind of json

{
  "secondaries": [
    {
      "secondaryId": 1,
      "primaries": [
        {
          "primary": 1,
          "status": "UNKNOWN"
        },
        {
          "primary": 2,
          "status": "UNKNOWN"
        }
      ]
    }
  ]
}

CREATE TABLE testing(
   id VARCHAR(100),
   json nvarchar(max)
);

INSERT INTO testing values('123', '{"secondaries":[{"secondaryId":1,"primaries":[{"primary":1,"status":"UNKNOWN"},{"primary":2,"status":"UNKNOWN"}]}]}');

I want to update status for all the primary as PASSED. So I first created a CTE

with cte as (select id,
                      CONCAT('$.secondaries[', t.[key], ']', '.primaries[', t2.[key],
                             ']')  as primaryPath
               from testing
                        cross apply openjson(json, '$.secondaries') as t
                        cross apply openjson(t.value, '$.primaries') as t2
               where id = '123'
               and json_value(t.value, '$.secondaryId') = 1
)
select * from cte;

This gives me below results

Now if I try and update the records using below sql query:

with cte as (select id,
                      CONCAT('$.secondaries[', t.[key], ']', '.primaries[', t2.[key],
                             ']')  as primaryPath
               from testing
                        cross apply openjson(json, '$.secondaries') as t
                        cross apply openjson(t.value, '$.primaries') as t2
               where id = '123'
               and json_value(t.value, '$.secondaryId') = 1
)
update testing
set json = JSON_MODIFY(json, cte.primaryPath + '.status', 'PASSED')
from testing
cross join cte 
where cte.id = testing.id;

select * from testing;

Only one of the records gets updated. I want all the records to get update. How can I achieve the same?

http://sqlfiddle.com/#!18/b61e1/6

I do have a working solution to do it, but it is not a query based one. I am looking for a possibility to do it just via the query itself

OPEN @getid
FETCH NEXT
FROM @getid INTO @id, @primaryPath
WHILE @@FETCH_STATUS = 0
    BEGIN
        update testing
        set json = JSON_MODIFY(json, @primaryPath + '.status', 'PASSED')
        where testing.id = @id

        FETCH NEXT
            FROM @getid INTO @id, @primaryPath
    END

CLOSE @getid
DEALLOCATE @getid

Answer 1

If you don't want to rebuild the whole JSON, you can use nested JSON_MODiFY calls.

It gets more complicated because of the doubly nested arrays, you need to use STRING_AGG also, and JSON_QUERY to prevent double-escaping.

UPDATE t
SET json = JSON_MODIFY(t.json, '$.secondaries', JSON_QUERY(j1.secondaries_new))
FROM testing t
CROSS APPLY (
    SELECT '[' + STRING_AGG(JSON_MODIFY(secondaries.value, '$.primaries', JSON_QUERY(j2.primaries_new)), ',') + ']'
    FROM OPENJSON(t.json, '$.secondaries') secondaries
    CROSS APPLY (
        SELECT '[' + STRING_AGG(JSON_MODIFY(primaries.value, '$.status', 'PASSED'), ',') + ']'
        FROM OPENJSON(secondaries.value, '$.primaries') primaries
    ) j2(primaries_new)
) j1(secondaries_new);

db<>fiddle