SQL Server 中的 JSON 数据

2025-09-16

适用于：SQL Server 2016 （13.x）及更高版本Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Microsoft Fabric 预览版中的 SQL 数据库

本文概述了 SQL Server、Azure SQL 数据库、Azure SQL 托管实例、Azure Synapse Analytics 和 Microsoft Fabric 中的 SQL 数据库中的文本数据格式 JSON。

Note

JSON 支持需要数据库兼容性级别 130 或更高版本。

Overview

JSON 是一种流行的文本数据格式，用于在现代 Web 和移动应用程序中交换数据。 JSON 还用于将非结构化数据存储在日志文件或 NoSQL 数据库（如 Microsoft Azure Cosmos DB）中。许多 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 文本的示例：

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

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

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

显示内置 JSON 支持概述的关系图。

SQL Server 2025 更新

SQL Server 2025 （17.x）预览版引入了以下 JSON 增强功能，所有当前都以预览版提供：

关键 JSON 功能

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

JSON 数据类型

Note

数据类型：

适用于具有 SQL Server 2025 或 Always-up-to-date更新策略的 Azure SQL 数据库和 Azure SQL 托管实例。
在 SQL Server 2025 (17.x) 预览版和 Fabric 中的 SQL 数据库预览中。

以本机二进制格式存储 JSON 文档的新 json 数据类型，与 varchar/nvarchar 中存储 JSON 数据相比，具有以下优势：

提高读取效率，因为文档已经过分析
提高写入效率，因为查询可以在不访问整个文档的情况下更新单个值
提高存储效率，针对压缩进行优化
仍与现有代码相兼容

使用本文中所述的 JSON 相同函数仍然是查询 json 数据类型的最有效方法。有关本机 json 数据类型的详细信息，请参阅 JSON 数据类型。

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

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

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

Example

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

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_VALUE和JSON_QUERY验证、查询和更改 JSON 数据。

更改 JSON 值

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

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;

结果集如下。

{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}}

将 JSON 集合转换为行集

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

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

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'
);

结果集如下。

ID	firstName	lastName	age	dateOfBirth
2	John	Smith	25
5	Jane	Smith		2005-11-04T12:00:00

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

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

有关详细信息，请参阅使用 OPENJSON 分析和转换 JSON 数据和OPENJSON。

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

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

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) '$');

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

结果集如下。

ID	firstName	lastName	age	dateOfBirth	技能
2	John	Smith	25
5	Jane	Smith		2005-11-04T12:00:00	SQL
5	Jane	Smith		2005-11-04T12:00:00	C#
5	Jane	Smith		2005-11-04T12:00:00	Azure

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

将 SQL Server 数据转换为 JSON 或导出 JSON

Note

不支持将 Azure Synapse Analytics 数据转换为 JSON，也不支持导出 JSON。

通过将 FOR JSON 子句添加到 SELECT 语句中，可将 SQL Server 数据或 SQL 查询结果的格式设置为 JSON。使用 FOR JSON 委托从客户端应用程序到 SQL Server 的 JSON 输出格式。有关详细信息，请参阅借助 FOR JSON 将查询结果的格式设置为 JSON。

以下示例使用带有 FOR JSON 子句的 PATH 模式：

SELECT id,
    firstName AS "info.name",
    lastName AS "info.surname",
    age,
    dateOfBirth AS dob
FROM People
FOR JSON PATH;

FOR JSON 子句将 SQL 结果的格式设置为 JSON 文本，该格式可提供给识别 JSON 的任何应用。 PATH 选项在 SELECT 子句中使用点分隔的别名在查询结果中嵌套对象。

结果集如下。

