June 1st 2016 is (or at least should be) the launch of the release version latest edition of Microsoft SQL Server namely SQL Server 2016. This article is about one of the many new feature in the new version.

You surely have heard about JSON which stands for JavaScript Object Notation.

Wikipedia defines JSON it like this: “An open-standard format that uses human-readable text to transmit data objects consisting of attribute–value pairs. It is the most common data format used for asynchronous browser/server communication (AJAJ), largely replacing XML which is used by AJAX.”.

Even if you are not doing JavaScript, Ajax or Web, chances are that you will find JSON on your way. Yes, it was created as a XML replacement for those technologies but nothing is preventing us from using it in any project.

What’s wrong with XML? I find it too verbose. JSON is much more compact yet still readable for the most part.

Downloadable code

This month code is limited to a SQL script that you can try against a SQL Server 2016 instance.

Notice that the scripts have been created using the release candidate 2 (RC2) version prior to the release of the release to market (RTM) version.

Links to older post

It is not the first time I post about JSON.

In January 2014, I have published an article on the Json.Net library used strictly in .Net (http://emoreau.com/Entries/Articles/2014/01/Using-JsonNet.aspx).

I have also provided links to useful online tools:

• JSON Editor Online. As the name says, this site offers an editor of Json to a structure or from a structure to Json. You can edit both side and regenerate the other one. Really helpful.
• json2csharp. This website accepts a JSON string and generates a C# class from it in one click.
• If you liked the previous web site but would prefer VB over C#, you can navigate to the JSON C# Class Generator which apparently lets you create VB.Net classes.

Why JSON in the database?

Why not? JSON is quickly replacing XML. SQL Server already supports XML and JSON became the favorite mechanism for serialization in applications so why not have the database natively supports JSON.

Not a datatype

As shown in figure 1, the implementation that the SQL Server team made of JSON is not a datatype like they did a couple of years ago when they implemented XML. And this is a good thing for a couple of reason.

First, if you already started storing JSON in varchar columns in your databases, you are ready to move on to the next step.

Also, we won’t have to wait for updates of development tools like ADO.Net and Entity Framework to benefit from JSON. So if your queries run on a SQL Server 2016, you can start using JSON without any kind of data conversion.

Figure 1: JSON is not a SQL data type

Testing some queries

To start testing the JSON parser, we need a string. I used the string found on Wikipedia which I have expanded a bit to demonstrate some more features. Because, no special datatype is required, you can use varchar/nvarchar variable like this:

DECLARE @json VARCHAR(max)
SET @json = '{
  "firstName": "John",
  "lastName": "Smith",
  "isAlive": true,
  "age": 25,
  "address": {
    "streetAddress": "21 2nd Street",
    "city": "New York",
    "state": "NY",
    "postalCode": "10021-3100"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    },
    {
      "type": "mobile",
      "number": "123 456-7890"
    }
  ],
  "children": ["Joe","Jack","Willam","Averell"],
  "spouse": null
}
'

Now that we have that string, the first thing is to validate if the parser can do its job on it. It is very easy because we have a function to test it:

SELECT ISJSON(@json)

That function will return 1 if the string is a valid JSON string. Otherwise, 0 is returned.

Using JSON_VALUE

The first method to parse a JSON string will return the value from a path. The JSON_VALUE returns a scalar value (of type varchar or nvarchar). It is useful to return a single value. Here are some examples (don’t forget that the path is case-sensitive):

SELECT JSON_VALUE(@json, '$.firstName') AS FirstName
, JSON_VALUE(@json, '$.address.city') AS City
, JSON_VALUE(@json, '$.children[0]') AS FirstChild
, JSON_VALUE(@json, '$.phoneNumbers[0].number') AS FirstPhoneNumberThis will give you these results shown in figure 2.

Figure 2: Results of the previous query:

Using JSON_QUERY

The previous query works well with scalar values. But if you try the following query, the results will be NULL.

SELECT JSON_VALUE(@json, '$.phoneNumbers[0]') AS FirstPhoneNumberNotWorkng
, JSON_VALUE(@json, '$.children') AS ChildrenNotWorking

This is because the values to be returned are not simple strings.

If what you need to retrieve is not a scalar value like the previous example but rather an array of values or another JSON string, you need to use the JSON_QUERY function like this:

SELECT JSON_QUERY(@json, '$.phoneNumbers[0]') AS FirstPhoneNumberWorking
, JSON_QUERY(@json, '$.children') AS ChildrenWorking

Figure 3: Results of JSON_QUERY

Using OPENJSON

Still if what you are handling is an array, you can use the OPENJSON function that will return either a key-value pair resultset as shown by this first query.

SELECT * FROM OPENJSON(@json, '$.address')
SELECT * FROM OPENJSON(@json, '$.children')

Figure 4: Result of the default OPENJSON

You can also select the columns for more complex objects like this:

SELECT * 
FROM OPENJSON(@json, '$.phoneNumbers') 
WITH (
	type VARCHAR(50) '$.type', 
	number VARCHAR(50) '$.number'
)

Figure 5: Result for the typed OPENJSON

Formatting a resultset as JSON

The previous examples all showed how to parse a JSON string.

But what if you need to produce a JSON string from one of your table? It couldn’t be much simpler. The SQL team just added the “FOR JSON PATH” as a clause to the SELECT command. An example of a query hitting the sys.objects table reads like this:

SELECT TOP 10 name, object_id, schema_id, type_desc
FROM sys.objects FOR JSON PATH

This query will return a JSON formatted string.

What if you can’t upgrade to SQL 2016?

Many companies, not to say most, won’t upgrade that important part of the system being the database server. So chances are that you won’t be able to use native JSON from the database just yet.

There are some workarounds to this like Phil Factor’s famous parseJSON UDF.

Conclusion

The addition of a JSON parser/generator in SQL Server 2016 is a great addition to this latest edition.

This article as scratch the surface of the parsing of JSON offered by SQL Server 2016 and showed how to quickly generate a JSON string from your data easily.

I really hope you will be able to upgrade your server to be able to use this feature soon.