How to update specific array element in json object

Question

I am manipulating a JSON column in an Azure SQL table / database, the JSON object is formed as follows:

{ "statusId": "5A5BC717-F33A-42A5-8E48-99531C30EC87", "creationDateTime": "", "assignations": [ { "userId": "CA3B0589-B558-4FCC-93A6-560754D324FC", "dateTime": "", "isCurrentAssigned": false }, { "userId": "CA3B0589-B558-4FCC-93A6-560754D325E8", "dateTime": "", "isCurrentAssigned": false }, { "userId": "CA3B0589-B558-4FCC-93A6-560754D347N", "dateTime": "", "isCurrentAssigned": true } ] } 

What I want to do is find a specific element inside the "destination" array and then update some of its properties, just something like:

 UPDATE MyTable SET JsonData = JSON_MODIFY(JsonData, '$.assignations.isCurrentAssigned', CONVERT(BIT, 0)) FROM MyDb WHERE JSON_VALUE(JsonData, '$.assignations.isCurrentAssigned') = CONVERT(BIT, 1) AND JSON_VALUE(JsonData, '$.assignations.userId') = CONVERT(UNIQUEIDENTIFIER, 'CA3B0589-B558-4FCC-93A6-560754D347N') 

Of course, this T-SQL is not working, I would appreciate any help with this

Answer 1

I found a “simple workaround” to handle this, it may not be the best solution, but I need a quick solution and it works.

Basically, I convert the array to a T-SQL table, update the records in this table as desired, and then convert this table to a JSON array and with this array I replace the original.

Code example:

 DECLARE @SomeJSON NVARCHAR(MAX) = '{ "statusId": "5A5BC717-F33A-42A5-8E48-99531C30EC87", "creationDateTime": "abc", "assignations": [ { "userId": "5A5BC717-F33A-42A5-8E48-99531C30EC87", "creationDateTime": "", "isCurrentAssigned": false }, { "userId": "5A5BC717-F33A-42A5-8E48-99531C30EC87", "creationDateTime": "", "isCurrentAssigned": false }, { "userId": "5A5BC717-F33A-42A5-8E48-99531C30EC87", "creationDateTime": "", "isCurrentAssigned": true } ] }' DECLARE @TblAssignations TABLE ( userId UNIQUEIDENTIFIER NULL, creationDateTime DATETIME NULL, isCurrentAssigned BIT NULL ) INSERT INTO @TblAssignations SELECT * FROM OPENJSON(@SomeJSON, '$.assignations') WITH(userId UNIQUEIDENTIFIER, creationDateTime DATETIME, isCurrentAssigned BIT) UPDATE @TblAssignations SET isCurrentAssigned = 0 WHERE userId = '5A5BC717-F33A-42A5-8E48-99531C30EC87' AND isCurrentAssigned = 1 INSERT INTO @TblAssignations VALUES ( '5A5BC717-F33A-42A5-8E48-99531C30EC87', '', 1 ) DECLARE @NewParentAssignations NVARCHAR(MAX) = (SELECT * FROM @TblAssignations FOR JSON PATH) SET @SomeJSON = JSON_MODIFY(@SomeJSON, '$.assignations', JSON_QUERY(@NewParentAssignations)) SELECT @SomeJSON 

Answer 2

We should do something like this at work and end up with a similar approach to the one you ended up with, although we do the processing directly when reading JSON to avoid using the temp table or var table.

  DECLARE @SomeJSON NVARCHAR(MAX) = '{ "statusId": "5A5BC717-F33A-42A5-8E48-99531C30EC87", "creationDateTime": "abc", "assignations": [ { "userId": "5A5BC717-F33A-42A5-8E48-99531C30EC87", "creationDateTime": "", "isCurrentAssigned": false }, { "userId": "5A5BC717-F33A-42A5-8E48-99531C30EC87", "creationDateTime": "", "isCurrentAssigned": false }, { "userId": "5A5BC717-F33A-42A5-8E48-99531C30EC87", "creationDateTime": "", "isCurrentAssigned": true } ] }' DECLARE @NewParentAssignations NVARCHAR(MAX) = ( SELECT * FROM ( SELECT --the update is done with the CASE clause userId, creationDateTime, CASE WHEN userId = '5A5BC717-F33A-42A5-8E48-99531C30EC87' AND isCurrentAssigned = 1 THEN CAST (0 AS BIT) ELSE isCurrentAssigned END AS isCurrentAssigned FROM OPENJSON(@SomeJSON, '$.assignations') WITH(userId UNIQUEIDENTIFIER, creationDateTime DATETIME, isCurrentAssigned BIT) UNION ALL -- the insert is done using UNION ALL SELECT '5A5BC717-F33A-42A5-8E48-99531C30EC87' AS userId, '' AS creationDateTime, CAST (1 AS BIT) AS isCurrentAssigned ) Result FOR JSON PATH ) SET @SomeJSON = JSON_MODIFY(@SomeJSON, '$.assignations', JSON_QUERY(@NewParentAssignations)) SELECT @SomeJSON 

In the end, it gives the same result.