How do you use the FOR JSON PATH clause to generate custom JSON from query results?
QuinnLw
Posted by QuinnLw
Last Updated: July 24, 2024
JSON SQL
In SQL Server, the FOR JSON PATH clause is used to format the result set of a query as JSON. This is particularly useful when you want to create a custom JSON structure, as it allows you to control the hierarchy and naming of the JSON output. Here’s a basic guide on how to use FOR JSON PATH to generate custom JSON from your query results:
Basic Syntax:
SELECT column1, column2
FROM your_table
FOR JSON PATH
Customizing the JSON Structure:
1. Nested JSON Objects: You can create nested JSON objects by using subqueries. 
SELECT 
       a.id,
       a.name,
       (SELECT b.*
        FROM related_table b
        WHERE b.foreign_key = a.id
        FOR JSON PATH) AS related_items
   FROM your_table a
   FOR JSON PATH;
2. Customizing Property Names: You can customize the names of JSON properties by using the AS keyword. 
SELECT 
       id AS customId,
       name AS customName
   FROM your_table
   FOR JSON PATH;
3. Grouping Data: You can aggregate data and group it appropriately in the JSON structure. 
SELECT 
       department,
       (SELECT emp_name, emp_id
        FROM employees e
        WHERE e.dept_id = d.id
        FOR JSON PATH) AS employees
   FROM departments d
   FOR JSON PATH;
4. Using the ROOT Option: If you want to add a root element to your JSON output, you can use the ROOT option. 
SELECT column1, column2
   FROM your_table
   FOR JSON PATH, ROOT('rootElement');
Example:
Here’s a complete example that incorporates several of the above elements. Suppose you have the following tables: - Departments (id, name) - Employees (id, emp_name, dept_id) You want to create a JSON structure that looks like this: 
{
    "Departments": [
        {
            "Id": 1,
            "Name": "Sales",
            "Employees": [
                {"Id": 101, "Name": "Alice"},
                {"Id": 102, "Name": "Bob"}
            ]
        },
        {
            "Id": 2,
            "Name": "HR",
            "Employees": [
                {"Id": 201, "Name": "Charlie"}
            ]
        }
    ]
}
Here’s how you could construct the query to generate that JSON: 
SELECT 
    d.id AS Id,
    d.name AS Name,
    (SELECT 
        e.id AS Id,
        e.emp_name AS Name
     FROM Employees e
     WHERE e.dept_id = d.id
     FOR JSON PATH) AS Employees
FROM Departments d
FOR JSON PATH, ROOT('Departments');
Summary:
- Use FOR JSON PATH to convert SQL results to JSON. - Customize the output with subqueries for nested objects and AS for custom property names. - Use the ROOT option to wrap your JSON in a specific root element. - Test and adjust your queries as needed to achieve the desired structure and format in the JSON output. Always ensure to test your query in your SQL Server environment, as the actual output might depend on the data and the relationships in your database schema.
Default Image