从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_test三单个条目来删除重复条目。

Answer 1

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

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

当然，您至少需要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