JSON 在 SQL Server 数据库中能为你做什么？

背景

在本文中，我们将了解我们熟悉的朋友 JSON 在 SQL Server 数据库引擎中的作用。既然今天是 13 号星期五，这也许是谈论这个话题的绝佳时机；不过，我不会谈论 Jason Voorhees 😊。

一个普遍的误解是，SQL Server 只是一个关系型数据库引擎，您应该将高度规范化的数据存储在表中。一些开发人员甚至认为这是一个过时的概念，适用于不遵循 NoSQL 等现代模式的遗留应用程序。事实上，目前经典的纯关系数据库已经不复存在。如果您查看 SQL Server、PostgreSQL、MySQL、Oracle 和其他“经典数据库系统”，您会发现它们支持各种 NoSQL 概念，例如 JSON 数据。拥有某种 NoSQL 支持是所有现代数据库的必备条件，您需要学习如何在应用程序中利用它。

在本文中，我将重点介绍 SQL Server 和 Azure SQL 数据库 – 通用多模型数据库，使您能够将经典关系概念与 JSON、XML 和图形等 NoSQL 概念相结合。与经典的 SQL 和 NoSQL 数据库不同，SQL 数据库不会强迫您在这些概念之间做出选择——它使您能够在同一数据库的不同实体上结合关系模型和非关系模型的最佳特性，以创建最佳数据模型。

在本文中，我将向您展示如何在 SQL Server 和 Azure SQL 数据库（Microsoft 在 Azure 云中托管的 SQL Server 的托管版本）中使用 JSON。我将使用“SQL Server 数据库引擎”这个术语，但请注意，所有内容都适用于本地版本（SQL Server 2016+）和 Azure 云版本（Azure SQL 数据库）。

SQL Server 数据库引擎中的 JSON

SQL Server 数据库引擎使您能够将 JSON 文档存储在数据库中，解析 JSON 文档并将其转换为关系数据（表），或者提取您的关系数据。SQL Server 数据库引擎提供了函数和运算符，使您可以像处理 SQL Server 中的任何其他类型一样处理 JSON，并在任何 T-SQL 查询类型中使用它。

我们有几个 JSON 函数，您可以使用它们从存储在变量或列中的 JSON 文本中提取值，并在任何查询中使用它，就像使用其他列一样。您可以选择 JSON 文本中的值，对其进行筛选，使用 JSON 值对结果进行排序，等等。

SELECT PersonID, FirstName, LastName,
     JSON_VALUE(AdditionalInfo, '$.Title') Title,
     JSON_VALUE(AdditionalInfo, '$.NameStyle') NameStyle,
     JSON_VALUE(AdditionalInfo, '$.PaymentInfo.Salary') Salary,
     JSON_QUERY(AdditionalInfo, '$.Skills') Skills
     EmailAddresses
FROM Person
WHERE JSON_VALUE(AdditionalInfo, '$.Company') = @company
 AND ISJSON(AdditionalInfo) > 0
ORDER BY JSON_VALUE(AdditionalInfo, '$.PaymentInfo.Salary')

一个有趣的优势是，您可以使用 GROUP BY、HAVING、窗口聚合等高级 SQL 运算符构建任何查询来处理 JSON 文档——这在处理 JSON 文档的专用 NoSQL 数据库中并不常见。

OPENJSON 函数可以接受 JSON 文档并将其所有值返回为表格式结果集。如果您需要查询 JSON 文档或将其导入表，这将非常有用。

SET @json =
 N'{ "Title":"Mr",
   "PaymentInfo":{"Salary":1500,"Type":"Weekly"},
   "Company":"AdventureWorks",
   "Skills":["SQL",".Net","C#"] }';

SELECT *
FROM OPENJSON(@json);

有一个 FOR JSON 查询子句，它指示数据库引擎以 JSON 格式返回结果，而不是表格式，还有一组 JSON 函数，它们解析 JSON 文本并从 JSON 文本中返回标量值或表。

SELECT FirstName, LastName
FROM Person
FOR JSON PATH

此查询的结果是 JSON 格式的文本，代表查询结果的行。

