使用 ROOT 选项将根节点添加到 JSON 输出中 (SQL Server)Add a Root Node to JSON Output with the ROOT Option (SQL Server)

适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later

若要将一个顶层元素添加到 FOR JSON 子句的 JSON 输出中,请指定 ROOT 选项。To add a single, top-level element to the JSON output of the FOR JSON clause, specify the ROOT option.

如果你没有指定 ROOT 选项,则 JSON 输出不会包括根元素。If you don't specify the ROOT option, the JSON output doesn't include a root element.

示例Examples

下表展示了在指定和未指定 ROOT 选项的情况下 FOR JSON 子句的输出。The following table shows the output of the FOR JSON clause with and without the ROOT option.

下表中的示例假定可选自变量 RootName 为空。The examples in the following table assume that the optional RootName argument is empty. 如果你提供了根元素的名称,则此值会替换示例中的值 rootIf you provide a name for the root element, this value replaces the value root in the examples.

未指定 ROOT 选项Without the ROOT option

{  
   <<json properties>>  
}  
[  
   <<json array elements>>  
]  

指定 ROOT 选项With the ROOT option

{   
  "root": {  
   <<json properties>>  
 }  
}  
{   
  "root": [  
   << json array elements >>  
  ]  
}  

下面又通过一个示例展示了在指定 ROOT 选项的情况下 FOR JSON 子句的输出。Here's another example of a FOR JSON clause with the ROOT option. 此示例指定了可选自变量 RootName 的值。This example specifies a value for the optional RootName argument.

查询Query

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

结果Result

{
    "info": [{
        "Id": 1,
        "FirstName": "Ken",
        "LastName": "Sánchez",
        "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"
        }
    }]
}

结果(未指定根)Result (without root)

[{
    "Id": 1,
    "FirstName": "Ken",
    "LastName": "Sánchez",
    "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"
    }
}]

详细了解 SQL Server 和 Azure SQL 数据库中的 JSONLearn more about JSON in SQL Server and Azure SQL Database

Microsoft 视频Microsoft videos

有关 SQL Server 和 Azure SQL 数据库中内置 JSON 支持的视频介绍,请观看以下视频:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

另请参阅See Also

FOR 子句 (Transact-SQL)FOR Clause (Transact-SQL)