• 项目

2017 年 4 月

第 32 卷，第 4 期

领先技术 - 在 SQL Server 2016 中执行 JSON 数据查询

作者 Dino Esposito | 2017 年 4 月

Dino Esposito如今大多数软件都会在独立的自治系统之间移动数据，JSON 是在幕后支持数据传输的通用语言。JSON 是 JavaScript 对象表示法的缩写，可通过文本列举对象状态，以便可以轻松执行状态序列化，并跨网将状态从一个系统传输到下一个系统，特别是在异类系统中。

JSON 已成为 Web 的通用语言，而 XML 最终也无法实现这一点。就我个人而言，我并不太相信 JSON 比 XML 更易于读取的说法。相比之下，JSON 是一种比 XML 更紧凑、更轻型的文本格式，可由开发者进行编辑，并可供计算机跨众多软件和硬件平台进行快速分析和理解。

JSON 字符串是纯文本字符串。使用任意关系数据库管理系统（RDBMS，包括 SQL Server）的任何一版，都可以存储字符串，无论字符串的内容布局如何。然而，SQL Server 2016 是首版支持以下操作的 Microsoft 数据库：以 JSON 格式读取现有表格数据，以便将表格数据保存为 JSON，更重要的是可以对 JSON 字符串执行查询，就如同 JSON 内容实际上汇集了各列一样。

有关 SQL Server 2016 中 JSON 函数的结构化综合概述，请参阅 MSDN 文档 (bit.ly/2llab1n)。有关 SQL Server 2016 中 JSON 的精彩概要，还可以参阅 Simple Talk 文章 (bit.ly/26rprwv)。本文更多的是从面向业务的角度介绍 SQL Server 2016 中的 JSON，通俗来说就是举例说明如何在关系暂留层中使用 JSON 数据。

暂留层中的 JSON 数据

以下两个谓词是理解 JSON 用途的关键所在：传输和序列化。因此，JSON 是用于列举软件实体状态的格式，以便能够跨进程空间传输状态，同时确保两端都能很好地理解相应状态。非常好，但这是有关 SQL Server 中 JSON 的专栏，因此也是有关暂留层中 JSON 的专栏。那么，让我们从以下基本问题说开去： 何时会在 SQL Server 中将数据保存为 JSON？

关系数据库表表示的是数量固定的列，每列都有自己的数据类型，如长度可变或固定的字符串、日期、数字、布尔值等。JSON 不是原生数据类型。从数据库角度来看，包含 JSON 数据的 SQL Server 列是纯字符串列。可以将 JSON 数据写入表格列，就像写入常规字符串一样。可以在任何一版 SQL Server 和其他任何 RDBMS 中执行此操作。

从哪里获取最终存储到数据库中的 JSON 字符串呢？ 主要分为以下两种情况： 第一种情况是，这些字符串可能来自 Web 服务或用于传输数据的其他某形式外部终结点（例如，连接的设备或传感器）。第二种情况是，使用 JSON 数据，可以将相关信息轻松组合到一起，从而以单个数据项的形式显示。这通常发生在处理半结构化数据时，如表示要在事件溯源方案中存储的业务事件的数据，或更简单地说是要在事件驱动型业务上下文（如财务、交易、评分、监控、工业自动化和控制等领域的实时系统）中存储的业务事件。在所有这些情况下，存储数据都会规范化为结构化表单，将不同长度和格式的相关信息序列化成单个数据项，以适应关系表的字符串列。

如前所述，可能暂留的 JSON 内容可以来自外部源，也可以通过序列化 C# 对象实例生成：

foreach (var c in countries)
{
  // Serialize the C# object to JSON
  var json = JsonConvert.SerializeObject(c);
  // Save content to the database
  record.JsonColumn = json;
}

还可以使用实体框架 (EF) 将 JSON 数据保存到数据库表的一列中。

SQL Server 2016 在这一点上达到了新高度，允许转换表格行中的 JSON 数据。此功能可以节省代码所需的大量工作和 CPU 周期，因为现在可将原始 JSON 文本推送到数据库，而无需先在应用程序代码中将其分析为 C# 对象，然后再通过 EF 或直接 ADO.NET 调用进行传递。实现这一目标的关键是全新的 OPENJSON 函数：

