How can I delete an item from JSON array in SQL Server.

Here is my JSON :

[
  {
    "nodeId": 15,
    "nodeCondition": "needRepairing=true"
  },
  {
    "nodeId": 16,
    "nodeCondition": "needWashing=false"
  }
]

which is stored in a column. I want to delete elements by their nodeId.

CodePudding user response：

I don't think you can delete an item from JSON array, so you need to parse, filter and rebuild the JSON array again.

Table:

SELECT *
INTO JsonTable
FROM (VALUES
   (N'[
      {"nodeId":13,"nodeCondition":"needRepairing=true"},
      {"nodeId":14,"nodeCondition":"needRepairing=true"},
      {"nodeId":15,"nodeCondition":"needRepairing=true"},
      {"nodeId":16,"nodeCondition":"needWashing=false"}
      ]')
) v (JsonColumn)

Statement:

UPDATE JsonTable
SET JsonColumn = (
   SELECT nodeId, nodeCondition
   FROM OPENJSON(JsonColumn) WITH (
      nodeId int '$.nodeId', 
      nodeCondition nvarchar(1000) '$.nodeCondition'
   )
   WHERE nodeId NOT IN (13, 15)
   FOR JSON PATH
)

Result:

Note, that in case of JSON object, you can delete a specific key with JSON_MODIFY(), using lax mode and NULL as new value.

CodePudding user response：

for example , if you want to remove nodeId 16 then you could try this :

DECLARE @YourJson NVARCHAR(MAX) = N'[
{"nodeId":15,"nodeCondition":"needRepairing=true"},
{"nodeId":16,"nodeCondition":"needWashing=false"}
]';
SELECT * FROM (SELECT * FROM OPENJSON(@YourJson) WITH(nodeId 
INT,nodeCondition NVARCHAR(MAX)) WHERE nodeId <> 16) AS R FOR JSON 
AUTO;
GO