Firstly I stored my JSON in a varchar column, now I got a requirement to change on of the fields in the JSON. How to retrieve the field and update it?
Example of one record
{ "name": "Bob", isSelected: "true" }
True needs to be changed to false
-
Does your JSON data really have smart quotes in it? SQL Server won't be able to parse that.Thom A– Thom A ♦2022-05-26 09:45:06 +00:00Commented May 26, 2022 at 9:45
-
Yes only for true false it doesn’t havemadineni sravani– madineni sravani2022-05-26 09:48:59 +00:00Commented May 26, 2022 at 9:48
Add a comment
|
1 Answer
If your JSON is properly formatted (it's not in your question as isSelected isn't quoted), then you use can JSON_MODIFY:
DECLARE @json nvarchar(MAX) = N'{"name": "Bob", "isSelected":"true" }';
SELECT JSON_MODIFY(@json, '$.isSelected', 'false');
If it isn't properly formatted, fix your process that creates the JSON first, to create well formed JSON, and then use JSON_MODIFY.
4 Comments
madineni sravani
It is showing json_modify is not recognised as built in function
Thom A
What version of SQL Server are you using, @madinenisravani ?
JSON_MODIFY is available in all fully supported versions of SQL Server and 2016. If you are getting that error, that likely means you're using a much older version of SQL Server; this is something you need to tell us as otherwise it will be assumed you are using (fully) supported software. If you are using SQL Server 2014 or prior I suggest that you either upgrade, or you do this outside of SQL Server, as 2014 and prior have no JSON support.madineni sravani
Yes we are using older version Is there anyother option to get by using regex
Thom A
T-SQL has no support for Regex, @madinenisravani . As I mentioned, if you're using a not (fully) supported of SQL Server, then your options are really either: 1. Upgrade. 2. Parse the JSON outside of SQL Server.