SQL Server 2016 JSON Support for Database Developers

SQL Server 2016 introduce JSON support for SQL Server developers by extending SQL SELECT queries with FOR JSON AUTO and FOR JSON PATH options.

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.

SQL Server JSON Support for Database Developers

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

SQL Server 2016 For JSON Auto to Format Query Data

Let's start with query SQL database table data using classic SQL SELECT statement. This query will return data in tabular format.


select name, type_desc from sys.tables

Code

sample database table data
Query SQL table data using SELECT statement

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

Code

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.

Right click on the SQL Server Management Studio query editor window.
From the context menu choose "Results to > Results to Text Ctrl+T" menu option.

SQL Server Management Studio output query results to text

And then again on the same context menu choose "Query Options..."

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 SELECT query with For JSON Auto option
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.


select

 t.name, t.type_desc, s.name

from sys.tables t

inner join sys.schemas s on t.schema_id = s.schema_id

for json auto

Code

SQL Server 2016 JSON support for database programmer
SQL query with INNER JOIN output to 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.

For example, if I change the above query as below...


select top 2

 [table].name, [table].type_desc, [schema].name, [schema].principal_id

from sys.tables [table]

inner join sys.schemas [schema] on [table].schema_id = [schema].schema_id

for json auto

Code

Formatted JSON output of the query on SQL Server Management Studio Query Editor is as follows

Inner Join with For JSON Auto on SQL Server 2016

The addition of the ROOT option makes the returned JSON data more meaningful like a root note as follows.


select top 2

 [table].name, [table].type_desc, [schema].name, [schema].principal_id

from sys.tables [table]

inner join sys.schemas [schema] on [table].schema_id = [schema].schema_id

for json auto, root('DatabaseTables')

Code

Transact-SQL JSON support with ROOT option on SELECT queries

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.


select top 1

 t.name, t.type_desc, s.name

from sys.tables t

inner join sys.schemas s on t.schema_id = s.schema_id

for json path

Code

This will return an error because we have two "name" columns in the select list. But T-SQL programmers will remember FOR JSON AUTO was able to handle this case in our previous section.

Msg 13601, Level 16, State 1, Line 30
Property 'name' is invalid due to a conflict with another column name or alias.

Now modify the above query as follows


select top 2

 t.name as [table.name],

 t.type_desc,

 s.name as [schema.name],

 s.principal_id

from sys.tables t

inner join sys.schemas s on t.schema_id = s.schema_id

for json path

Code

As seen in below query result, the first "name" field is marked with "table" before, and the second "name" field is marked with "schema" in front of it.

output data formatting with For Json Path on SQL Server 2016
SQL Server support for data output in JSON format using FOR JSON PATH in SQL Server 2016

You can also add the ROOT('root_node_name') option at the end of the SQL JSON Select query. It will run successfully now.

If the above SQL query is updated in the select list for the alias of principal_id column, the generated JSON data as the result of the query execution will be different.


select top 1

 t.name as [table.name],

 t.type_desc as [schema.type_desc],

 s.name as [schema.name],

 s.principal_id as [schema.principal_id]

from sys.tables t

inner join sys.schemas s on t.schema_id = s.schema_id

for json path

Code

The columns with aliased virtually from same table will be grouped together. Even the columns belong to different tables the alias name defines the final JSON data format.

for JSON path to format JSON output on SQL Server

This shows that FOR JSON PATH provides a more flexibility for the T-SQL developer to generate a desired JSON data format

SQL developer can define the sorting criteria of the data by using ORDER BY clause before the FOR JSON AUTO/PATH

NULL Values in SQL Server JSON Queries

NULL values in table columns have a signaficant effect on the JSON output. A column with NULL value is not displayed in the JSON output of that row data.

To compare and understand NULL values and empty string values in the JSON output data, let's create our sample database table. Then populate it with sample data.


create table JSONSource (id int identity(1,1), code varchar(4), name nvarchar(100))

insert into JSONSource (code,name) values ('SQL','SQL Server'),('CODE',NULL)

insert into JSONSource (code,name) values ('JSON','JavaScript Object Notation'),('TSQL','')

Code

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

Code

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

SQL Server for json auto and for json path

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


select * from JSONSource for json auto, include_null_values

-- or

select * from JSONSource for json path, include_null_values

Code

The output of the above SQL Server JSON query will change into following format this time.

[
{"id":1,"code":"SQL","name":"SQL Server"},
{"id":2,"code":"CODE","name":null},
{"id":3,"code":"JSON","name":"JavaScript Object Notation"},
{"id":4,"code":"TSQL","name":""}
]

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.