在 PATH 模式下格式化嵌套 JSON 输出 (SQL Server)

项目
07/08/2024

适用范围：SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics（仅限无服务器 SQL 池）

若要保持对 FOR JSON 子句输出的完全控制，请指定 PATH 选项。

借助PATH 模式，你可以创建包装器对象，并嵌套复杂属性。结果会格式化为 JSON 对象数组。

替代方法是使用 AUTO 选项根据 SELECT 语句的结构自动格式化输出。

有关 AUTO 选项的详细信息，请参阅在 AUTO 模式下自动格式化 JSON 输出。
有关这两个选项的概述，请参阅使用 FOR JSON 将查询结果格式化为 JSON。

下面的一些示例展示了如何使用 PATH 选项指定 FOR JSON 子句。使用以点分隔的列名称或使用嵌套查询来格式化嵌套结果，如下面的示例所示。默认情况下，FOR JSON 输出中不包括 null 值。建议使用 Azure Data Studio 作为 JSON 查询的查询编辑器，因为它会自动格式化 JSON 结果（如本文所示），而不是显示平面字符串。

示例 - 以点分隔的列名称

以下查询将 AdventureWorks Person 表的前五行格式化为 JSON。

FOR JSON PATH 子句使用列别名或列名来确定 JSON 输出中的键名称。如果别名中包含点，则 PATH 选项将创建嵌套对象。

查询

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"
    }
}]

示例 - 多个表

如果查询中引用了多个表，FOR JSON PATH 将使用列别名嵌套每个列。以下查询将为查询中联接的每个（OrderHeader，OrderDetails）对创建一个 JSON 对象。

查询

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
    }
}]

详细了解 SQL Server 和 Azure SQL 数据库中的 JSON

Microsoft 视频

有关 SQL Server 和 Azure SQL 数据库中内置 JSON 支持的视频介绍，请观看以下视频：

JSON 充当 NoSQL 和关系环境之间的桥梁

另请参阅

FOR 子句 (Transact-SQL)

通过