CODEWITHSUNDEEP
jsonsql-server
behzad
behzad

Reputation: 196

Remove params from JSON in SQL Server

There is a JSON column in SQL Server tables with data like:

["1","2","3","4"]

and I want to delete "3" or ("2","4") (for example) from it.

Can I do it with Json_Modify or anything else?

Upvotes: 0

Views: 78

Answers (1)

Meyssam Toluie
Meyssam Toluie

Reputation: 1071

JSON modify can modify by PATH if you have not any key to modify and just a simple list like that you can do this:

DECLARE @JsonList NVARCHAR(1000) = N'["1","2","3","4"]';
DECLARE @NewList NVARCHAR(1000);

SET @NewList =
(
    SELECT CONCAT('[', STRING_AGG(CONCAT('"', oj.Value, '"'), ','), ']')
    FROM OPENJSON(@JsonList) AS oj
    WHERE oj.Value NOT IN ( 2, 4 )
);

PRINT @NewList

Upvotes: 2

Related Questions