Format Query as JSON

JSON (JavaScript Object Notation) is a file format used to transmit data from various applications, very similar to XML, it also used to stored NoSQL unstructured data, and because of this versatility, Many REST applications and web services use it.

Since this is a standard file format, you should be able to generate data in JSON format from SQL Server.

Fortunately, since SQL Server 2016, there is a native way to do it, in this post I will show you how to do it.

Generate a simple JSON file

The most basic syntax to generate a JSON file is this:

SELECT <field list>
FROM <YourObject>
FOR JSON AUTO 

Using this simple test table with 10 records as an example:

SELECT TOP 10
 *
FROM [dbo].[MyTestTable]

If we use JSON AUTO on this sample data we will have the following output:

SELECT TOP 10
 *
FROM [dbo].[MyTestTable]
FOR JSON AUTO

Query executed

Part of the JSON AUTO output

Using dot syntax

For most of real-world applications, the JSON AUTO will not give you the control you could need over your file format, for having more control over it, you must use the JSON PATH option, along with the ROOT option as follows:

SELECT TOP 10
 id,
 dataVarchar,
 dataNumeric,
 dataInt,
 dataDate
FROM [dbo].[MyTestTable]
FOR JSON PATH, ROOT('TestTable')

This will generate the following output:

JSON PATH output

And if we want to group similar items (for example for queries with joins), just rename the fields as element.field as in this example:

SELECT TOP 10
 id,
 dataVarchar as [group1.D1],
 dataNumeric as [group1.D2],
 dataInt as [group2.E1],
 dataDate as [group2.E2]
FROM [dbo].[MyTestTable]
FOR JSON PATH, ROOT('TestTable')

Will generate the following output:

JSON PATH with grouped items

Of course, if you have SQL Server Operations Studio you can do it from the IDE:

If you want to learn more about the FOR JSON option, please read Microsoft official documentation here