SQL Server makes it surprisingly simple to generate JSON directly from a query. If you’ve ever had to send data to a web service, feed an API, or just save query results in a structured text format, JSON is a natural choice. Instead of writing complex formatting logic in your application, you can let SQL Server do the heavy lifting with the FOR JSON clause. And you can use the AUTO keyword to have the JSON formatted automatically by SQL Server.
In this article, we’ll look at how FOR JSON AUTO works, along with some examples to demonstrate its usage.
What FOR JSON AUTO Does
When you add FOR JSON AUTO to the end of a SELECT query, SQL Server automatically converts the result set into JSON. The way it organizes the JSON is tied to the structure of your query:
- Each row in the result set becomes a JSON object.
- Each column becomes a property in that object.
- If you include joins, SQL Server nests related tables into child arrays or objects.
The keyword AUTO is important here. It tells SQL Server to figure out the structure based on the query itself. When using the AUTO keyword, you don’t explicitly define the JSON format. Instead, SQL Server infers it from the SELECT statement.
A Basic Example
Imagine you have a table called Employees:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Department NVARCHAR(50)
);
INSERT INTO Employees VALUES
(1, 'Sophia', 'Martinez', 'Finance'),
(2, 'David', 'Nguyen', 'Engineering'),
(3, 'Amira', 'Khan', 'HR');If you query it like this:
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees
FOR JSON AUTO;The output looks something like:
[{"EmployeeID":1,"FirstName":"Sophia","LastName":"Martinez","Department":"Finance"},{"EmployeeID":2,"FirstName":"David","LastName":"Nguyen","Department":"Engineering"},{"EmployeeID":3,"FirstName":"Amira","LastName":"Khan","Department":"HR"}]
The formatting of the JSON output may look slightly different depending on your client/setup. For example, if you’re querying it in VS Code, you might see a single line crammed into a table cell. But if you click in the table cell, it should open a new tab with a prettified version of the JSON. Something like this:
[
{
"EmployeeID": 1,
"FirstName": "Sophia",
"LastName": "Martinez",
"Department": "Finance"
},
{
"EmployeeID": 2,
"FirstName": "David",
"LastName": "Nguyen",
"Department": "Engineering"
},
{
"EmployeeID": 3,
"FirstName": "Amira",
"LastName": "Khan",
"Department": "HR"
}
]
Either way, the FOR JSON AUTO has done its job. It has outputted the query results as JSON using the query structure to determine the JSON structure.
Handling Joins with FOR JSON AUTO
Things get more interesting when joins are involved. By default, it doesn’t actually group related rows into arrays. It simply reflects the joined result set. That means if one parent row is related to multiple child rows, you’ll see the parent repeated. That said, we can certainly construct our query in order to provide such grouping.
Let’s add a second table for projects:
CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
ProjectName NVARCHAR(100),
EmployeeID INT
);
INSERT INTO Projects VALUES
(101, 'Annual Report', 1),
(102, 'Data Migration', 2),
(103, 'Recruitment Drive', 3),
(104, 'Cloud Integration', 2);Now, join Employees and Projects:
SELECT e.EmployeeID, e.FirstName, e.LastName, p.ProjectID, p.ProjectName
FROM Employees e
JOIN Projects p ON e.EmployeeID = p.EmployeeID
FOR JSON AUTO;Result:
[
{
"EmployeeID": 1,
"FirstName": "Sophia",
"LastName": "Martinez",
"p": [{ "ProjectID": 101, "ProjectName": "Annual Report" }]
},
{
"EmployeeID": 2,
"FirstName": "David",
"LastName": "Nguyen",
"p": [{ "ProjectID": 102, "ProjectName": "Data Migration" }]
},
{
"EmployeeID": 3,
"FirstName": "Amira",
"LastName": "Khan",
"p": [{ "ProjectID": 103, "ProjectName": "Recruitment Drive" }]
},
{
"EmployeeID": 2,
"FirstName": "David",
"LastName": "Nguyen",
"p": [{ "ProjectID": 104, "ProjectName": "Cloud Integration" }]
}
]
Notice how David Nguyen appears twice, once for each project. This is technically correct , as it mirrors the underlying rowset. In some cases, this flat representation might be useful (for example, if your consuming application expects one row per combination).
But most of the time, you’ll want a more hierarchical structure, with projects grouped under each employee. To do that, you’ll need a subquery:
SELECT
e.EmployeeID,
e.FirstName,
e.LastName,
(
SELECT p.ProjectID, p.ProjectName
FROM Projects p
WHERE p.EmployeeID = e.EmployeeID
FOR JSON AUTO
) AS Projects
FROM Employees e
FOR JSON AUTO;Output:
[
{
"EmployeeID": 1,
"FirstName": "Sophia",
"LastName": "Martinez",
"Projects": [{ "ProjectID": 101, "ProjectName": "Annual Report" }]
},
{
"EmployeeID": 2,
"FirstName": "David",
"LastName": "Nguyen",
"Projects": [
{ "ProjectID": 102, "ProjectName": "Data Migration" },
{ "ProjectID": 104, "ProjectName": "Cloud Integration" }
]
},
{
"EmployeeID": 3,
"FirstName": "Amira",
"LastName": "Khan",
"Projects": [{ "ProjectID": 103, "ProjectName": "Recruitment Drive" }]
}
]
This approach groups all related projects into an array under each employee, which is usually the more natural fit when working with JSON in APIs or configuration files. Both are valid depending on your needs, but the subquery pattern is probably the one you’ll want when exporting relational data into JSON.
When to Use FOR JSON AUTO
FOR JSON AUTO is perfect when:
- You want quick JSON output without defining custom shapes.
- Your query structure already matches the hierarchy you need.
- You’re prototyping or sending results to external tools for debugging or lightweight integrations.
If you need more control (such as renaming keys, customizing nesting, or shaping the JSON differently) FOR JSON PATH is a better choice. But for many straightforward use cases, AUTO gets the job done with minimal effort.