When using SQL Server, you can use the FOR JSON clause in a query to format the results as JSON. When doing this, you must choose either the AUTO or the PATH option. This article contains examples of using the PATH option.

Syntax

The syntax goes like this:

SELECT ...
  (your query goes here)
FOR JSON PATH;

So basically, all you need to do is add FOR JSON PATH to the end of your query.

Example 1 – Basic Usage

Here’s an example to demonstrate.

USE Music;
SELECT TOP 3 
  AlbumName, 
  ReleaseDate
FROM Albums
FOR JSON PATH;

Result:

[
    {
        "AlbumName": "Powerslave",
        "ReleaseDate": "1984-09-03"
    },
    {
        "AlbumName": "Powerage",
        "ReleaseDate": "1978-05-05"
    },
    {
        "AlbumName": "Singing Down the Lane",
        "ReleaseDate": "1956-01-01"
    }
]

So the results come out as a nicely formatted JSON document, instead of in rows and columns.

In this case I used TOP 3 to limit the result set to just three results.

Single Line Results?

Your results may initially appear in a single row and a single column, and as one long line like this:

If this is the case, try clicking the result set. Depending on your database management software, this should launch the JSON document as it appears in the above example.

Whether this works exactly as described will depend on the software that you use to query SQL Server.

At the time of writing, this worked fine for me when using SQL Operations Studio (which has since been renamed to Azure Data Studio).  It also worked fine when using the MSSQL extension in VS Code. SSMS however, only formats the results as one long line (although still in JSON format).

I also had varying degrees of success using command line tools.

You may also find that the results are initially distributed across multiple rows, depending on how large the result set is.

If you can’t get it to display the results in a satisfactory way, try a different tool.

Example 2 – Query across Multiple Tables

In this example, I query two tables that have a one-to-many relationship between them. In this case, each artist can have many albums, and I use a subquery to retrieve the albums for each artist.

USE Music;
SELECT TOP 2 ArtistName,
    (SELECT AlbumName 
        FROM Albums
        WHERE Artists.ArtistId = Albums.ArtistId
        FOR JSON PATH) AS Albums
FROM Artists
ORDER BY ArtistName
FOR JSON PATH;

Result:

[
    {
        "ArtistName": "AC/DC",
        "Albums": [
            {
                "AlbumName": "Powerage"
            }
        ]
    },
    {
        "ArtistName": "Allan Holdsworth",
        "Albums": [
            {
                "AlbumName": "All Night Wrong"
            },
            {
                "AlbumName": "The Sixteen Men of Tain"
            }
        ]
    }
]

In this case, each album has been nested under “Albums”. This is because the albums are returned via a subquery.

Example 3 – Nested Output with Dot Notation

When using the PATH option, you can use dot-separated column names to create nested objects.

To do this, use a dot-separated alias. Here’s an example:

USE Music;
SELECT TOP 3 
  AlbumId,
  AlbumName AS 'Details.Album Name', 
  ReleaseDate AS 'Details.Release Date'
FROM Albums
FOR JSON PATH;

Result:

[
    {
        "AlbumId": 1,
        "Details": {
            "Album Name": "Powerslave",
            "Release Date": "1984-09-03"
        }
    },
    {
        "AlbumId": 2,
        "Details": {
            "Album Name": "Powerage",
            "Release Date": "1978-05-05"
        }
    },
    {
        "AlbumId": 3,
        "Details": {
            "Album Name": "Singing Down the Lane",
            "Release Date": "1956-01-01"
        }
    }
]

Example 4 – Add a Root Node

You can use the ROOT() option to add a root node to the output. This adds a single, top-level element to the output. To do this, simply add the ROOT() option to the end of the query, with the name you want root node to have.

So we can modify the previous example to this:

USE Music;
SELECT TOP 3 
  AlbumId,
  AlbumName AS 'Details.Album Name', 
  ReleaseDate AS 'Details.Release Date'
FROM Albums
FOR JSON PATH, ROOT('Albums');

Result:

{
    "Albums": [
        {
            "AlbumId": 1,
            "Details": {
                "Album Name": "Powerslave",
                "Release Date": "1984-09-03"
            }
        },
        {
            "AlbumId": 2,
            "Details": {
                "Album Name": "Powerage",
                "Release Date": "1978-05-05"
            }
        },
        {
            "AlbumId": 3,
            "Details": {
                "Album Name": "Singing Down the Lane",
                "Release Date": "1956-01-01"
            }
        }
    ]
}

Example 5 – Remove the Array Wrapper

You can use the WITHOUT_ARRAY_WRAPPER option to remove the square brackets that surround the results.

Example:

USE Music;
SELECT TOP 1 
  AlbumName, 
  ReleaseDate
FROM Albums
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Result:

{
    "AlbumName": "Powerslave",
    "ReleaseDate": "1984-09-03"
}

Note that if you do this on a multiple row result, you’ll end up with invalid JSON.