Skip to content

format-nested-json-output-with-path-mode-sql-server.md

Latest commit

 

History

History
129 lines (106 loc) · 4.11 KB

format-nested-json-output-with-path-mode-sql-server.md

File metadata and controls

129 lines (106 loc) · 4.11 KB
title Format Nested JSON Output with PATH Mode
description To maintain full control over the output of the FOR JSON clause, specify the PATH option.
author WilliamDAssafMSFT
ms.author wiassaf
ms.reviewer jovanpop, umajay
ms.date 07/23/2025
ms.service sql
ms.topic how-to
ms.custom
ignite-2025
monikerRange =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

Format Nested JSON Output with PATH Mode

[!INCLUDE sqlserver2016-asdb-asdbmi-asa-serverless-pool-only-fabricse-fabricdw-fabricsqldb]

To maintain full control over the output of the FOR JSON clause, specify the PATH option.

PATH mode lets you create wrapper objects and nest complex properties. The results are formatted as an array of JSON objects.

The alternative is to use the AUTO option to format the output automatically based on the structure of the SELECT statement.

Here are some examples of the FOR JSON clause with the PATH option. Format nested results by using dot-separated column names or by using nested queries, as shown in the following examples. By default, null values are not included in FOR JSON output. Azure Data Studio is the recommended query editor for JSON queries because it auto-formats the JSON results (as seen in this article) instead of displaying a flat string.

Example - Dot-separated column names

The following query formats the first five rows from the AdventureWorks Person table as JSON.

The FOR JSON PATH clause uses the column alias or column name to determine the key name in the JSON output. If an alias contains dots, the PATH option creates nested objects.

Query

SELECT TOP 5   
       BusinessEntityID As Id,  
       FirstName, LastName,  
       Title As 'Info.Title',  
       MiddleName As 'Info.MiddleName'  
   FROM Person.Person  
   FOR JSON PATH

Result

[{
    "Id": 1,
    "FirstName": "Ken",
    "LastName": "Sanchez",
    "Info": {
        "MiddleName": "J"
    }
}, {
    "Id": 2,
    "FirstName": "Terri",
    "LastName": "Duffy",
    "Info": {
        "MiddleName": "Lee"
    }
}, {
    "Id": 3,
    "FirstName": "Roberto",
    "LastName": "Tamburello"
}, {
    "Id": 4,
    "FirstName": "Rob",
    "LastName": "Walters"
}, {
    "Id": 5,
    "FirstName": "Gail",
    "LastName": "Erickson",
    "Info": {
        "Title": "Ms.",
        "MiddleName": "A"
    }
}]

Example - Multiple tables

If you reference more than one table in a query, FOR JSON PATH nests each column using its alias. The following query creates one JSON object per (OrderHeader, OrderDetails) pair joined in the query.

Query

SELECT TOP 2 H.SalesOrderNumber AS 'Order.Number',  
        H.OrderDate AS 'Order.Date',  
        D.UnitPrice AS 'Product.Price',  
        D.OrderQty AS 'Product.Quantity'  
FROM Sales.SalesOrderHeader H  
   INNER JOIN Sales.SalesOrderDetail D  
     ON H.SalesOrderID = D.SalesOrderID  
FOR JSON PATH

Result

[{
    "Order": {
        "Number": "SO43659",
        "Date": "2011-05-31T00:00:00"
    },
    "Product": {
        "Price": 2024.9940,
        "Quantity": 1
    }
}, {
    "Order": {
        "Number": "SO43659"
    },
    "Product": {
        "Price": 2024.9940
    }
}]

Learn more about JSON in the SQL Database Engine

For a visual introduction to the built-in JSON support, see the following videos:

Related content