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.
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:
| JsonColumn |
|---|
| [{"nodeId":14,"nodeCondition":"needRepairing=true"},{"nodeId":16,"nodeCondition":"needWashing=false"}] |
Note, that in case of JSON object, you can delete a specific key with JSON_MODIFY(), using lax mode and NULL as new value.
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
nodeIdandnodeConditionkay\value pairs?