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?
-
2You can parse the JSON and then filter it using WHERE Clause , DECLARE @JSON VARCHAR(2000)='["1","2","3","4"]' SELECT * FROM OPENJSON(@JSON) WHERE Value NOT IN (1,2)Srinivasan Rajasekaran– Srinivasan Rajasekaran2022-10-10 06:17:27 +00:00Commented Oct 10, 2022 at 6:17
-
What is your SQL Server version?Zhorov– Zhorov2022-10-10 06:21:45 +00:00Commented Oct 10, 2022 at 6:21
-
@Zhorov hi, it is 2019behzad– behzad2022-10-10 08:15:06 +00:00Commented Oct 10, 2022 at 8:15
Add a comment
|
1 Answer
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
