Convert a String to JSON with SQL Server Queries

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.

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.

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).

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 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.

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'.

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.

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.

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

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.

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.

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

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