How to update a specific item of an array in json object

Question

I'm manipulating a JSON column in a SQL Azure table/database, the JSON object is formed like this:

{
  "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 accomplish is to find a specific element inside the array "assignations" 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 will appreciate any help on this

Answer 1

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

  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

At the end this yields the same result.

Answer 2

I've found a "simple workaround" to handle this, maybe it is not the best solution but I need a quick solution and this is working.

Basically I convert the array to a T-SQL Table, update the records on that table as desired, then convert that table to a JSON Array and with that array I replace the original one.

Sample code:

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