借助 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 PATH

使用 FOR JSON PATH 控制输出

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

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

有关 FOR JSON PATH 的详细信息

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

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

FOR JSON AUTO

SELECT 语句使用 FOR JSON AUTO 控制输出

在 AUTO 模式下，SELECT 语句的结构决定 JSON 输出的格式。

默认情况下，输出中不包括 NULL 值。 可使用 INCLUDE_NULL_VALUES 更改此行为。

下面是一个示例查询，它使用带有 FOR JSON 子句的 AUTO 模式。

SELECT name, surname
FROM emp
FOR JSON AUTO;

下面是返回的 JSON。

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

JOIN 和 NULL 的示例

以下 SELECT...FOR JSON AUTO 示例显示了当连接表中的数据之间存在“1 对多”关系时 JSON 结果如何。

还说明了返回的 JSON 中缺少 NULL 值。 但是，可以使用 FOR 子句上的 INCLUDE_NULL_VALUES 关键字替代此默认行为。

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

CREATE TABLE #tabClass (
    ClassGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    ClassName NVARCHAR(32) NOT NULL
);

CREATE TABLE #tabStudent (
    StudentGuid UNIQUEIDENTIFIER NOT NULL DEFAULT newid(),
    StudentName NVARCHAR(32) NOT NULL,
    ClassGuid UNIQUEIDENTIFIER NULL -- Foreign key.
);
GO

INSERT INTO #tabClass (ClassGuid, ClassName)
VALUES
    ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
    ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
    ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting');

INSERT INTO #tabStudent (StudentName, ClassGuid)
VALUES
    ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
    ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
    ('Betty Boot', '98509D36-A2C8-4A65-A310-E744F5621C83'),
    ('Carla Cap', null);
GO

SELECT c.ClassName,
    s.StudentName
FROM #tabClass AS c
RIGHT JOIN #tabStudent AS s ON s.ClassGuid = c.ClassGuid
ORDER BY c.ClassName,
    s.StudentName
FOR JSON AUTO
    -- To include NULL values in the output, uncomment the following line:
    --, INCLUDE_NULL_VALUES
    ;
GO

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;
GO

接下来是上述 SELECT 输出的 JSON。

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

有关 FOR JSON AUTO 的详细信息

有关详细信息和示例，请参阅在 AUTO 模式下自动格式化 JSON 输出 (SQL Server)。

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

控制其他 JSON 输出选项

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

• ROOT

  若要将单个顶层元素添加到 JSON 输出中，请指定 ROOT 选项。 如果没有指定此选项，JSON 输出不会包括根元素。 有关详细信息，请参阅使用 ROOT 选项将根节点添加到 JSON 输出中 (SQL Server)。

• INCLUDE_NULL_VALUES

  若要在 JSON 输出中包含 null 值，请指定 INCLUDE_NULL_VALUES 选项。 如果没有指定此选项，输出不会在查询结果中包括 NULL 值的 JSON 属性。 有关更多信息，请参阅将 NULL 值包含在 JSON 输出中—INCLUDE_NULL_VALUES 选项。

• WITHOUT_ARRAY_WRAPPER

  若要删除默认括住 FOR JSON 子句的 JSON 输出的方括号，请指定 WITHOUT_ARRAY_WRAPPER 选项。 使用此选项可以生成单个 JSON 对象作为单行结果中的输出。 如果不指定此选项，JSON 输出将格式化为数组 - 即输出括在方括号内。 有关更多信息，请参阅从 JSON 输出中删除方括号—WITHOUT_ARRAY_WRAPPER 选项。

FOR JSON 子句的输出

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

1. 结果集包含单个列。

   • 一个小结果集可包含单个列。
   • 一个大结果集可将长 JSON 字符串拆分到多行中。

     • 默认情况下，输出设置为“以网格显示结果”时，SQL Server Management Studio (SSMS) 会将结果连接到单个行中。 SSMS 状态栏会显示实际行数。

     • 其他客户端应用程序可能需要使用代码，通过串联多个行的内容来将较长的结果重新组合为单个有效的 JSON 字符串。 有关 C# 应用程序中此代码的示例，请参阅在 C# 客户端应用中使用 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"
}]

相关内容

• FOR JSON 如何将 SQL Server 数据类型转换为 JSON 数据类型 (SQL Server)
• FOR JSON 如何转义特殊字符和控制字符 (SQL Server)
• JSON 充当 NoSQL 和关系环境之间的桥梁
• SELECT - FOR 子句 (Transact-SQL)
• 在 SQL Server 和客户端应用中使用 FOR JSON 输出 (SQL Server)