Working with JSON 

JSON, or JavaScript Object Notation, is a popular format these days for exchanging data among various endpoints. The most common usage of JSON is on mobile and web services. JSON is also used to store data for NoSQL databases, such as the Azure Cosmos DB. While it might seem that, with NoSQL and JSON, we work with unstructured data, only it's actually not the case. The data has a structure, only it's schema agnostic and the storage schema is defined by JSON itself, based on the content.

SQL Server has supported working with JSON since SQL Server 2016. Unlike XML, however, JSON is not the native data type in SQL Server. You can, however, use many SQL Server functions and operators to work with JSON text and perform the following:

• Parse JSON text, and read or modify values
• Transform arrays of JSON objects into a table format
• Run any Transact-SQL query on the converted JSON objects
• Format the results of Transact-SQL queries in JSON format