By: Joe Gavin | Updated: 2024-07-25 | Comments | Related: > Functions System
Problem
JavaScript Object Notation (JSON) is a lightweight format for data interchange. It's text-based, easily readable by humans, and is programming language-independent. Therefore, it has interoperability with other programming languages, frameworks, and systems that can easily read and write JSON, allowing for data interchange between different systems. JSON can be directly consumed by web applications, send and receive data through Application Programming Interfaces (API), and migrate data between Relation Database Management Systems (RDBMS) and NoSQL database systems like MongoDB, Apache Cassandra, Redis, etc. JSON is frequently used for configuration files due to its readability and ease of use, allowing configurations based on database query results that can be dynamically built. SQL query results to JSON can be easily imported into data analysis and visualization tools. This shows us that exporting data from a SQL Server database in JSON format would be useful.
Solution
It's easy to export data from SQL Server using the FOR clause with the JSON option. We'll walk through some examples of using the following modes:
- AUTO
- PATH
Then, we'll go through examples using the following options:
- ROOT
- INCLUDE_NULL_VALUES
- WITHOUT_ARRAY_WRAPPER
FOR JSON Syntax
FOR JSON { { AUTO | PATH } [ [ , ROOT [ ( 'RootName' ) ] ] [ , INCLUDE_NULL_VALUES ] [ , WITHOUT_ARRAY_WRAPPER ] ] }
Example JSON Output
The examples were created using:
- Azure Data Studio (ADS) version 1.48.0 - SQL Server Management Studio (SSMS) will output JSON in one long unformatted string, and ADS will give you formatted text
- SQL Server 2022 Developer Edition
- WideWorldImporters sample database
JSON Output to Grid
Let's start by querying the Application.Cities table in the WorldWideImporters database.
/* Output to Grid*/ SELECT TOP 2 [CityID] AS [Application.CityID], [CityName] AS [Application.City] FROM [WideWorldImporters].[Application].[Cities] ORDER BY [CityName]; GO
As expected, the results are returned to us in the familiar default grid format.
![Output as Grid](/tipimages2/8043_string-to-json-sql-server-1.png)
FOR JSON AUTO
The FOR JSON clause requires that either the AUTO or PATH mode be specified. We'll start with AUTO. When you specify the AUTO mode, the format of the JSON output is automatically determined based on the structure of the SELECT statement. This format cannot be changed.
/* FOR JSON AUTO */ SELECT TOP 2 [CityID] AS [Application.CityID], [CityName] AS [Application.City] FROM [WideWorldImporters].[Application].[Cities] ORDER BY [CityName] FOR JSON AUTO; GO
As you can see, the results are on one line. This is where you'll be glad you're not using SSMS. Click on the results to open the formatted JSON in another window.
![FOR JSON AUTO Unformatted](/tipimages2/8043_string-to-json-sql-server-2.png)
Here is the formatted JSON. The field aliases are returned in the order as they are ordered in the SELECT statement (Application.CityID, then Application.City).
![FOR JSON AUTO - One Table](/tipimages2/8043_string-to-json-sql-server-3.png)
Next, join the Application.StateProvinces table.
/* FOR JSON AUTO */ SELECT TOP 2 [c].[CityID] AS [Application.CityID], [c].[CityName] AS [Application.City], [sp].[StateProvinceName] AS [Application.State] FROM [WideWorldImporters].[Application].[Cities] [c] INNER JOIN [WideWorldImporters].[Application].[StateProvinces] [sp] ON [c].StateProvinceID = [sp].[StateProvinceID] ORDER BY [c].[CityName], [sp].[StateProvinceID] FOR JSON AUTO; GO
And now we see the second table's values are in nested arrays.
![FOR JSON AUTO](/tipimages2/8043_string-to-json-sql-server-4.png)
FOR JSON PATH
As stated before, the AUTO mode's behavior cannot be changed. But we likely need more control over the output, so we'll change to using the FOR JSON clause with the PATH mode. PATH mode lets you create wrapper objects and nest complex properties.
This query is the same as the first one, except that the AUTO has been changed to PATH.
/* FOR JSON PATH */ SELECT TOP 2 [CityID] AS [Application.CityID], [CityName] AS [Application.City] FROM [WideWorldImporters].[Application].[Cities] ORDER BY [CityName] FOR JSON PATH; GO
The key name is now determined by the column or alias name. And nested objects are created. The FOR JSON PATH clause uses the column alias or column name to determine the key name in the JSON output. If an alias contains dots, nested objects are created.
![FOR JSON PATH - One Table](/tipimages2/8043_string-to-json-sql-server-5.png)
Join the Application.StateProvinces table again.
/* FOR JSON PATH */ SELECT TOP 2 [c].[CityID] AS [Application.CityID], [c].[CityName] AS [Application.City], [sp].[StateProvinceName] AS [Application.State] FROM [WideWorldImporters].[Application].[Cities] [c] INNER JOIN [WideWorldImporters].[Application].[StateProvinces] [sp] ON [c].StateProvinceID = [sp].[StateProvinceID] ORDER BY [c].[CityName], [sp].[StateProvinceID] FOR JSON PATH; GO
And we see each field nested under ‘Application'.
![FOR JSON Path - Join](/tipimages2/8043_string-to-json-sql-server-6.png)
JSON INCLUDE_NULL_VALUES
In this query, we're only selecting rows where LatestRecordedPopulation is in the Application.Cities table and has a value of null.
/* NULLs omitted */ SELECT TOP 2 [CityID] AS [Application.CityID], [CityName] AS [Application.City], [LatestRecordedPopulation] AS [Application.LatestRecordedPopulation] FROM [WideWorldImporters].[Application].[Cities] WHERE [LatestRecordedPopulation] IS NULL ORDER BY [CityName] FOR JSON PATH; GO
The default behavior of FOR JSON is to exclude the null fields.
![Without INCLUDE_NULL_VALUES](/tipimages2/8043_string-to-json-sql-server-7.png)
To include them, add the INCLUDE_NULL_VALUES option.
/* INCLUDE_NULL_VALUES */ SELECT TOP 2 [CityID] AS [Application.CityID], [CityName] AS [Application.City], [LatestRecordedPopulation] AS [Application.LatestRecordedPopulation] FROM [WideWorldImporters].[Application].[Cities] WHERE [LatestRecordedPopulation] IS NULL ORDER BY [CityName] FOR JSON PATH, INCLUDE_NULL_VALUES; GO
And here, we see null fields.
![INCLUDE_NULL_VALUES](/tipimages2/8043_string-to-json-sql-server-8.png)
JSON ROOT
By default, a root node is not created by FOR JSON. To add a root, we use the ROOT option in the form ROOT(‘Name') to add a single, top-level element to the JSON output.
/* ROOT */ SELECT TOP 2 [CityID] AS [Application.CityID], [CityName] AS [Application.City], [LatestRecordedPopulation] AS [Application.LatestRecordedPopulation] FROM [WideWorldImporters].[Application].[Cities] WHERE [LatestRecordedPopulation] IS NULL ORDER BY [CityName] FOR JSON PATH, ROOT('MyRoot'); GO
![ROOT](/tipimages2/8043_string-to-json-sql-server-9.png)
JSON WITHOUT_ARRAY_WRAPPER
Specifying the WITHOUT_ARRAY_WRAPPER option will remove the square brackets surrounding the JSON. This option is used with a single-row result and generates a single JSON object as opposed to an array with a single element. Otherwise, it would not be valid JSON.
/* WITHOUT_ARRAY_WRAPPER */ SELECT TOP 1 [CityID] AS [Application.CityID], [CityName] AS [Application.City], [LatestRecordedPopulation] AS [Application.LatestRecordedPopulation] FROM [WideWorldImporters].[Application].[Cities] WHERE [LatestRecordedPopulation] IS NULL ORDER BY [CityName] FOR JSON PATH, WITHOUT_ARRAY_WRAPPER; GO
The square brackets are gone.
![WITHOUT_ARRAY_WRAPPER](/tipimages2/8043_string-to-json-sql-server-10.png)
Next Steps
We saw how to use the FOR JSON clause to convert the output of SQL queries to the JSON format. The following are links to a few tips on working with JSON and SQL Server that you'll find at MSSQLTips.com.
- Introducing JSON for SQL Server 2016
- JSON Support in SQL Server 2016
- Advanced Techniques to Transform Relational Data to JSON in SQL Server 2016
- Save SQL Server Database Structure as JSON
- Including NULL Values in JSON Output in SQL Server 2016
- Transforming JSON Data to Relational Data in SQL Server 2016
- Opening JSON Data with T-SQL
- Advanced JSON Techniques in SQL Server - Part 1
- Advanced JSON Techniques in SQL Server - Part 2
- Advanced JSON Techniques in SQL Server - Part 3
About the author
![MSSQLTips author Joe Gavin](/images/Joe-Gavin-2023.png)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-07-25