tsql - 如何在JSON对象中更新数组的特定项目

我正在处理SQL Azure表/数据库中的JSON列，JSON对象的格式如下:

{
  "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
    }
  ]
}

我要完成的工作是在数组“赋值”中找到一个特定元素，然后更新其某些属性，如下所示:

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')

当然，此T-SQL无法正常工作，对此我将不胜感激

最佳答案

我发现了一个“简单的解决方法”来解决这个问题，也许这不是最好的解决方案，但是我需要一个快速的解决方案，并且这种方法正在起作用。

基本上，我将数组转换为T-SQL表，根据需要更新该表上的记录，然后将该表转换为JSON数组，并使用该数组替换原始数组。

样例代码:

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