JavaScript Object Notation Support in SQL Server 2016

18 Jul 2016

“JavaScript Object Notation is a way to store a human-readable collection of data that we can access in a logical manner”.

JSON is used for exchanging data in modern web and mobile applications. In addition, JSON is used for storing unstructured data in NoSQL databases like Microsoft Azure DocumentDB. It is the main format for exchanging data between web pages and web servers using AJAX calls. JSON is currently one of the most commonly used data exchange formats.

Some tools and products that work with JSON

SQL Server support for an XML type arrived in SQL Server 2005 however a popular request on the Microsoft Connect site has been for JSON support to be added to SQL Server to cater for newer applications. The good news is that this will be included with SQL Server 2016 … well, kind of. In this release of SQL Server JSON will actually be represented and stored as NVARCHAR but we can use it like JSON.

Microsoft offers 3 reasons for this:

Migration - catering for those already storing JSON as text.

Compatibility - NVARCHAR is supported everywhere so JSON will be, right?

Client Side Support - while we don’t have a standardized JSON object type in client apps. In C#.Net, a lot of developers use JSON.Net parser with built in JObject or JArray type; however it is not a standard. That said it is entirely possible to use your own JSON type that can be implemented using the CLR and JSON.NET.

OK, so what can we do with this in SQL Server?

In short we can take relational data and export it in a JSON format, we can import JSON as relational data into tables and we can run a few functions along the way.

Importing JSON (as relational data)

The all-important Table Valued Function (TVF) to enable this is OPENJSON. It sounds a bit like OPENXML and, well, it is. An important difference however, is that OPENJSON works directly with JSON text without the need for a sp_xml_preparedocument equivalent. Using OPENJSON instead of parsing JSON at the client side you can send JSON as JSON and parse it at the database layer.

DECLARE @teams NVARCHAR(1000)

SET @teams =

N'{“aflteams”:

[

{“teamname”: “Hawthorn Football Club”

,”founded”: 1902, “premierships”: 13

,”homeground”: “Glenferrie Oval”},

{“teamname”: “Collingwood Football Club”

,”founded”: 1892, “premierships”: 15

,”homeground”: “Victoria Park”}

]}'

SELECT *

FROM OPENJSON(@teams,’$.aflteams’)

 WITH (Team NVARCHAR(30) ‘$.teamname'

, YearFounded NVARCHAR(4) '$.founded'

, PremiershipsWon TINYINT '$.premierships’

, HomeGround NVARCHAR(30) ‘$.homeground’)

Exporting JSON (from relational structures)

SELECT Team AS team, YearFounded AS founded

, PremiershipsWon AS premierships

,HomeGround AS homeground

FROM tbl_AFLTeams

FOR JSON PATH

As with the ‘FOR XML’ options, ‘PATH’ allows you to define the structure of the JSON output and ‘AUTO’ automatically creates nested JSON sub-arrays albeit with less control over how that output looks.

JSON related functions

Inevitably at the end of the AFL season the “premierships” value for “teamname” : ”Hawthorn Football Club” will need to be adjusted (most probably after a motivational talk by JSON Dunstall…sorry). This will be done using the JSON_MODIFY() function

Here is an example of a change.

SET @teams =

     JSON_MODIFY(@teams, '$.aflteams[1].premierships',14)

We can check JSON compatibility using ISJSON(). Scalar values can be extracted using JSON_VALUE() and JSON_QUERY() can be used to extract and object or an array.

So there we have it, JSON support in SQL Server 2016. Go on, give it a try.

If you are thinking of upskilling in SQL Server 2016, click here for more information.