您可以在我之前的 Friday 13th 文章中找到有关 SQL Server 中 JSON 支持的更多信息。

在本文中，我将详细讨论用例，展示何时以及如何在数据库中使用 JSON。

定制数据模型的复杂性

在专用数据库（关系型或 NoSQL）中，您通常需要做出设计选择来表示您的数据，并且以后很难更改此决定。

经典关系数据库强制您创建高度规范化的数据模型，其中高层概念，如 Customer、Order 或 Invoice，在物理上与其相关实体（如 Customer emails、Customer phones、Product parts、Order Line Items、Invoice tags 等）分离。规范化模型需要实体的高度粒度，因为它们旨在支持大量独立事务，这些事务同时更新概念实体及其各个部分。这很好，因为将概念实体分解为单独的表可以使每个事务更新自己的表而不影响其他事务。这种方法的缺点是，您需要从不同的表中检索信息才能重构一个复杂的实体及其属性，并且如果事务必须原子地更新多个表，则存在锁甚至死锁的风险。

经典文档数据库（NoSQL）将依赖实体合并到主要的で概念实体中，作为每个主要实体的聚合部分，包含序列化的集合和其他复杂对象。这样，您只需要一次查找即可检索或更新实体及其所有相关数据。但是，如果频繁更新相关数据，此模型就不合适了。此外，如果有人要求您创建一个报告，对文档数据进行分组聚合和复杂计算，或者执行使用多个不同集合的更复杂查询，您可能会感到痛苦。

理想的模型取决于您的数据和工作负载的性质，因此在选择规范化和反规范化之间时，您需要考虑数据的用途。现实情况是，在某些情况下，您可能需要以关系/规范化结构组织数据，但对于某些信息，您可能希望利用一些 NoSQL 概念。支持关系型和 NoSQL 概念的现代多模型数据库，如 SQL Server 和 Azure SQL 数据库，使您能够做到这一点。

下图显示了使用 JSON 集合表示依赖实体的规范化模型和等效的反规范化模型

在左侧，您可以看到一个经典的规范化模式，它很好并且设计得很完善。然而，在某些情况下，规范化模型可能会增加复杂性

1. 如果您需要返回或更新整个 Customer、Order 或 Invoice 对象，您将需要运行几个查询。这不仅仅是编程上的不便——对于每个逻辑对象，您都需要一次逻辑读取/写入主实体，以及至少一次逻辑读取每个依赖实体集合。
2. 如果您想使用事务性复制或其他导出方法将数据迁移到其他系统，您将需要设置复制并从多个表中导出数据，处理某些表失败时的错误等等。

许多开发人员和架构师都倾向于切换到 NoSQL，在那里他们将拥有三个集合（Customer、Order、Invoice），每次逻辑实体只需一次读/写操作，导出和复制更简单。但是，在这种情况下，您会丢失关系数据库的一些固有优势，例如引用完整性（数据库确保您不会出现像 Invoice 没有 Customer 这样的脏数据），并且在大多数 NoSQL 数据库中，创建跨集合查询并不容易。

SQL Server 数据库引擎使您能够在适当的情况下混合使用这些概念，并拥有标准的关系表集合，包含外键和引用完整性，以及复杂数据，如列表、数组、集合和字典，这些数据格式化为 JSON 或 XML、空间数据，甚至表之间的图形关系。SQL Server 和 Azure SQL 数据库使您能够微调数据模型的设计，并根据您的用例使用最合适的数据结构。

如果您拥有不经常更新相关数据的模型，您可以将相关实体作为 JSON 格式的子集合存储在表单元格中，并将 NoSQL 设计的简洁性与 SQL 查询能力相结合。

最重要的事情之一是，您可以更改您的设计，而无需从关系系统迁移到 NoSQL 或反之。如果您发现您的 JSON 集合经常更新，并且将其表示为子表会更好，您可以使用 OPENJSON 函数，该函数解析存储在某个表单元格中的 JSON 集合，将其转换为表格式并加载到子表中。同样，如果您发现您的子表不经常更新，但您需要额外的查询始终与主表一起读取它，您可以使用 FOR JSON 对其进行反规范化，并将其内容放入主表中的某个列中。这样，您就可以完全灵活地向前和向后迁移，并根据您的需求定制模型。

