The importance of JSON data type in our lives continues to increase day by day. We will examine the functions that SQL Server provides us for the JSON data type in this article.
There are 4 function for JSON in SQL Server;
- ISJSON
- JSON_VALUE
- JSON_QUERY
- JSON_MODIFY
ISJSON Function in SQL Server(TSQL):
This functions checks the parameter, and if the parameter is a valid JSON it returns 1. If it is not valid JSON it returns 0.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @jsoncheck NVARCHAR(MAX); SET @jsoncheck = '{"info":{"address":[{"town":"Istanbul"},{"town":"Ankara"}]}}'; IF (ISJSON(@jsoncheck) > 0) BEGIN print 'Yes, @jsoncheck value is a valid JSON' END ELSE BEGIN print 'No, @jsoncheck value is not a valid JSON' END |
JSON_VALUE and JSON_QUERY Functions in SQL Server(TSQL):
JSON_VALUE extracts a scalar value from the JSON and JSON_QUERY extracts an object or an array from the JSON.
The last line of the SELECT Clause returns NULL value. Because JSON_VALUE returns only scalar value.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @json_data NVARCHAR(4000) SET @json_data=N'{ "Person": { "Name": "Nurullah CAKIR", "Friends": ["Faruk ERDEM", "Hakan GURBASLAR", "Ogun OZALP"] } }' SELECT JSON_VALUE(@json_data,'$.Person.Name') AS 'Name', JSON_QUERY(@json_data,'$.Person.Friends') AS 'Friends', JSON_VALUE(@json_data,'$.Person.Friends[2]') AS 'Last Friend', JSON_VALUE(@json_data,'$.Person.Friends') AS 'Friends'; |
JSON_MODIFY Function in SQL Server(TSQL):
We can update a property of a JSON using JSON_MODIFY function. It returns updated JSON.
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @json_data NVARCHAR(4000) SET @json_data=N'{ "Person": { "Name": "Nurullah CAKIR", "Friends": ["Faruk ERDEM", "Hakan GURBASLAR", "Ogun OZALP"] } }' SET @json_data=JSON_MODIFY(@json_data,'$.Person.Name','Faruk ERDEM') print @json_data |
