JSON Support in SQL Server 2016

By: Daniel Calbimonte | Comments | Related: > SQL Server 2016

Problem

JSON (JavaScript Object Notation) is a data interchange format that has become popular when moving data between systems. There has not been any native support in SQL Server to output the data in this format, so other workarounds need to be put in place. This has changed with SQL Server 2016.

Solution

A new feature in SQL Server 2016 is native support for JSON. For this tip, I am using SQL Server 2016 CTP 2.2 and the Adventure Works 2014 database.

History of JSON

JSON stands for JavaScript Object Notation. It was created by Douglas Crockford, a famous computer programmer and entrepreneur.

JSON is replacing XML in many places to interchange data. It was designed first for JavaScript, but now it is an independent language. There are no plans to discontinue XML support in SQL Server, but adding JSON in SQL Server provides an alternative.

It is now supported in JavaScript, Perl, C++, ABAP, Ada, AdvPL, ASP, AWK, Bash, BlizMax, C, C#, Ciao, Clojure, Cobol, ColdFusion, Delphi, Java, Mathlab, Net.Data, Objective C, Photoshop, PHP, Python, Ruby, etc. and now in SQL Server 2016 and future versions.

JSON is easy to read in its raw state and also easy to parse for programming languages. It is very common in Ajax and is becoming more common in other programming languages.

JSON support in SQL Server 2016

In order to start, open SSMS and run the following query (make sure you are using SQL 2016 CTP 2.2 or later):

1. Let's start with a simple query in T-SQL without JSON.

SELECT 
[FileName]
,[Title]
,[ChangeNumber]
 FROM [Production].[Document]
GO

Check the results:

Now compare the results with the query using JSON AUTO.

SELECT 
[FileName]
,[Title]
,[ChangeNumber]
 FROM [Production].[Document]
FOR JSON AUTO 
GO

Note that in the query editor JSON is detected as an error, but the query still runs. This will be fixed in future releases.

Check the results of the query with the JSON AUTO clause:

As you can see, the result has the following syntax:

[{"Column 1 Name":"Column1 Value","Column 2 Name":"Column2 Value","Column 3 Name":"Column3 Value".....},{"Column 1 Name row2":"Column1 Value row2"}....]

You have the object name and next the value. It uses curly brackets { } to separate each row. The colons (:) are used to separate the object name from the value of the object. The coma (,) is used to separate the columns.

JSON PATH in SQL Server 2016

Another alternative is JSON PATH. AUTO is used to format the JSON results and uses the default order. If you are familiar with XML in SQL Server, you will notice that it is very similar. PATH offers more control in the format of the files.

SELECT 
[FileName]
,[Title]
,[ChangeNumber]
FROM [Production].[Document]
FOR JSON PATH
GO

Here is the output:

If you are not using tables, PATH is your only option. The below example will fail:

declare @val1 int=1, @val2 int =2
SELECT @val1 AS value1, @val2 as value2
FOR JSON AUTO

If you run the above query, you will receive the following error message:

Msg 13600, Level 16, State 1, Line 2 FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name.

However, if you run the same query with the PATH clause, it will work successfully.

declare @val1 int=1, @val2 int =2
SELECT @val1 AS value1, @val2 as value2
FOR JSON PATH
GO

The results displayed with be the following:

Using JSON with ROOT

You can also use the ROOT option as shown below.

declare @val1 int=1, @val2 int =2
SELECT @val1 AS value1, @val2 as value2
FOR JSON PATH, ROOT ('myRoot')
GO

The result are as follows:

Conclusion

SQL Server 2016 includes native support for JSON, but this is still in the works and we displayed what is currently supported.

The OPENJSON option is not included in CTP2.2, but it is planned in CTP3 and later versions.

Next Steps

For more information about JSON in SQL Server, check these links:

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips