How do you use the FOR JSON AUTO clause to generate JSON from query results?
JackBrn
Posted by JackBrn
Last Updated: June 08, 2024
JSON SQL
In SQL Server, the FOR JSON AUTO clause is used to format the results of a query as JSON. This can be particularly useful for applications that consume JSON data, such as web services or APIs. When you use FOR JSON AUTO, SQL Server automatically maps the result set to a JSON format based on the structure of the SQL query.
Basic Usage of FOR JSON AUTO
Here's a simple example to demonstrate how to use FOR JSON AUTO. Assume you have a table called Employees with the following columns: EmployeeID, FirstName, LastName, and DepartmentID. 
-- Example table structure
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    DepartmentID INT
);

-- Insert sample data
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID)
VALUES (1, 'John', 'Doe', 1),
       (2, 'Jane', 'Smith', 2),
       (3, 'Sam', 'Brown', 1);
Querying with FOR JSON AUTO
To generate a JSON result, you can write a query with the FOR JSON AUTO clause at the end. Here’s how you would query the Employees table: 
SELECT EmployeeID, FirstName, LastName, DepartmentID
FROM Employees
FOR JSON AUTO;
Result
The above query would generate a JSON output similar to: 
[
    {"EmployeeID":1,"FirstName":"John","LastName":"Doe","DepartmentID":1},
    {"EmployeeID":2,"FirstName":"Jane","LastName":"Smith","DepartmentID":2},
    {"EmployeeID":3,"FirstName":"Sam","LastName":"Brown","DepartmentID":1}
]
Nested JSON
If you have related tables and want to produce a nested JSON structure, you can leverage FOR JSON AUTO by joining tables. For instance, if we have another table called Departments: 
CREATE TABLE Departments (
    DepartmentID INT,
    DepartmentName NVARCHAR(50)
);

INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'HR'),
       (2, 'Finance');
You can write a more complex query like this: 
SELECT 
    e.EmployeeID, 
    e.FirstName, 
    e.LastName, 
    d.DepartmentName
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
FOR JSON AUTO;
Result for Nested JSON
This would yield a result similar to: 
[
    {"Employees":{"EmployeeID":1,"FirstName":"John","LastName":"Doe","DepartmentName":"HR"}},
    {"Employees":{"EmployeeID":2,"FirstName":"Jane","LastName":"Smith","DepartmentName":"Finance"}},
    {"Employees":{"EmployeeID":3,"FirstName":"Sam","LastName":"Brown","DepartmentName":"HR"}}
]
Notes
- The FOR JSON AUTO outputs the data based on the naming of your result set columns. The structure of the JSON reflects the hierarchy of your query's SELECT statement. - If you require more control over the JSON structure, you may choose FOR JSON PATH, which allows more detailed customization. - The output can be consumed directly by applications that support JSON, making it very useful for RESTful service implementations.
Summary
The FOR JSON AUTO clause is a powerful feature in SQL Server that can quickly convert query results into JSON format, suitable for modern application development needs involving data interchange and APIs.
Default Image