json - 从 SQL Server 中删除重复的 JSON 节点

Question

我想从 SQL Server 表的列中的 JSON 中删除重复节点。

JSON 列:

{
    "Categories": [
        {
            "Type": "Type1",
            "GDS": [
                {
                    "GDSName": "Type1_test1",
                    "IsEnable": true,
                    "Priority": 2
                },
                {
                    "GDSName": "Type1_test2",
                    "IsEnable": false,
                    "Priority": 0
                }
            ]
        },
        {
            "Type": "Type2",
            "GDS": [
                {
                    "GDSName": "Type2_test1",
                    "IsEnable": false,
                    "Priority": 0
                },
                {
                    "GDSName": "Type2_test2",
                    "IsEnable": true,
                    "Priority": 0
                },
                {
                    "GDSName": "Type2_test3",//this is duplicate,keep this
                    "IsEnable": true,
                    "Priority": 0
                },
                {
                    "GDSName": "Type2_test3",//this is duplicate ,delete this
                    "IsEnable": true,
                    "Priority": 0
                }
            ]
        },
        {
            "Type": "Type3",
            "GDS": [
                {
                    "GDSName": "Type3_test3",
                    "IsEnable": true,
                    "Priority": 0
                }
            ]
        }
    ]
}

在这里

"Type": "Type2",
"GDS [2] & [3] of GDSName": "Type2_test3"

是重复的。

我需要确保如果 Type2_test3 存在并且它是多个，则通过保持每列 Type2_test3 单个条目来删除重复条目。

Answer 1

删除重复项的一种可能方法是以下步骤:

• 使用具有显式模式的 OPENJSON() 将输入 JSON 中的 Categories JSON 数组解析为表格
• 使用 FOR JSON AUTO 将不同的行输出为 JSON
• 使用 JSON_MODIFY() 将 Categories JSON 数组修改为新的 JSON:

当然，您至少需要 SQL Server 2016 才能使用内置的 JSON 支持。

JSON:

DECLARE @json nvarchar(max) = N'{
    "Categories": [
        {
            "Type": "Type1",
            "GDS": [
                {
                    "GDSName": "Type1_test1",
                    "IsEnable": true,
                    "Priority": 2
                },
                {
                    "GDSName": "Type1_test2",
                    "IsEnable": false,
                    "Priority": 0
                }
            ]
        },
        {
            "Type": "Type2",
            "GDS": [
                {
                    "GDSName": "Type2_test1",
                    "IsEnable": false,
                    "Priority": 0
                },
                {
                    "GDSName": "Type2_test2",
                    "IsEnable": true,
                    "Priority": 0
                },
                {
                    "GDSName": "Type2_test3",
                    "IsEnable": true,
                    "Priority": 0
                },
                {
                    "GDSName": "Type2_test3",
                    "IsEnable": true,
                    "Priority": 0
                }
            ]
        },
        {
            "Type": "Type3",
            "GDS": [
                {
                    "GDSName": "Type3_test3",
                    "IsEnable": true,
                    "Priority": 0
                }
            ]
        }
    ]
}'

声明:

CREATE TABLE Data (JsonColumn nvarchar(max))
INSERT INTO Data (JsonColumn) VALUES (@json)

UPDATE Data
SET JsonColumn = JSON_MODIFY(
   JsonColumn,
   '$.Categories',
   (
   SELECT j.[Type], a.GDS
   FROM OPENJSON(JsonColumn, '$.Categories') WITH (
      [Type] varchar(10) '$.Type',
      [GDS] nvarchar(max) '$.GDS' AS JSON
   ) j
   CROSS APPLY (
      SELECT DISTINCT GDSName, IsEnable, Priority
      FROM OPENJSON(j.GDS) WITH (
         GDSName varchar(50) '$.GDSName',
         IsEnable bit '$.IsEnable',
         Priority int '$.Priority'
      )
      FOR JSON PATH
   ) a (GDS)
   FOR JSON AUTO
   )
)

SELECT *
FROM Data