Saturday, June 11, 2016

Exporting data from SQL Server 2016 as JSON string?

JSON support was long due and finally it was addressed in SQL Server 2016. If you are new to JSON review this article first.

Formatting and exporting data was the feature first introduced in SQL Server 2016 CTP2.

Microsoft introduced the FOR JSON AUTO clause. This clause automatically formats in JSON based on the order of the columns from tables. There is no need to specify a path or use nested sub-queries.

Here is a simple example from the AdventureWorks2014 OLTP database in SQL Server 2016. If you do not have this database you can install it as shown here.

If you do not have SQL Server 2016 follow this link to install.

The following query returns 10 rows from the Employee table of HumanResources.


JSON_00

For returning the JSON Formatted data all you need to do is to append the FOR JSON AUTO clause to the above query as shown here:

JSON_01

The response is rearranged in the queries response pane as shown:

JSON_02 

Read this JSON article with a twist.