declare @country nvarchar(max) = '{
  "id" : 101,
  "name": "United States",
  "continent": "North America"
}';
  INSERT INTO Countries
    SELECT * FROM OPENJSON(@country)
    WITH (id int,
      name nvarchar(100),
      continent nvarchar(100))

可以使用此函数插入或更新纯 JSON 文本的常规表格行。使用 WITH 子句，可以将 JSON 属性映射到现有表格列。

事件溯源方案

在我的 2016 年 12 月专栏中，我介绍了事件溯源这种用于存储应用程序历史状态的新兴模式 (msdn.com/magazine/mt790196)。使用事件溯源，可以保存状态变化的每个业务事件，并通过重播过往事件重新生成最新状态，而不是保存最新的已知良好状态。

实现事件溯源的关键是如何有效地保存和检索过往事件。每个事件都是不同的，架构也可能会不同，具体视可用的类型和信息而定。同时，对每种事件类型实现不同的（关系）存储容易出问题，因为事件是异步的，并且可能会影响状态的不同实体和不同细分。如果将它们保留在不同的表中，重新生成状态的成本可能会很高，因为要执行跨表 JOIN。因此，将事件保存为对象是最值得推荐的做法，NoSQL 存储的表现十分出色。可以改用关系数据库执行事件溯源吗？

在任何一版 SQL Server 上将事件保存为 JSON 是可行的，但如果存储了大量事件，则可能无法持续有效地读取 JSON。在 SQL Server 2016 推出原生 JSON 功能后，格局发生了变化，在事件溯源方案中使用 SQL Server 变得可行。不过，如何在数据库表中执行 JSON 数据查询呢？

对 JSON 内容执行数据查询

假设你已成功在规范关系表中创建一个或多个 JSON 数据列。因此，包含基元数据的列和填充了 JSON 数据的列毗邻共存。除非使用 SQL Server 2016 的新函数，否则会将 JSON 列视为纯文本字段，只能使用 T-SQL 字符串和文本指令（如 LIKE、SUBSTRING 和 TRIM）进行查询。出于本文的演示目的，我创建了一个“Countries”列（又细分成几个表格列），以及一个“Serialized”列，其中包含将其他所有数据序列化成 JSON 的记录，如图 1 所示。

包含 JSON 列的示例“Countries”数据库
图 1：包含 JSON 列的示例“Countries”数据库

示例表中的已序列化 JSON 对象如下所示：