在数据库中构建您的 ViewModel

高度规范化的数据库模型中的一个主要痛点是，有时您需要执行 10 个查询才能从不同的表中收集信息，以收集重建单个实体所需的所有信息。让我们看下面一个表示人员及其电子邮件地址和电话号码的简单数据库模式

如果您需要获取有关该人员的信息，您将需要发送一个查询来读取 Person 行，另一个查询使用 PersonID 来获取电子邮件地址，第三个查询通过连接 PersonPhone 和 PersonPhoneNumberType 来获取电话号码。如果您正在搜索人员并为满足搜索条件的每个人获取相关信息，查询可能会变得复杂。即使您由于某些 ORM（它会将您的 LINQ/Lazy loading 操作转换为查询）看不到这有多复杂，当您看到执行此操作生成的 SQL 查询集时，您可能会感到惊讶。

使用 JSON 和反规范化，您可以获取所需的 People 行，以及每行集合中有关相关电子邮件和电话的所有相关信息

[{“EmailAddress”:
”stephen0@adventure-works.com”}
,{“EmailAddress”:
”stephen.jiang@outlook.com”}]

[{“PhoneNumber”:”112-555-6207″,
”PhoneNumberType”:”Work”},
{“PhoneNumber”:”238-555-0197″,
”PhoneNumberType”:”Cell”},
{“PhoneNumber”:”817-555-1797″,
”PhoneNumberType”:”Home”}] 

[{“EmailAddress”:
”johnf@adventure-works.com”}]

[{“PhoneNumber”:”112-555-6207″,
”PhoneNumberType”:”Work”}]

您可以使用 JSON 函数从关系模型中获取此结果。接受这些结果的外部应用程序可以通过将 JSON 格式的集合反序列化为对象集合（例如，如果您在应用程序层使用 .NET，可以使用 Json.NET - Newtonsoft）轻松地将 SQL 结果“物化”为 DTO 对象。看看这篇文章，了解如何 序列化和反序列化 Entity Framework 返回的 JSON 集合。

主要问题是如何为依赖的子对象集合返回 JSON 集合？

一种选择是反规范化电子邮件和电话，并将它们作为 Person 表中的附加列存储。如果您知道电子邮件和电话不经常更新，并且您知道您将始终一次性读取电子邮件/电话集合，那么您应该使用此方法。在这种情况下，您可以使用类似以下的查询将表迁移到列

UPDATE Person
SET Emails = (SELECT EmailAddress 
               FROM EmailAddresses e 
               WHERE e. BusinessEntityID = Person. BusinessEntityID
               FOR JSON PATH)

现在，如果您从 Person 表中选择行，您将获得电子邮件和电话的 JSON 集合，就像其他任何列一样。

另一种选择是保留电子邮件和电话在单独的表中，并在查询时使用列子查询（SQL Server 术语中的用户定义表达式）将它们“附加”到每个 Person 行。

SELECT     Person.Person.BusinessEntityID AS ID, Person.Person.FirstName, _
           Person.Person.LastName,
      Emails = (SELECT Person.EmailAddress.EmailAddress
                FROM Person.EmailAddress
                WHERE Person.Person.BusinessEntityID = Person.EmailAddress.BusinessEntityID
                FOR JSON PATH),
      Phones = (SELECT Person.PersonPhone.PhoneNumber, _
                Person.PhoneNumberType.Name AS PhoneNumberType
                FROM  Person.PersonPhone
                      INNER JOIN Person.PhoneNumberType
                      ON Person.PersonPhone.PhoneNumberTypeID = _
                         Person.PhoneNumberType.PhoneNumberTypeID
                WHERE Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID
                FOR JSON PATH)
FROM         Person.Person
WHERE <<condition>>

您的应用程序接收此查询结果时，将获得每人一行以及电话和电子邮件的集合，而不会察觉到底层存在关系表结构。

如果您将此结果用于某个期望 JSON 作为响应的单页应用程序，您可以在主查询上使用 FOR JSON 子句，将所有内容格式化为一个大的 JSON 文本，然后将其发送到客户端。

