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

CodePudding user response：

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

CodePudding user response：

The JSON_MODIY() function doesn't support wild cards for the path parameter, so possible options (...query based...) are:

• Parse, modify and build the JSON content for each row (this approach expects a known and fixed JSON structure).
• Generate and execute a dynamic statement with nested JSON_MODIFY() calls.

Parse, modify and build the JSON content for each row:

DECLARE @id varchar(100) = '123'
DECLARE @secondaryId int = 1  

UPDATE testing
SET json = (
   SELECT 
      secondaryId,
      primaries = CASE
         WHEN secondaryId = @secondaryId THEN 
            (
            SELECT [primary], 'PASSED' AS status
            FROM OPENJSON(_primaries) WITH ([primary] int '$.primary')
            FOR JSON PATH
            )
         ELSE JSON_QUERY(_primaries)
      END
   FROM OPENJSON(json, '$.secondaries') WITH (
      secondaryId int '$.secondaryId',
      _primaries nvarchar(max) '$.primaries' AS JSON  
   )
   FOR JSON PATH, ROOT('secondaries')
)
WHERE (id = @id)  

Dynamic statement:

DECLARE @stmt nvarchar(max) = N''
DECLARE @id varchar(100) = '123'
DECLARE @secondaryId int = 1  
  
; WITH cte AS (
   SELECT 
     id, 
     CONCAT('$.secondaries[', j1.[key], ']', '.primaries[', j2.[key],']')  AS primaryPath
  FROM testing
  CROSS APPLY OPENJSON(json, '$.secondaries') AS j1
  CROSS APPLY OPENJSON(j1.[value], '$.primaries') AS j2
  WHERE (id = @id) AND JSON_VALUE(j1.[value], '$.secondaryId') = @secondaryId
)
SELECT 
   @stmt = CONCAT(
      N'UPDATE testing SET json = ',
      STRING_AGG(N'JSON_MODIFY(', N''),
      N'json',
      STRING_AGG(CONCAT(N',''', primaryPath, N'.status'',''PASSED'')'), N''),
      N' WHERE id = @id'
   )
FROM cte  

DECLARE @err int  
EXEC @err = sp_executesql @stmt, N'@id varchar(100)', @id
IF @err = 0 PRINT 'Success' ELSE PRINT 'Error'