SQL Server:比较json并查找新添加、更改和删除的记录

Question

我想要比较SQL Server中的两个json，找出新增、修改和删除的值。

注:未定义输入cif_id订单。

例如，我有以下旧的和新的json：

年长的

{
 "cifs":[
    {
      "cif_id":"1",
      "bank_code":"110"
    },
    {
      "cif_id":"2",
      "bank_code":"120"
    },
    {
      "cif_id":"3",
      "bank_code":"130"
    }
  ]
}

和新的/更改的值

{
 "cifs":[
    {
      "cif_id":"1",
      "bank_code":"111"
    },
    {
      "cif_id":"2",
      "bank_code":"122"
    },
    {
      "cif_id":"4",
      "bank_code":"140"
    }
  ]
}

这里修改了cif_id 1和2，删除了3，添加了4。

我的返回值将被UI团队用来显示在页面上。我可以使用存储过程或函数，也可以使用纯SQL，我的返回应该是json格式。

结果：

{
  "added":[
     {
      "cif_id":"4",
      "bank_code":"140"
    }
  ],
  "modified":[
     {
      "cif_id":"1",
      "bank_code":"111"
    },
    {
      "cif_id":"2",
      "bank_code":"122"
    }
  ],
  "deleted":[
     {
      "cif_id":"3",
      "bank_code":"130"
    }
  ]
}

Answer 1

一种可能的方法：

DECLARE @old nvarchar(max) = N'{
 "cifs": [
    {"cif_id":"1", "bank_code":"110"},
    {"cif_id":"2", "bank_code":"120"},
    {"cif_id":"3", "bank_code":"130"}
  ]
}'
DECLARE @new nvarchar(max) = N'{
 "cifs": [
    {"cif_id":"1", "bank_code":"111"},
    {"cif_id":"2", "bank_code":"122"},
    {"cif_id":"4", "bank_code":"140"}
  ]
}'

; WITH OldCTE AS (
   SELECT *
   FROM OPENJSON(@old, '$.cifs') WITH (
      cif_id nvarchar(1) '$.cif_id',
      bank_code nvarchar(3) '$.bank_code'
   )
), NewCTE AS (
   SELECT *
   FROM OPENJSON(@new, '$.cifs') WITH (
      cif_id nvarchar(1) '$.cif_id',
      bank_code nvarchar(3) '$.bank_code'
   )
)
SELECT
   added = (
      SELECT cif_id, bank_code
      FROM NewCTE 
      WHERE cif_id NOT IN (SELECT cif_id FROM OldCTE)
      FOR JSON AUTO
   ),
   modified = (
      SELECT n.cif_id, n.bank_code 
      FROM NewCTE n
      INNER JOIN OldCTE o ON n.cif_id = o.cif_id AND n.bank_code <> o.bank_code
      FOR JSON AUTO
   ),
   deleted = (
      SELECT cif_id, bank_code
      FROM OldCTE 
      WHERE cif_id NOT IN (SELECT cif_id FROM NewCTE)
      FOR JSON AUTO
   )
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

结果：

{
   "added":[
      {
         "cif_id":"4",
         "bank_code":"140"
      }
   ],
   "modified":[
      {
         "cif_id":"1",
         "bank_code":"111"
      },
      {
         "cif_id":"2",
         "bank_code":"122"
      }
   ],
   "deleted":[
      {
         "cif_id":"3",
         "bank_code":"130"
      }
   ]
}