另一个可以利用 JSON 的有趣案例是减少返回数据库数据的代码复杂性。想象一下，您有一个报告或一些应该在浏览器中显示的数据。

通常，您需要使用某些 ORM 将数据从表中读取到模型对象中，然后您有一些视图模型类，用于将模型转换为客户端应用程序理解的数据结构。然后，您有一些组件将这些视图模型转换为 JSON 响应，并将其发送到客户端。

使用 SQL 数据库中的 FOR JSON 子句，您可以执行任何查询，要求 SQL Server 以 JSON 格式返回结果，并直接将结果发送到您的客户端。这可能会极大地减少您需要编写的代码量以及对体系结构中每个层的测试量，因为您只需要一个带 FOR JSON 子句的 SQL 查询即可处理每个 REST API 调用。

简化数据驱动型 REST API 的开发

我们需要记住，JSON 是一个 Web 概念，而不是某种数据库存储优化格式。它非常适合使用 AJAX 请求与 Web 服务器通信的 JavaScript 代码。这是每个现代 Web 应用程序中通信的首选模式。

单页应用程序是构建 Web 应用程序的现代架构。它们基于丰富的 JavaScript 应用程序框架和库，并包含大量在 Web 浏览器中执行的客户端 JavaScript 代码。单页应用程序通过 HTTP/AJAX 请求与后端系统通信，这些请求检索或发送 JSON 消息到某个 REST API，该 API 在数据库中获取或存储数据。Web 开发中的主要困难之一是将关系数据转换为 JSON 消息，反之亦然。在经典的 REST API 中，您需要从数据库获取数据并将其转换为 JSON 消息，然后发送到您的单页应用程序。

FOR JSON 子句和 OPENJSON 函数使您能够轻松地将表中存储的关系数据转换为 JSON，并将来自单页应用程序的 JSON 文本解析到 SQL 数据库中。这可能是构建 REST API 的理想工具，用于与使用 JSON 消息通信的单页应用程序进行后端系统通信。

使用 JSON 运算符和函数，您可以轻松地从 SQL 表中获取数据，或者使用 T-SQL 创建复杂的报告，将结果格式化为 JSON 文本，并让 REST API 将此文本返回给客户端应用程序。

如果您使用 ASP.NET 并且需要创建一个 REST API 端点来在数据库中执行查询并以 JSON 响应返回结果，您可以在控制器中的操作方法中放置类似以下内容：

public async Task Report()  {

  await db.Sql("SELECT color AS x, AVG(price) AS y FROM product GROUP BY color FOR JSON PATH")
          .Stream(Response.Body);

}

此操作使用来自 Belgrade SqlClient Data Access library 的辅助函数，该函数直接在您的数据库中执行 T-SQL 查询，并将结果流式传输到您的输出流（在本例中为 HttpResponse 正文）。

如果您使用 Node.JS 和 express，您可以使用类似的方式：

router.get('/Report', function (req, res) {

    req.sql("select Color AS x, AVG(price) AS y FROM Product GROUP BY color FOR JSON PATH")
       .into(res, '[]');

});

有关为单页应用程序构建 REST API 的更多详细信息，请参阅这篇 文章，以及一个示例，展示了如何为 Angular 应用程序创建 REST API 后端，请参阅 这里。

日志数据分析

应用程序和设备会生成大量的日志，其中包含有关应用程序中发生的情况、错误详情等信息。在许多情况下，日志数据以 JSON 格式表示。您可以将 JSON 日志存储在廉价的日志存储（如文件）中，但如果您想分析纯文本日志，您可能需要处理复杂的解析和正则表达式。CSV 或 TSV 易于解析；但是，它们需要固定数量的列。JSON 在许多情况下被选择为日志格式，并在解析工具的可用性和结构灵活性之间进行了权衡。

遥测数据最终会进入日志文件，这些文件存储在文件系统、Azure Blob 存储、Hadoop 文件系统等上。这些存储系统非常适合存储日志数据，因为它们的成本低于 Azure SQL 或 Cosmos DB，并且通常将日志条目写入普通文件的速度比写入关系型数据库甚至 NoSQL 数据库（涉及额外的处理开销）更快。但是，它们代表了冷存储，查询和分析能力非常有限。

