SQL Server 2016 JSON Support for Database Developers

JSON stands for JavaScript Object Notation and it is an XML like format for storing and transferring data especially popular in mobile and web applications.
It is compact when compared to XML data format. JSON is also language independent.
An other big advantage of JSON data format ove XML is while parsing XML data requires a seperate parser for XML formatted data, it is possible to parse JSON data using a standard Javascript function.

Query Out SQL Data using JSON in SQL Server 2016

JSON support of SQL Server 2016 introduces developers with SQL commands which has similar syntax to SQL XML commands.
For example, database developer can query data in database tables using FOR JSON AUTO and FOR JSON PATH options in SQL SELECT queries.

Developers can format the JSON output structure using root keys just like we did for XML outputs up to now in SQL Server data platform.

For the rest of this SQL tutorial, developers can test the resultant JSON output on an online JSON validator on the web like JSONLint or JSON Formatter

Let's now use the new SQL syntax FOR JSON AUTO introduced with SQL Server 2016 for developers.

select
name, type_desc
from sys.tables
for json auto

Perhaps it is better to set the query results pane for text mode and resize the available text size of an output line on the query results screen before executing the SELECT query for JSON formatted output.

On Query Options screen, drill-down Results node and click on Text
On the right, clear the option "Include column headers in the result set"
Additionally to see longer text output as the result of the SELECT query, set the "Maximum number of characters displayed in each column:" to 8192 for example.
Click on OK

Now execute the JSON Select statement given above where "FOR JSON AUTO" is added at the end of the query.

SQL Server 2016 FOR JSON AUTO Select command option

I just formatted manually the output text to let the develoeprs visualize how JSON data is formed by SQL Server 2016 database engine.

Each table row is surrounded between "{" and "}" characters.
Row data in JSON format is formed of key-value pairs mapping to table column and column data in SQL data.
Each column and column data pair is seperated by "," (comma) character from an other pair.
Column name and column values are seperated by ":" and surrounded with ' " ' characters as seen in above screenshot.

The JSON output format slightly changes if the SQL Server developer uses JOIN's between database tables.
For example following SQL Select statement joins sys.tables and sys.schemas system views and outputs results to text in JSON format.

The column-value pairs which are from the main table which is right after FROM clause are formatted as before.
On the other hand, for the secondary tables which are joined using INNER JOIN etc and aliased as in this example values are in an inner structure.

Although it is possible to format the text results of the SQL Select query with FOR JSON AUTO, the formatting capabilities and control of the developer on the JSON data is limited when compared with FOR JSON PATH option.
Let's move to next section to see how For JSON Path works

For JSON Path in SQL Server 2016 for Output Data Formatting

Start with updating the inner join query we used previously but this time by replacing FOR JSON AUTO with FOR JSON PATH without ROOT option for now.

Now execute any of the above SQL Select queries for data output in JSON format, either FOR JSON AUTO or FOR JSON PATH syntax.

select * from JSONSource for json auto
-- or
select * from JSONSource for json path

Both queries will result with the same JSON output in this case

As seen above, the second row which the "name" column value is NULL is displayed without "name" column in the JSON formatted text.{"id":2,"code":"CODE"},

On the other hand, the last column which has the empty string value in the "name" column is displayed with its all columns but the "name" has empty string as "" in JSON output data.{"id":4,"code":"TSQL","name":""}

Omitting NULL value columns in the output JSON data might cause a misunderstanding that NULL column does not exist, or it was not selected for data transfer.
If you want to prevent this, you can use and additional option INCLUDE_NULL_VALUES with the FOR JSON AUTO/PATH command as in the following syntax

Do you see, the NULL column value is displayed as "name":null after INCLUDE_NULL_VALUES option is used after FOR JSON PATH/AUTO SELECT command.

To summarize, SQL Server 2016 provides a means for developers to ease their tasks for generating JSON data for their mobile or web applications.
The new SQL SELECT syntax enhancements for Transact-SQL programmers enable them to format query resultset in the JSON format by using the JSON FOR AUTO or JSON FOR PATH with INCLUDE_NULL_VALUES and ROOT('') extensions.

I hope this SQL Server 2016 T-SQL tutorial is useful for demonstrating the JSON support provided by SQL Server data platform for developers.