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

How many objects can you have in secondaries, is it always 1? — Charlieface
– Charlieface, Commented Sep 16, 2022 at 10:59

Charlieface · Accepted Answer · 2022-09-16 11:34:16Z

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

answered Sep 16, 2022 at 11:34

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zhorov · Accepted Answer · 2022-09-19 06:09:23Z

0

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'

edited Sep 19, 2022 at 6:09

answered Sep 16, 2022 at 8:48

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

2 Comments

Tarun Lalwani Over a year ago

This may not be a feasible approach as the number of fields in the json is almost 50+ and sometimes there may be additional fields which are not in all jsons.

Beacon of Wierd Over a year ago

There should be a warning on this dynamic solution, since you can preform injection attacks if you have access to any of the fields making up @stmt

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related