如果您需要热日志数据，并且可以快速轻松地对其进行分析，SQL Server 数据库引擎使您能够从冷存储加载 JSON 格式的日志文件，并使用标准的 T-SQL 语言查询和分析日志数据。

首先，您需要创建一个表来存储您的热日志

CREATE TABLE WebLogs (
    _id bigint IDENTITY,
    data nvarchar(max),
    INDEX cci CLUSTERED COLUMNSTORE
)

在这个简化的示例中，我有一个相当于经典 NoSQL 集合的表，其中有一个数据列，包含单个日志记录。您还可以添加更多列，如类型、日志条目添加的日期，但这取决于您的用例。添加关系列有助于通过这些列进行过滤，因为这可能比通过 JSON 列中的属性进行过滤快得多。

请注意表定义中的 CLUSTERED COLUMNSTORE 索引——这很可能是您应该放在包含大量 JSON 数据的表上的索引，因为它可以在数据上执行高压缩，并将存储需求减少多达 20 倍。此外，具有 COLUMNSTORE 索引的表经过优化，适用于分析查询，因此如果您需要分析日志，它们可能是个不错的选择。

如果您将日志存储在文件共享或 Azure Blob 存储上，您可以使用 BULK IMPORT SQL 命令将文件直接导入到 SQL 数据库的目标表中。

BULK INSERT WebLogs
FROM 'data/log-2018-07-13.json'
WITH ( DATA_SOURCE = 'MyAzureBlobStorageWebLogs');

在此示例中，日志条目作为每行一个 JSON 日志条目导入。

将数据加载到 SQL 表后，您可以使用标准的 T-SQL 语言对其进行分析。下面是一个 T-SQL 查询示例，用于查找在某个日期范围内访问系统的 Top 10 IP 地址。

SELECT TOP 10 JSON_VALUE(data, '$.ip'), COUNT(*)
FROM WebLogs
WHERE CAST(JSON_VALUE(data, '$.date') AS date) BETWEEN @start AND @end
GROUP BY JSON_VALUE(data, '$.ip')
ORDER BY COUNT(*) DESC

SQL Server 和 Azure SQL 数据库是加载冷存储中的热日志数据以使用 T-SQL 和广泛的 SQL Server 和 Azure SQL 数据库工具提供快速丰富分析的不错选择。

物联网数据分析

物联网（IoT）可以看作是日志数据的一个特殊案例，您需要存储和分析来自各种设备的大量遥测数据。IoT 用例在数据摄取、处理和存储方面通常共享一些模式。首先，这些系统需要摄取来自不同本地的设备传感器的突发数据。接下来，这些系统处理和分析流式数据以获得实时洞察。数据存储在某些持久存储中以供进一步分析。

在下图中，您可以看到一个 IoT 管道，其中 IoT 设备将数据发送到 Azure IoT Hub，最终 IoT 数据落入 Azure SQL 数据库。

在 IoT 管道中，我们可以注意到从数据源到存储的两个主要路径

1. 热路径，其中来自传感器的最新数据需要尽快放入存储层。在此示例中，事件通过 Azure Event Hub 和 Azure Functions 发送到 Azure SQL 数据库（假设这是在 Azure 云平台上实现的 Lambda 架构）。
2. 冷路径，其中 IoT 数据存储在某些低成本存储层，如 Azure Blob 存储。冷数据稍后可以加载到某个系统中进行分析以查询数据。

如果您在 IoT 解决方案中使用 Azure 云，有多种选项可用于存储 IoT 数据，例如

1. Azure Blob 存储，如果您需要以低成本存储大量冷数据。存储的 IoT 数据可以按需加载到某个 Azure SQL 数据库或 Azure SQL Datawarehouse 中，以使用标准查询进行分析，或使用 Azure 机器学习服务进行分析。
2. Azure SQL 数据库或 Azure SQL Datawarehouse，如果您可以解析传入数据并将其存储为关系格式。
3. Azure SQL 数据库，如果您需要存储 JSON 格式的半结构化数据，并且您需要将 IoT 信息与一些现有的关系数据进行关联。
4. Azure SQL 数据库或 Azure Cosmos DB，如果您需要存储 JSON 格式的半结构化数据。

