In 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 AUTO option.

Syntax

The syntax goes like this:

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

So basically, all you need to do is add FOR JSON AUTO 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 AUTO;

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.

USE Music;
SELECT 
  ArtistName, 
  AlbumName
FROM Artists
  INNER JOIN Albums
    ON Artists.ArtistId = Albums.ArtistId
ORDER BY ArtistName
FOR JSON AUTO;

Result:

[
    {
        "ArtistName": "AC/DC",
        "Albums": [
            {
                "AlbumName": "Powerage"
            }
        ]
    },
    {
        "ArtistName": "Allan Holdsworth",
        "Albums": [
            {
                "AlbumName": "All Night Wrong"
            },
            {
                "AlbumName": "The Sixteen Men of Tain"
            }
        ]
    },
    {
        "ArtistName": "Buddy Rich",
        "Albums": [
            {
                "AlbumName": "Big Swing Face"
            }
        ]
    },
    {
        "ArtistName": "Devin Townsend",
        "Albums": [
            {
                "AlbumName": "Ziltoid the Omniscient"
            },
            {
                "AlbumName": "Casualties of Cool"
            },
            {
                "AlbumName": "Epicloud"
            }
        ]
    },
    {
        "ArtistName": "Iron Maiden",
        "Albums": [
            {
                "AlbumName": "Powerslave"
            },
            {
                "AlbumName": "Somewhere in Time"
            },
            {
                "AlbumName": "Piece of Mind"
            },
            {
                "AlbumName": "Killers"
            },
            {
                "AlbumName": "No Prayer for the Dying"
            }
        ]
    },
    {
        "ArtistName": "Jim Reeves",
        "Albums": [
            {
                "AlbumName": "Singing Down the Lane"
            }
        ]
    },
    {
        "ArtistName": "Michael Learns to Rock",
        "Albums": [
            {
                "AlbumName": "Blue Night"
            },
            {
                "AlbumName": "Eternity"
            },
            {
                "AlbumName": "Scandinavia"
            }
        ]
    },
    {
        "ArtistName": "The Script",
        "Albums": [
            {
                "AlbumName": "No Sound Without Silence"
            }
        ]
    },
    {
        "ArtistName": "Tom Jones",
        "Albums": [
            {
                "AlbumName": "Long Lost Suitcase"
            },
            {
                "AlbumName": "Praise and Blame"
            },
            {
                "AlbumName": "Along Came Jones"
            }
        ]
    }
]

As you can see, each album has been nested under “Albums”. This is because the AUTO option determines the output based on the order of columns in the SELECT list and their source tables.

Example 3 – 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
  ArtistName, 
  AlbumName
FROM Artists
  INNER JOIN Albums
    ON Artists.ArtistId = Albums.ArtistId
ORDER BY ArtistName
FOR JSON AUTO, ROOT('Music');

Result:

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

I also limited the result set to just three results by adding TOP 3 to the query.

Example 4 – 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 AUTO, 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.