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.

Answer 1

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