sql - 在 SQL Server 2017 中修改多个 JSON 数组元素

Question

我有一个 SQL Server 2017 表 Orders，它有一个 OrderId 主键和 nvarchar(max) 列 Details。此列包含一个表示“项目”数组的 json 字符串。这是一个示例:

{ items[
{
    "id": 1,
    "isDeleted": false
},
{
    "id": 2,
    "isDeleted": false
},
{
    "id": 3,
    "isDeleted": false
},
{
    "id": 4,
    "isDeleted": false
}
] }

我想弄清楚是否有一种方法可以让我更新 Details 中的一个或多个 isDeleted 属性的单个(或几个)SQL 语句此表的 列，给定表中记录的 OrderId 以及要更新的 Details 列中的 ID 列表。

例如，对于给定的 OrderId，我想在 Details JSON 字符串记录中将 ID 2 和 3 更新为 true。我知道我可以在 while 循环中使用 json_modify 执行此操作，但我想知道是否有更优雅的解决方案结合 json_modify、json_query 或 openjson。

提前感谢您的任何建议。

Answer 1

您可以使用以下方法之一:

• 解析每个 OrderId uisng OPENJSON() 和显式模式的 Details JSON。结果是一个包含列的表，在 WITH 子句中定义。更新此表并使用 FOR JSON 再次将更改的数据作为 JSON 返回。
• 解析每个 OrderId uisng OPENJSON() 和默认模式的 Details JSON。结果是一个表，其中包含 key、value 和 type 列以及 items< 中的每个项目(JSON 对象)一行 JSON 数组。更新此表并使用基于字符串的方法生成 items JSON 数组(我不认为 FOR JSON 可以生成标量值数组/JSON 对象)。使用 JSON_MODIFY() 更新源表中的 JSON。
• 使用 JSON_MODIFY() 生成并执行动态语句

包含数据的表格:

CREATE TABLE Orders (OrderId int, Details nvarchar(max))
INSERT INTO Orders (OrderId, Details)
VALUES 
   (1, N'{"items":[{"id":1,"isDeleted":false},{"id":2,"isDeleted":false},{"id":3,"isDeleted":false},{"id":4,"isDeleted":false}]}'),
   (2, N'{"items":[{"id":11,"isDeleted":false},{"id":12,"isDeleted":false},{"id":13,"isDeleted":false}]}')

带有 ID 的表:

CREATE TABLE ItemIds (id int)
INSERT INTO ItemIds (id) VALUES (1), (3)

带有 OPENJSON() 和显式模式的语句:

UPDATE Orders
SET Details = (
   SELECT 
      j.id AS id, 
      CONVERT(bit, CASE WHEN i.id IS NOT NULL THEN 1 ELSE j.isDeleted END) AS isDeleted
   FROM OPENJSON(Details, '$.items') WITH (
      id int '$.id', 
      isDeleted bit '$.isDeleted'
   ) j
   LEFT OUTER JOIN ItemIds i ON j.id = i.id
   FOR JSON AUTO, ROOT('Items')
)
WHERE OrderId = 1

带有 OPENJSON() 和默认架构的语句:

UPDATE Orders
SET Details = JSON_MODIFY(
   Details,
   '$.items',
   JSON_QUERY((
      SELECT CONCAT(
         '[', 
         STRING_AGG(
            CASE 
               WHEN i.id IS NULL THEN j.[value] 
               ELSE JSON_MODIFY(j.[value], '$.isDeleted', CONVERT(bit, 1)) 
            END,
            ','
         ),
         ']'
      )   
      FROM OPENJSON(Details, '$.items') j
      LEFT OUTER JOIN ItemIds i ON CONVERT(int, JSON_VALUE(j.[value], '$.id')) = i.id
   ))
)
WHERE OrderId = 1

动态语句:

DECLARE @stm nvarchar(max) 
SELECT @stm = STRING_AGG(
    CONCAT(
      'UPDATE Orders ',
      'SET Details = JSON_MODIFY(Details, ''$.items[', a.[key], '].isDeleted'', CONVERT(bit, 1)) ',
      'WHERE OrderId = ', o.OrderId, ';'
   ),
   ' '
)   
FROM Orders o
CROSS APPLY (
   SELECT o.OrderId, j1.[key]
   FROM OPENJSON(o.Details, '$.items') j1
   CROSS APPLY OPENJSON(j1.[value]) WITH (id int '$.id') j2
   WHERE j2.id IN (SELECT id FROM ItemIds)
) a   
WHERE o.OrderId = 1

PRINT @stm 
EXEC sp_executesql @stm

结果:

OrderId Details
1   {"items":[{"id":1,"isDeleted":true},{"id":2,"isDeleted":false},{"id":3,"isDeleted":true},{"id":4,"isDeleted":false}]}
2   {"items":[{"id":11,"isDeleted":false},{"id":12,"isDeleted":false},{"id":13,"isDeleted":false}]}