借助 FOR JSON 将查询结果的格式设置为 JSON

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

通过将 FOR JSON 子句添加到 SELECT 语句中,将查询结果格式化为 JSON,或者以 JSON 的形式从 SQL Server 导出数据。 使用 FOR JSON 子句,通过将 JSON 输出的格式处理从应用委托给 SQL Server 来简化客户端应用程序。

注意

建议使用 Azure Data Studio 作为 JSON 查询的查询编辑器,因为它会自动格式化 JSON 结果,如本文所示。 SQL Server Management Studio 显示未设置格式的字符串。

设置查询结果的格式

使用 FOR JSON 子句时,可显式指定 JSON 输出的结构,或者让 SELECT 语句的结构来决定输出。

  • 使用 FOR JSON PATH 来保持对 JSON 输出格式的完全控制。 你可以创建包装对象并嵌套复杂属性。

  • 若要根据 SELECT 语句的结构自动格式化 JSON 输出,请使用 FOR JSON AUTO

下面是带有 FOR JSON 子句的 SELECT 语句及其输出的示例。

显示 FOR JSON 工作原理的示意图。

使用 FOR JSON PATH 控制输出

PATH 模式下,可以使用点语法来设置嵌套的输出格式,例如 Item.Price

下面是一个示例查询,它使用带有 FOR JSON 子句的 PATH 模式。 下面的示例使用 ROOT 选项指定一个已命名根元素。

FOR JSON 输出的示意图。

有关 FOR JSON PATH 的详细信息

有关详细信息和示例,请参阅在 PATH 模式下设置嵌套的 JSON 输出格式 (SQL Server)

有关语法和用法的详细信息,请参阅 FOR 子句 (Transact-SQL)

控制其他 JSON 输出选项

使用以下额外选项控制 FOR JSON 子句的输出。

FOR JSON 子句的输出

FOR JSON 子句的输出具有以下特征:

  1. 结果集包含单个列。

    • 一个小结果集可包含单个列。
    • 一个大结果集可将长 JSON 字符串拆分到多行中。
      • 默认情况下,输出设置为“以网格显示结果”时,SQL Server Management Studio (SSMS) 会将结果连接到单个行中。 SSMS 状态栏会显示实际行数。

      • 其他客户端应用程序可能需要使用代码,通过串联多个行的内容来将较长的结果重新组合为单个有效的 JSON 字符串。 有关 C# 应用程序中此代码的示例,请参阅在 C# 客户端应用中使用 FOR JSON 输出

        SQL Server Management Studio 中的 FOR JSON 输出的屏幕截图。

  2. 结果会格式化为 JSON 对象数组。

    • JSON 数组中的元素数量等于 SELECT 语句结果中的行数(应用 FOR JSON 子句前)。

    • SELECT 语句结果中的每一行(应用 FOR JSON 子句前)将成为数组中的单独 JSON 对象。

    • SELECT 语句结果中的每一列(应用 FOR JSON 子句前)将成为 JSON 对象的属性。

  3. 列的名称及其值都会根据 JSON 语法进行转义。 有关详细信息,请参阅 FOR JSON 如何转义特殊字符和控制字符 (SQL Server)

示例

下面是演示 FOR JSON 子句如何格式化 JSON 输出的示例。

查询结果

A B C D
10 11 12 X
20 21 22 Y
30 31 32 Z

JSON 输出

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}]