JSON response from a SQL Query in SQL Server 2016

JSON is supported in SQL Server and it is very easy to obtain JSON formatted response from a query easily. For example,

while connected to Northwind database you get the JSON formatted data by running a query such as this one using the Products table:

---------------------------------

SELECT        ProductName, QuantityPerUnit, UnitPrice
FROM            Products FOR JSON Auto;

---------------------------------

The response will be as shown:

JSON_0

You could also return a single row of data as shown by running this query:

-----------------------

SELECT [ProductID]
      ,[ProductName] 
FROM [Northwind].[dbo].[Products]
WHERE ProductID=5
For JSON AUTO

-------------------------

JSON_1

The result comes in an array even if the returned data is just one row.

You could remove the array wrapper ([ ]) surrounding the result set by the following query:

-------------------

SELECT [ProductID]
      ,[ProductName] 
FROM [Northwind].[dbo].[Products]
For JSON AUTO, WITHOUT_ARRAY_WRAPPER

---------------------------------------

JSON_2

Notice that the array wrapper is gone in the result set.

You can further qualify where the data (Which table, for example) by adding a 'root' element by issuing this query:

------------------------

SELECT [ProductID]
      ,[ProductName] 
FROM [Northwind].[dbo].[Products]
For JSON AUTO, Root('Products')

------------------------

JSON_3

You are not allowed to use WITHOUT_ARRAY_WRAPPER and Root in the same sql query because you get the following message, if you do:

Msg 13620, Level 16, State 1, Line 5
ROOT option and WITHOUT_ARRAY_WRAPPER option cannot be used together in FOR JSON. Remove one of these options.

I am using SQL Server 2016 Developer's edition on my Windows 10 Pro laptop.

If you are new to JSON read the following:

http://hodentekhelp.blogspot.com/2014/11/how-do-you-work-with-javascript-object.html

If you are motivated to learn there are more here:

http://hodentekhelp.blogspot.com/search?q=json