SQL Server 中的 JSON 数据

JSON 是一种流行的数据格式，用于在现代 Web 和移动应用程序中交换数据。 JSON 还可用于在日志文件或 Microsoft Azure Cosmos DB 等 NoSQL 数据库中存储非结构化数据。 许多 REST Web 服务以 JSON 文本格式返回结果，或接受采用 JSON 格式的数据。 例如，大多数 Azure 服务（如 Azure 搜索、Azure 存储和 Azure Cosmos DB）都提供返回或使用 JSON 的 REST 终结点。 JSON 也是用于通过 AJAX 调用在网页与 Web 服务器之间交换数据的主要格式。

JSON 函数首次是在 SQL Server 2016 (13.x) 中引入的，使用它们可在同一数据库中将 NoSQL 和相关概念合并。 现在，可以将经典关系列与同一表中包含格式化为 JSON 文本的文档的列合并，在关系结构中分析和导入 JSON 文档，或者将关系数据格式化为 JSON 文本。

下面是 JSON 文本的示例：

JSON复制

[
    {
        "name": "John",
        "skills": [ "SQL", "C#", "Azure" ]
    },
    {
        "name": "Jane",
        "surname": "Doe"
    }
]

通过使用 SQL Server 内置函数和运算符，你可以对 JSON 文本执行以下操作：

• 分析 JSON 文本和读取或修改值。
• 将 JSON 对象数组转换为表格式。
• 在转换后的 JSON 对象上运行任意 Transact-SQL 查询。
• 将 Transact-SQL 查询的结果设置为 JSON 格式。

SQL Server 和 SQL 数据库的关键 JSON 功能

下一部分介绍 SQL Server 随其内置 JSON 支持一起提供的主要功能。

从 JSON 文本中提取值并在查询中使用这些值

如果使用存储在数据库表中的 JSON 文本，则可以使用以下内置函数来读取或修改 JSON 文本中的值：

• ISJSON (Transact-SQL) 测试字符串是否包含有效 JSON。
• JSON_VALUE (Transact-SQL) 从 JSON 字符串中提取标量值。
• JSON_QUERY (Transact-SQL) 从 JSON 字符串中提取对象或数组。
• JSON_MODIFY (Transact-SQL) 更改 JSON 字符串中的值。

示例

在以下示例中，查询同时使用 People 表中的关系数据和 JSON 数据（存储在名为 jsonCol 的列中）：

SQL复制

SELECT Name,
    Surname,
    JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
    JSON_VALUE(jsonCol, '$.info.address."Address Line 1"')
        + ' ' + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
    JSON_QUERY(jsonCol, '$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
    AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
    AND STATUS = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

对于应用程序和工具来说，从标量表列中提取的值与从 JSON 列中提取的值没有任何差异。 可以在 Transact-SQL 查询的任何组成部分（包括 WHERE、ORDER BY 或 GROUP BY 子句、窗口聚合，等等）中使用来自 JSON 文本的值。 JSON 函数使用类似于 JavaScript 的语法来引用 JSON 文本内的值。

有关详细信息，请参阅使用内置函数验证、查询和更改 JSON 数据 (SQL Server)、JSON_VALUE (Transact-SQL) 和 JSON_QUERY (Transact-SQL)。

更改 JSON 值

如果必须修改部分 JSON 文本，可以使用 JSON_MODIFY (Transact-SQL) 函数更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串。 以下示例将更新包含 JSON 的变量中的属性的值：

SQL复制

DECLARE @json NVARCHAR(MAX);

SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');

SELECT modifiedJson = @json;

结果

将 JSON 集合转换为行集

在 SQL Server 中查询 JSON 不需要自定义查询语言。 可以使用标准的 T-SQL 查询 JSON 数据。 如果必须基于 JSON 数据创建查询或报表，可以通过调用 OPENJSON 行集函数，轻松地将 JSON 数据转换为行与列。 有关详细信息，请参阅用 OPENJSON 将 JSON 数据转换为行和列 (SQL Server)。

以下示例调用 OPENJSON，并且将 @json 变量中存储的对象数组转换为可使用标准 Transact-SQL SELECT 语句查询的行集：

SQL复制

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
    );

结果

OPENJSON 将 JSON 对象的数组转换为表，其中每个对象表示为一行，键/值对将作为单元返回。 输出遵循下列规则：

• OPENJSON 将 JSON 值转换为 WITH 子句中指定的类型。
• OPENJSON 可以处理规则的键/值对以及分层组织的嵌套对象。
• 不需要返回 JSON 文本中包含的所有字段。
• 如果 JSON 值不存在，OPENJSON 返回 NULL 值。
• 可以选择在类型规范后指定一个路径，以引用嵌套属性或按不同的名称引用属性。
• 路径中可选的 strict 前缀指定 JSON 文本中必须存在指定属性的值。

有关详细信息，请参阅用 OPENJSON 将 JSON 数据转换为行和列 (SQL Server) 和 OPENJSON (Transact-SQL)。

JSON 文档可能包含无法直接映射到标准关系列中的子元素和层次结构数据。 在这种情况下，可通过将父实体与子数组联接，平展 JSON 层次结构。

在下面的示例中，数组中的第二个对象包含表示人员技能的子数组。 每个子对象都可使用附加 OPENJSON 函数调用进行分析：

SQL复制

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"}
]';

SELECT id,
    firstName,
    lastName,
    age,
    dateOfBirth,
    skill
FROM OPENJSON(@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob',
    skills NVARCHAR(MAX) '$.info.skills' AS JSON
    )
OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(8) '$');

首个 OPENJSON 中返回 skills 数组作为原始 JSON 文本片段，并使用 APPLY 运算符传递给其他 OPENJSON 函数。 第二个 OPENJSON 函数分析 JSON 数组并将字符串值返回为单列行集，这一行集将与第一个 OPENJSON 的结果联接。

此查询的结果如下表所示：

结果

OUTER APPLY OPENJSON 联接一级实体和子数组，并返回平展后的结果集。 由于 JOIN，将对每个技能重复第二行。