如果您将 IoT 数据存储在 Azure SQL 数据库中，您可以使用内置的本机函数来解析和分析从设备收集的 IoT 数据。

此外，您可以轻松构建遥测管道，其中包含 Azure Event Hub、Azure Event Grid 或 Azure IoT Hub，它们接收来自您的 IoT 设备的数据，Azure Function，您可以在其中放置一个简单的代码来接收来自 Azure Event Hub 的 JSON 消息并调用接受此 JSON 消息的 SQL 过程，最后是 SQL 过程，该过程要么将原始 JSON 消息存储以供处理，要么在 JSON 消息进入数据库时解析它们并将字段存储到列中。

例如，以下代码示例显示了一个从 Azure Event Hub 读取 JSON 消息并将其发送到 Azure SQL 数据库的 Azure Function

using System.Configuration;
using System.Data.SqlClient;
using Belgrade.SqlClient.SqlDb;

public static async void Run(string myEventHubMessage, TraceWriter log)
{
    if(String.IsNullOrWhiteSpace(myEventHubMessage))
        return;
    try{
        string ConnString = ConfigurationManager.ConnectionStrings
                            ["azure-db-connection"].ConnectionString;
        var cmd = new Command(ConnString);
        var sqlCmd = new SqlCommand("ImportEvents");
        sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
        sqlCmd.Parameters.AddWithValue("Events", myEventHubMessage);
        await cmd.ExecuteNonQuery(sqlCmd);
    } catch (Exception ex) {
        log.Error($"C# Event Hub trigger function exception: {ex.Message}");
    }
}

这是一个相当简单的函数，可能是将 Event Hub 服务和数据库存储绑定的唯一必要项。当此函数将 JSON 消息作为 myEventHubMessage 变量接收时，它只是将其传递给名为 ImportEvents 的 SQL 过程。

在过程中，您可以选择是将 JSON 消息原样存储在表行中，还是对其进行解析并将其分解为列。下面的简单实现只是将提供的值插入表中。

CREATE PROCEDURE dbo.ImportEvents  @Events NVARCHAR(MAX)
AS  BEGIN
    INSERT INTO dbo.Events (Data)
        VALUES( @Events);
END

另一个实现可能会解析传入的 JSON 消息，提取属性并将其存储到列中

CREATE PROCEDURE dbo.ImportEvents   @Events NVARCHAR(MAX)
AS  BEGIN 
    INSERT INTO dbo.Events 
    SELECT *
       FROM OPENJSON(@Events) 
       WITH ([eventId] int, [deviceId] int, [value] int, [timestamp] datetime2(7)))
END 

这种方法的优点是您可以轻松地更改用于存储 IoT 事件的模式的实现，而不会影响管道的其余部分。

您可以在这篇 文章中找到有关此 IoT 场景的更多信息。

关注点

关系数据库中的 JSON 支持有时可能会打开您关系数据库的 NoSQL 世界。如果您曾经遇到过复杂模式问题，并想尝试用 NoSQL 数据库解决问题，但又无法轻松迁移系统，那么 SQL 数据库中的 JSON 支持可能会使您能够结合 NoSQL 数据库和关系世界的最佳概念。

后续步骤

如果您想了解更多关于 SQL Server/Azure SQL 数据库中的 JSON 支持，我推荐以下资源：

• SQL Server/JSON 文档——您可以在其中找到 SQL Server 数据库引擎和 Azure SQL 数据库中 JSON 函数的详细解释。
• SQL Server/JSON 博客文章——您可以在其中找到描述 SQL 数据库中 JSON 不同用例的各种文章。
• SQL Server/JSON 示例存储库包含各种应用程序和示例，展示了如何在单页、JQuery 驱动的、Angular、React 应用程序中使用 JSON。

历史

• 2018 年 7 月 13 日：初始版本