[
  {
    "id": 2,
    "info": {
      "name": "John",
      "surname": "Smith"
    },
    "age": 25
  },
  {
    "id": 5,
    "info": {
      "name": "Jane",
      "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
  }
]

有关详细信息，请参阅使用 FOR JSON 和 SELECT - FOR 子句将查询结果格式化为 JSON。

来自聚合的 JSON 数据

JSON 聚合函数支持基于来自 SQL 数据的聚合构造 JSON 对象或数组。

JSON_OBJECTAGG 从 SQL 数据或列的聚合构造 JSON 对象。
JSON_ARRAYAGG 从 SQL 数据或列的聚合构造 JSON 数组。

Note

json 聚合函数JSON_OBJECTAGG和JSON_ARRAYAGG是：

适用于 Azure SQL 数据库、Azure SQL 托管实例（SQL Server 2025 或 Always-up-to-date更新策略）和 Fabric 数据仓库。
处于预览状态的 SQL Server 2025 (17.x)

SQL Server 中 JSON 数据的用例

SQL Server 和 Azure SQL 数据库中的 JSON 支持使你能够将关系概念与 NoSQL 概念结合起来。你可以轻松地将关系数据转换为半结构化数据，反之亦然。但是，JSON 不可替换现有关系模型。以下是一些受益于 SQL Server 和 SQL 数据库中的 JSON 支持的具体用例。

简化复杂的数据模型

考虑使用 JSON 字段代替多个子表来对数据模型进行去规范化。
存储零售和电子商务数据

为了提高灵活性，将具有多种可变属性的产品信息存储在非规范化模型中。
处理日志和遥测数据

使用 Transact-SQL 语言的所有功能加载、查询和分析存储为 JSON 文件的日志数据。
存储半结构化的 IoT 数据

当需要实时分析物联网数据时，将传入的数据直接加载到数据库中，而不是将其暂存到存储位置。
简化 REST API 开发

轻松将数据库中的关系数据转换为支持网站的 REST API 所使用的 JSON 格式。

合并关系数据和 JSON 数据

SQL Server 提供混合模型，用于通过标准 Transact-SQL 语言存储和处理关系数据与 JSON 数据。你可以将 JSON 文档的集合组织到表中，在它们之间建立关系，将表中存储的强类型标量列与 JSON 列中存储的灵活键/值对合并，以及使用完整 Transact SQL 查询一个或多个表中的标量值和 JSON 值。

JSON 文本存储在 varchar 或 nvarchar 列中，索引为纯文本。任何支持文本的 SQL Server 功能或组件均支持 JSON，因此 JSON 和其他 SQL Server 功能之间的交互几乎没有任何约束。你可以将 JSON 存储在内存中或临时表中，并对 JSON 文本应用行级别安全性谓词等。

以下用例说明如何在 SQL Server 中使用内置的 JSON 支持。

在 SQL Server 中存储和索引 JSON 数据

JSON 是文本格式，因此 JSON 文档可以存储在 SQL 数据库中 的 nvarchar 列中。由于所有 SQL Server 子系统都支持 nvarchar 类型，因此可以将 JSON 文档放入具有聚集列存储索引、内存优化表或可以使用 OPENROWSET 或 PolyBase 读取的外部文件中。

要了解有关在 SQL Server 中存储、索引和优化 JSON 数据的详细信息，请参阅以下文章：

将 JSON 文件加载到 SQL Server

你可以将文件中存储的信息格式化为标准 JSON 或行分隔的 JSON。 SQL Server 可以导入 JSON 文件的内容，使用 OPENJSON 或 JSON_VALUE 函数分析内容，并将其加载到表中。

如果 JSON 文档存储在可由 SQL Server 访问的本地文件、共享网络驱动器或 Azure 文件位置，则你可以使用批量导入将 JSON 数据加载到 SQL Server。
如果行分隔的 JSON 文件存储在 Azure Blob 存储或 Hadoop 文件系统中，则你可以用 PolyBase 来加载 JSON 文本，以 Transact-SQL 代码形式分析文本，然后将其加载到表中。

将 JSON 数据导入到 SQL Server 表中

如果必须将 JSON 数据从外部服务加载到 SQL Server，则可以使用 OPENJSON 将数据导入 SQL Server，而不是在应用程序层中分析数据。

在受支持的平台中，使用本机 json 数据类型而不是 nvarchar(max) 来提高性能和存储效率。

DECLARE @jsonVariable NVARCHAR(MAX);

SET @jsonVariable = N'[
  {
    "Order": {
      "Number":"SO43659",
      "Date":"2011-05-31T00:00:00"
    },
    "AccountNumber":"AW29825",
    "Item": {
      "Price":2024.9940,
      "Quantity":1
    }
  },
  {
    "Order": {
      "Number":"SO43661",
      "Date":"2011-06-01T00:00:00"
    },
    "AccountNumber":"AW73565",
    "Item": {
      "Price":2024.9940,
      "Quantity":3
    }
  }
]';

-- INSERT INTO <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, N'$') WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData;

你可以通过外部 REST 服务提供 JSON 变量的内容，然后将这些内容从客户端 JavaScript 框架作为参数发送，或者从外部文件加载这些内容。你可以在 SQL Server 表中轻松插入、更新或合并 JSON 文本中的结果。

