ISJSON() – Verifies that the input text has valid JSON data
JSON_VALUE() – Extracts scalar value from the JSON text.
JSON_QUERY() – Extracts an object or array from the JSON text.
However there might be scenarios where you might need to manipulate/modify the JSON string. In SQL Server 2016, there is a new function named JSON_MODIFY which will assist you to modify the value of a property in the JSON string and return the modified string.
Let us now see a demonstration of the JSON_MODIFY() function –
|— JSON_MODIFY Demonstration to update the value of a property in JSON string|
|DECLARE @json nvarchar(max)|
|SET @json =N'|
|IF (ISJSON(@json) = 1)|
|PRINT 'It is a Valid JSON' –Valid JSON|
|PRINT 'It is an Invalid JSON string'|
|PRINT JSON_MODIFY (@json, '$.Person.Address.Zip' , '35043');|
When you execute the query above, you would find that the JSON_MODIFY function will successfully modify the Zip field of the Person object from 24153 to 35043 —
With this blog post, I have completed writing about the 4 new JSON functions introduced in SQL Server 2016.
Stay tuned to learn about more features of SQL Server 2016 and how it has been nothing less than a revelation in itself.