Delete Duplicate JSON nodes from SQL Server

Question

I want to delete duplicate nodes from JSON in a column of a SQL Server table.

JSON column:

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

Here in

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

are duplicates.

I need to make sure If Type2_test3 is present and it is multiple then delete duplicate entries by keeping Type2_test3 single entry per column.

user_6578080 · Answer

One possible approach to remove the duplicates are the following steps:

Parse the Categories JSON arrays from the input JSON as table using OPENJSON() with explicit schema
Output the distinct rows as JSON using FOR JSON AUTO
Modify the Categories JSON arrays with the new JSON using JSON_MODIFY():

Of course, you need at least SQL Server 2016 to use the build-in JSON support.

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
                }
            ]
        }
    ]
}'

Statement:

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

Delete Duplicate JSON nodes from SQL Server

1 Answers