{
  "CountryCode":"AD",
  "CountryName":"Andorra",
  "CurrencyCode":"EUR",
  "Population":"84000",
  "Capital":"Andorra la Vella",
  "ContinentName":"Europe",
  "Continent":"EU",
  "AreaInSqKm":"468.0",
  "Languages":"ca",
  "GeonameId":"3041565",
  "Cargo":null

下面的 T-SQL 查询展示了如何只选择居民超过 1 亿的国家。此查询将常规表格列和 JSON 属性混在一起：

SELECT CountryCode,
  CountryName,
  JSON_VALUE(Serialized, '$.Population') AS People
FROM Countries
WHERE ISJSON(Serialized) > 0 AND
  JSON_VALUE(Serialized, '$.Population') > 100000000
ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')

JSON_VALUE 函数需要使用 JSON 列的名称（或设置为 JSON 字符串的局部变量），并提取指定路径后的标量值。如图 2 所示，$ 符号是指已序列化的 JSON 对象的根目录。

JSON 查询结果
图 2：JSON 查询结果

由于 JSON 列已配置为普通的 NVARCHAR 列，不妨使用 ISJSON 函数来检查列的内容是否是真正的 JSON。如果内容是 JSON，此函数会返回正值。

无论选定的属性是哪个，JSON_VALUE 始终返回不超过 4,000 字节的字符串。如果希望返回值更长，应改用 OPENJSON。无论如何，不妨考虑使用 CAST 获取正确类型的值。回到上面的示例，假设你希望获取某国家/地区的居民人数，格式为采用逗号分隔符。（一般来说，最好不这么做，因为在表示层中设置数据格式会提高代码的灵活性。） SQL FORMAT 函数应接收的是数字，如果传递直接 JSON 值，则会生成错误。为了确保代码能正常运行，必须使用显式 CAST：

SELECT CountryCode,
  CountryName,
  FORMAT(CAST(
    JSON_VALUE(Serialized, '$.Population') AS int), 'N0')
    AS People
FROM Countries
WHERE ISJSON(Serialized) > 0 AND
  JSON_VALUE(Serialized,'$.Population') > 100000000
ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')

JSON_VALUE 只能返回一个标量值。如果有一组嵌套对象要提取，必须使用 JSON_QUERY 函数。

执行 JSON 数据查询的效果如何？ 我们来做些测试。

在 SQL Server 2016 中为 JSON 内容编制索引

不言而喻，对数据库中的整个 JSON 字符串执行查询，然后通过专用库（如 Newtonsoft JSON）在内存中分析它，尽管这样做一直可行，但在部分情况下可能并不是有效的做法。是否有效主要取决于数据库中的记录数，以及获取相应格式的数据所需的真正时长。对于应用程序偶尔运行的查询，在内存中处理 JSON 数据可能仍可行。尽管如此，通过 JSON 专用函数执行查询，并让 SQL Server 在内部进行分析一般会稍微加快代码的运行速度。如果为 JSON 数据编制索引，差别会更大。

然而，不得在 JSON 列上创建索引，因为这会为 JSON 值编制索引，使之成为一个字符串。几乎不会查询整个或部分 JSON 字符串。查询已序列化 JSON 对象中的特定属性值反而更为现实。更有效的方法是，根据一个或多个 JSON 属性的值创建一个或多个计算列，然后为这些列编制索引。下面为 T-SQL 查询示例：

-- Add a computed column
ALTER TABLE dbo.Countries
ADD JsonPopulation
AS JSON_VALUE(Serialized, '$.Population')
-- Create an index
CREATE INDEX IX_Countries_JsonPopulation
ON dbo.Countries(JsonPopulation)

再强调一遍，应注意 JSON_VALUE 返回的是 NVARCHAR。因此，除非添加 CAST，否则将会为文本编制索引。

有趣的是，JSON 分析比一些特殊类型（如 XML 和空间类型）的反序列化更快。有关详细信息，可以访问 bit.ly/2kthrrC。综合而言，JSON 分析起码优于提取其他类型的属性。

JSON 和 EF

作为一般说明，SQL Server 2016 中的 JSON 支持主要通过 T-SQL 语法进行公开，因为现在工具相当有限。尤其是 EF 暂不提供任何 JSON 数据查询设施，EF6 中的 SqlQuery 方法和 EF Core 中的 FromSql 除外。不过，这并不意味着无法将 C# 类（如数组）的复杂属性序列化成 JSON 列。有关 EF Core 的精彩教程，请访问 bit.ly/2kVEsam。

总结

SQL Server 2016 引入了一些原生 JSON 功能，这样就可以更有效地对存储为规范行集的 JSON 数据执行查询。这大多发生在 JSON 数据是一些半结构化数据聚合的序列化版本时。为反映一个或多个 JSON 属性值的计算列编制索引绝对有助于提升性能。

JSON 数据存储为纯文本，不被视为特殊类型（如 XML 和空间类型）。不过，这样一来，就正好可以在任意 SQL Server 对象中即时使用 JSON 列。候补清单中的其他复杂类型（如 XML、CLR 和空间类型）却不是这种情况。

在本期专栏中，我侧重于介绍将 JSON 转换成行集的原生支持。不过，SQL Server 2016 也完全支持将行集转换成 JSON 查询，以便你可以编写常规 T-SQL 查询，然后通过 FOR JSON 子句将结果映射到 JSON 对象。有关此功能的详细信息，请访问 bit.ly/2fTKly7。

Dino Esposito*是《Microsoft .NET： 构建面向企业的应用程序》（Microsoft Press，2014 年）和《使用 ASP.NET 构建新型 Web 应用程序》（Microsoft Press，2016 年）的作者。Esposito 是 JetBrains 公司 .NET 和 Android 平台的技术推广专家，经常在全球性行业活动上发表演讲，他在 software2cents.wordpress.com 和 Twitter: @despos.*上分享了他的软件构想。

衷心感谢以下 Microsoft 技术专家对本文的审阅： Jovan Popovic