使用 SQL 查询分析 JSON 数据

如果必须筛选或聚合 JSON 数据用于报表，可以使用 OPENJSON 将 JSON 转换为关系格式。然后，你可以使用标准 Transact-SQL 和内置函数来准备报表。

SELECT Tab.Id,
    SalesOrderJsonData.Customer,
    SalesOrderJsonData.Date
FROM SalesOrderRecord AS Tab
CROSS APPLY OPENJSON(Tab.json, N'$.Orders.OrdersArray') WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'
ORDER BY JSON_VALUE(Tab.json, '$.Group'),
    Tab.DateModified;

你可以在同一个查询中使用 JSON 文本中的标准表列和值。你可以在 JSON_VALUE(Tab.json, '$.Status') 表达式上添加索引来提高查询的性能。有关详细信息，请参阅索引 JSON 数据。

从格式化为 JSON 的 SQL Server 表中返回数据

如果你的 Web 服务从数据库层提取数据并以 JSON 格式返回数据，或者你具有接受格式化为 JSON 的数据的 JavaScript 框架或库，则可以直接在 SQL 查询中设置 JSON 输出的格式。你可以使用 FOR JSON 将 JSON 格式设置委托给 SQL Server，而非编写代码或者包含一个库来转换表格查询结果并将对象序列化为 JSON 格式。

例如，你可能想要生成符合 OData 规范的 JSON 输出。 Web 服务需要采用以下格式的请求和响应：

请求： /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName
响应： {"@odata.context": "https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity", "ProductID": 1, "ProductName": "Chai"}

此 OData URL 代表针对 ID 为 1 的产品的 ProductID 和 ProductName 列的请求。可以使用 FOR JSON 按 SQL Server 中所需的格式设置输出格式。

SELECT 'https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity' AS '@odata.context',
  ProductID,
  Name as ProductName
FROM Production.Product
WHERE ProductID = 1
FOR JSON AUTO;

此查询的输出是完全符合 OData 规范的 JSON 文本。格式设置和转义由 SQL Server 处理。 SQL Server 还可以将格式查询结果设置为任何格式，如 OData JSON 或 GeoJSON。

使用 AdventureWorks 示例数据库试用内置 JSON 支持

若要获取 AdventureWorks 示例数据库，请至少从 GitHub 下载数据库文件和示例和脚本文件。

将示例数据库还原到 SQL Server 实例后，请提取示例文件，然后从 JSON 文件夹中打开 JSON Sample Queries procedures views and indexes.sql 文件。运行此文件中的脚本，将某些现有数据的格式重新设置为 JSON 数据，对 JSON 数据测试示例查询和报表，为 JSON 数据编制索引，然后导入和导出 JSON。

下面是你可以对该文件中包含的脚本执行的操作：

使现有架构非规范化以创建 JSON 数据的列。

将包含销售订单相关信息的 SalesReasons、SalesOrderDetails、SalesPerson、Customer 和其他表中的信息存储在 SalesOrder_json 表内的 JSON 列中。
将 EmailAddresses 和 PersonPhone 表的信息以 JSON 对象数组的形式存储在 Person_json 表中。

创建查询 JSON 数据的过程和视图。
索引 JSON 数据。为 JSON 属性和全文索引创建索引。
导入和导出 JSON。创建并运行相关过程，将 Person 和 SalesOrder 表的内容导出为JSON 结果，并使用 JSON 输入导入和更新 Person 和 SalesOrder 表。
运行查询示例。运行一些查询，以调用步骤 2 和 4 中创建的存储过程和视图。
清理脚本。如果想要保留步骤 2 和 4 中创建的存储过程和视图，请不要运行此部分。

反馈

此页面是否有帮助？

通过

SQL Server 中的 JSON 数据

Overview

SQL Server 2025 更新

关键 JSON 功能

JSON 数据类型

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

更改 JSON 值

将 JSON 集合转换为行集

将 SQL Server 数据转换为 JSON 或导出 JSON

来自聚合的 JSON 数据

SQL Server 中 JSON 数据的用例

合并关系数据和 JSON 数据

在 SQL Server 中存储和索引 JSON 数据

将 JSON 文件加载到 SQL Server

将 JSON 数据导入到 SQL Server 表中

使用 SQL 查询分析 JSON 数据

从格式化为 JSON 的 SQL Server 表中返回数据

使用 AdventureWorks 示例数据库试用内置 JSON 支持

相关内容

反馈

其他资源