SQL Server 2016 – JSON Support

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

Advertisements

One Response to SQL Server 2016 – JSON Support

  1. […] I’ll follow up in the coming weeks with more detailed posts on some of these features. SQL Server 2016 – Temporal Tables SQL Server 2016 – Data Masking Row Level Security JSON Support […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: