SQL Server 2016 introduces support for JSON. There isn’t a JSON data type (like the XML data type), instead the JSON document is stored as a nvarchar type.
Examples:
These examples are run using SQL Server 2016 CTP 2.3
Sample Table
This script will create a sample Player table to use in an example.
Relational Data to JSON:
Forming a JSON document from a query is a lot like creating an XML document. We use the FOR JSON clause with either AUTO or PATH.
select JerseyNumber, FirstName, LastName, Position from dbo.Player where JerseyNumber = 2 for json auto;
returns
[{"JerseyNumber":2,"FirstName":"Matt","LastName":"Ryan","Position":"QB"}]
AUTO returns default formatting, where PATH allows the output to be shaped differently. For example, if we wanted to nest the player’s name in the output.
select JerseyNumber, FirstName as [Name.FirstName], LastName as [Name.LastName], Position from dbo.Player where JerseyNumber = 2 for json path;
returns
[{"JerseyNumber":2,"Name":{"FirstName":"Matt","LastName":"Ryan"},"Position":"QB"}]
Validating JSON:
One disadvantage with using the NVARCHAR type is that any type of text can be stored, including invalid JSON. According to MSDN an ISJSON function will be added to a future CTP that will validate that a string is a valid JSON document.
Future editions will also include functions to extract data from specified JSON nodes.
More Information:
MSDN – JSON Support
FOR JSON
