The simplest way to get query results as JSON is to use FOR JSON PATH in the query (note: this was added in SQL Server 2016):
SELECT [Name], [Year]
FROM Movies
FOR JSON PATH
Code language: SQL (Structured Query Language) (sql)It returns the results as a single JSON string with one JSON object per row:
[
{
"Name": "Office Space",
"Year": 1999
},
{
"Name": "John Wick",
"Year": 2014
},
{
"Name": "Mad Max: Fury Road",
"Year": 2015
}
]
Code language: JSON / JSON with Comments (json)Note: SQL Server returns the JSON without indenting. All examples in this article show it as indented for readability purposes only.
In this article, I’ll go into details about how to use FOR JSON PATH, and at the end I’ll compare the performance of server-side vs client-side JSON serialization.
Table of Contents
Getting the result on the client-side
On the client-side, when you are only getting a JSON string (and not just including it as one of the columns with a subquery), it will return the results as multiple rows of strings (of up to 2033 characters), which you have to join together.
Here’s an example of using Dapper to get a JSON string:
using Dapper;
using (var con = new SqlConnection(connectionString))
{
var jsonStringRows = con.Query<string>("SELECT Name, Year FROM Movies FOR JSON PATH");
return string.Join("", jsonStringRows);
}
Code language: C# (cs)Return a single JSON object
By default, FOR JSON PATH will return a JSON array, even if there’s only one row in the results.
If you want it to return a single JSON object, you can use the WITHOUT_ARRAY_WRAPPER option:
SELECT TOP 1 [Name], [Year]
FROM Movies
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Code language: SQL (Structured Query Language) (sql)This returns a single JSON object:
{
"Name": "Office Space",
"Year": 1999
}
Code language: JSON / JSON with Comments (json)Note: You can use WITHOUT_ARRAY_PARAMETER with multiple rows, but it returns comma-separated JSON objects (which is invalid JSON), so I don’t recommend it.
Changing the JSON property names
By default, the JSON property names will match the column names as specified in the query. This means you can change the JSON property names by changing the column name casing or by using an alias.
If you want to change the property names to use camel-casing, specify the column names with column casing in the select:
SELECT [name], [numberOfEpisodes], [numberOfSeasons]
FROM Shows
FOR JSON PATH
Code language: SQL (Structured Query Language) (sql)This outputs the JSON with camel-cased property names:
[
{
"name": "Star Trek: Picard",
"numberOfEpisodes": 10,
"numberOfSeasons": 1
}
]
Code language: JSON / JSON with Comments (json)Note: This wouldn’t work if you’re using a case-sensitive collation. In that scenario, use aliasing instead.
If you want the property name to be different from the column name, you can use aliasing:
SELECT [Name], [NumberOfEpisodes] as [EpisodeCount], [NumberOfSeasons] as [SeasonCount]
FROM Shows
FOR JSON PATH
Code language: SQL (Structured Query Language) (sql)This outputs the JSON using the column aliases as the property names:
[
{
"Name": "Star Trek: Picard",
"EpisodeCount": 10,
"SeasonCount": 1
}
]
Code language: JSON / JSON with Comments (json)If you’re using SELECT *, it’ll use the column names as they are defined in the table.
Getting a related table as a JSON array
A Show has one or more Episodes. To get the Episodes table as a JSON array, you can do a subquery:
SELECT [Name],
(SELECT e.Season, e.Number, e.Title FROM Episodes e
WHERE e.Show = s.Name
FOR JSON PATH) as [Episodes]
FROM Shows s
FOR JSON PATH
Code language: SQL (Structured Query Language) (sql)This outputs the following (notice the Episodes JSON array property):
[
{
"Name": "Star Trek: Picard",
"Episodes": [
{
"Season": 1,
"Number": 1,
"Title": "Remembrance"
},
{
"Season": 1,
"Number": 2,
"Title": "Maps and Legends"
}
]
}
]
Code language: JSON / JSON with Comments (json)Note: You can do a JOIN with FOR JSON AUTO instead of a subquery, but I wouldn’t recommend it. The subquery approach is easier to understand, and there are some quirks with JOINs.
Changing the JSON property nesting
To nest properties, you can alias the column using the “dot syntax”:
SELECT [Name]
,[RuntimeMinutes]
,[Year] as 'BoxOffice.Year'
,[Revenue] as 'BoxOffice.Revenue'
FROM Movies
FOR JSON PATH
Code language: SQL (Structured Query Language) (sql)This outputs the following with the nested BoxOffice property:
[
{
"Name": "Office Space",
"RuntimeMinutes": 89,
"BoxOffice": {
"Year": 1999,
"Revenue": 12200000
}
}
]
Code language: JSON / JSON with Comments (json)This is useful because it’s common for table definitions (flat) to not match object definitions (hierarchy).
Include nulls
Null values aren’t included in the JSON results by default. If you want to include nulls, you can use the INCLUDE_NULL_VALUES option:
SELECT [Name]
,[FirstYear]
,[LastYear]
FROM Shows
FOR JSON PATH, INCLUDE_NULL_VALUES
Code language: SQL (Structured Query Language) (sql)This outputs the following (notice the null property):
[
{
"Name": "Star Trek: Picard",
"FirstYear": 2020,
"LastYear": null
},
{
"Name": "Breaking Bad",
"FirstYear": 2008,
"LastYear": 2013
}
]
Code language: JSON / JSON with Comments (json)DateTime format
DateTime (and related) columns are returned in the JSON string in the ISO-8601 standard format:
[
{
"Name": "John Wick",
"DateOfRelease": "2014-10-24T19:00:00Z"
}
]
Code language: JSON / JSON with Comments (json)You can change the date format in the results by using the FORMAT(date) SQL function:
SELECT [Name],
FORMAT([DateOfRelease], 'MM-dd-yyyy') as [DateOfRelease]
FROM [Movies]
FOR JSON PATH
Code language: SQL (Structured Query Language) (sql)This uses the US date format (MM-dd-yyyy) for the DateOfRelease property:
[
{
"Name": "John Wick",
"DateOfRelease": "10-24-2014"
}
]
Code language: JSON / JSON with Comments (json)Unnamed column error
If you call a SQL function on a column, the resulting column will be unnamed, and you’ll get the following error:
Column expressions and data sources without names or aliases cannot be formatted as JSON text using FOR JSON clause. Add alias to the unnamed column or table.
To fix this, alias the result of the function call.
For example, the following query would result in the unnamed column error:
SELECT COUNT(*), MAX(Year)
FROM Movies
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Code language: SQL (Structured Query Language) (sql)To fix this, alias COUNT(*) and MAX(Year):
SELECT COUNT(*) as [Count], MAX(Year) as [MaxYear]
FROM Movies
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Code language: SQL (Structured Query Language) (sql)Now this is able to run and outputs:
{
"Count": 3,
"MaxYear": 2015
}
Code language: JSON / JSON with Comments (json)Performance comparison: server-side vs client-side JSON serialization
I compared the performance of server-side and client-side JSON serialization by sending 1, 10, and 100 concurrent requests. I repeated the test with 1000 rows and 1 row of data.
In conclusion, the server-side approach is slightly faster when dealing with small amounts of data. However, the client-side approach is significantly faster and uses less memory when dealing with more data.
Code compared
Server-side JSON serialization code:
using (var con = new SqlConnection(connectionString))
{
var jsonStringRows = con.Query<string>("SELECT Id, Name, DateOfRelease, RuntimeMinutes FROM Movies FOR JSON PATH, INCLUDE_NULL_VALUES");
return string.Join("", jsonStringRows);
}
Code language: C# (cs)Client-side JSON serialization code:
using (var connection = new SqlConnection(connectionString))
{
var movies = connection.Query<Movie>("SELECT Id, Name, DateOfRelease, RuntimeMinutes FROM Movies");
var moviesJson = JsonSerializer.Serialize(movies, options);
return moviesJson;
}
Code language: C# (cs)