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