JSON data is a textual data format used to exchange data in modern WEB/Mobile applications.

JSON can also be used to store unstructured data in log files or NoQL databases such as Microsoft Azure Cosmos DB.

Many REST businesses will return results in JSON text format or receive input as JSON format.

Through SQL Server’s built-in functions, we can:

• Parse JSON text, edit value.
• Convert arrays in JSON objects to table format.
• Run a T-SQL Query with the converted JSON object.
• Format the output of the T-SQL statement in JSON format.

Some built-in functions that handle JSON:

• ISJSON: check the validity of a JSON string.
• JSON_VALUE: output a single value from a JSON string.
• JSON_QUERY: output object or array from JSON string
• JSON_MODIFY: change JSON string value

Modify JSON value

To edit the element of the JSON text, the JSON_MODIFY function is used to update the value of the property in the JSOn string and return the updated JSON string.

For example:

DECLARE @json nvarchar(max); set @json = '{"info" : {"address": [{"town": "Belgrade"}, {"town" : "Paris"}, {"town": "Marid"}]}}'; SET @json = JSON_MODIFY(@json, '$.info.address[1].town' , 'London'); select modifiedJson = @json

OPENJSON to convert JSON to row and column data

For example:

declare @json nvarchar(max); set @json = N'[ {"id": 2, "info": {"name": "john","surname": "smith"},"age": 25}, {"id": 5, "info": {"Jane": "john","surname": "smith"},"dob": "2005-11-04T12:00:00"} ]'; select * from OPENJSON(@json) with( id INT 'strict $.id', firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname', age INT, dateofBirth datetime '$.dob' )

Export SQL Server data to JSON

For example:

use AdventureWorks2019; SELECT BusinessEntityID, FirstName as "info.name" , LastName as "info.surname", ModifiedDate as dob from Person.Person for json PATH