Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

JSON Support in SQL Server 2016


By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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:

query 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.

JSON SQL Server syntax

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

JSON AUTO SQL Server

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:

JSON PATH SQL Server

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:

JSON PATH SQL Server

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:

JSON ROOT SQL Server

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:



Last Update:


signup button

next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools