SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

SQL Guatemala

SQL Server Database Administrator/Developer with experience in large environments. I have worked on IT for nearly 15 years in different areas. Microsoft Certified Solutions Associate: SQL 2016 Database Administration since 2018. Microsoft Certified Solutions Associate: SQL Server 2012/2014 since 2017. Microsoft Certified Professional since 2014.

Comments

Leave a comment on the original post [www.sqlguatemala.com, opens in a new window]

Loading comments...