SQL Server 的 JSON

在过去几年中，尽管 XML 仍然被广泛使用，但 JSON 已将自己定位为服务之间的标准数据交换格式。在 SQL Server 2016 中，微软直接在数据库引擎中实现了 JSON 支持，数据操作的能力在每个后续版本中都在提升。

 

 

这个词是 JavaScript Object Notation 的缩写，它以人类可读的键值对的形式表示一种开放标准格式；它是独立于语言的。它通常用于应用程序配置、RESTful Web 服务以及 NoSQL 数据库（如 CouchDB 和 MongoDB）。

 

流行的开发语言，包括 JavaScript，原生支持无需序列化的 JSON 的生成和使用，这在保持自我描述的同时提供了灵活性，而无需 XML 中的架构要求。

 

JSON 基础知识

JSON 文本内容是包含符合 JSON 值语法的代码点的令牌序列。值可以是原始值（字符串、数字、布尔值或空值）或复杂值（对象或数组）。

 

JSON对象被定义为“零个或多个”名为对象成员的键-值对的集合，这些键-值对被写入成对的大括号中。键和值用一个冒号分隔，对象用逗号分隔。键是字符串，值可以是任何原始或复杂数据类型。JSON 数组是由逗号分隔并用方括号括起来的零个或多个值的有序列表。

 

由于 JSON 被设计为尽可能轻量级，因此它仅支持四种原始数据类型——数字（双精度浮点数）、字符串（用双引号括起来的 Unicode 文本）、真/假（必须以小写形式写入的布尔值） ) 和空值。没有专门的“日期”类型——它们被表示为字符串。在 JSON 中，字符串是用引号包裹的序列，除转义字符外，所有字符都必须放在其中。

 

 

 

JSON 基本结构

 

以 JSON 格式获取 SQL Server 数据

当我们开始在 SQL Server 中使用 JSON 时，我们通常首先必须以这种格式检索表格数据。微软首先在 SQL Server 2017 中实现了一个FOR JSON 子句——这个子句可以在本地与 SELECT 语句一起使用，类似于我们用于检索 XML 格式数据的 FOR XML。

 

FOR JSON允许有两种方法可供选择：

 

FOR JSON AUTO - 输出将根据 SELECT 语句结构进行格式化

FOR JSON PATH - 输出将根据用户定义的结构进行格式化，允许您使用嵌套对象和属性

 

无论您选择哪种模型，SQL Server 都将在 SELECT 语句中提取关系数据。它将自动将数据库数据类型转换为 JSON 类型并实现字符转义规则。最后，它将根据显式或隐式定义的格式规则来格式化输出。

 

使用FOR JSON AUTO，输出格式由 SELECT 语句的设计控制。因此，使用这种模式需要一个数据库表或视图。

 

 

我们收到以下错误消息：

 

消息 13600，级别 16，状态 1，第 4 行

 

FOR JSON AUTO 至少需要一张表来生成 JSON 对象。使用 FOR JSON PATH 或添加带有表名的 FROM 子句。

 

现在我们展示 SQL Server 如何自动生成 JSON 数据。首先，它在 Management Studio 中作为输出，然后在文本编辑器中格式化：

 

 

 

原始结果集中的每一行都创建为平面属性结构。如果将其与标准 XML 进行比较，您会看到更少的文本。这是因为表名没有出现在 JSON 输出中。

 

当您开始在 XML 中使用 ELEMENTS 选项而不是默认的 RAW 值时，大小的差异变得很重要。为了证明这一点，我们使用 SELECT 语句来比较 XML 和 JSON 输出的数据长度（以字节为单位）：

 

 

 

从查询结果中可以看出，XML 元素大小比 JSON 大小大 65% 左右。另一方面，当表示为 XML 属性时，JSON 和 XML 大致相同。

 

使用FOR JSON AUTO的输出是具有单级属性的平面结构。如果这不足以满足您的需求，您需要使用FOR JSON PATH扩展。

 

FOR JSON PATH允许您通过创建包装器对象和使用复杂属性来完全控制 JSON 输出。最终结果显示为 JSON 对象数组。此扩展将使用别名/列名来定义输出中的键名。如果别名包含点，它将创建一个嵌套对象。

 

扩展前面的示例，我们希望将FirstName和LastName列显示为新PersonName列的嵌套属性。我们通过添加别名来执行我们嵌套的列并使用点语法来获得正确的输出：

 

 

 

如果我们不更改默认设置，则结果中将不会包含 NULL 值。如果您的语句在一个查询中连接多个表，则输出将是一个平面列表，其中FOR JSON PATH根据定义的列别名嵌套每一列。

 

FOR JSON PATH扩展不需要数据库表，我们可以在下面的例子中：

 

 

 

使用 JSON 进行表格数据转换

要将 JSON 与关系数据一起使用或使用表，您需要使用 JSON 映射此数据并将其导入到数据库表中。

 

如果您使用的是 SQL Server 2016 或更高版本，则可以使用OPENJSON函数。这是添加到数据库引擎的新行集表值函数。它返回一个可用作视图或表的对象。

 

它将 JSON 对象/属性对转换为行/列组合，接受两个输入参数：Expression（基于 UNICODE 的 JSON 文本）和Path（JSON 路径表达式，可选参数，用于指定输入表达式的片段）。

 

如果您的数据库的兼容级别不是 130 或更高，则在尝试使用 OPENJSON 函数时会出现以下异常：

 

Msg 208, Level 16, State 1, Line 78
Invalid object name ‘OPENJSON’.

 

如果您没有为返回的结果指定架构，它将创建一个包含三列的表：

 

键（属性名称或元素索引，列类型为 NOT NULL VARCHAR(4000)）；

值（属性值或元素索引，列类型为 NOT NULL NVARCHAR(MAX)）；

类型（值的 JSON 数据类型，列类型为 TINYINT）。

OPENJSON将返回一个表，其中第一级属性将是行，每一行将是一个 JSON 属性或数组元素。为了演示这一点，我们将使用示例 JSON 数据并将其作为字符串提供以查看输出：

 

 

 

如果输入的数据格式不正确，将显示以下错误：

 

 

 

在这个例子中，我们只返回了一级属性。如果我们希望返回 JSON 文档（对象和数组）的复杂值，我们需要指定一个路径参数。请参阅下一个返回PersonName元素的示例：

 

 

 

同样，如果 JSON 格式不正确，SQL Server 引擎将抛出异常。让我们在同一个 JSON 文本中犯一个故意的错误——我们将省略FirstName旁边的一个引号：

 

 

 

一个常见的问题是以逗号分隔值格式加载数据，我们可以使用OPENJSON来帮助我们解决这个问题：

 

 

 

有一种更简单的方法来处理 JSON 代码——现代技术为我们带来了许多工具。例如，dbForge SQL Complete允许您将此插件添加到 SSMS，然后直接在专用的数据查看器窗口中查看 JSON 代码。

 

结论

因此，我们已经介绍了 JSON 的基础知识及其在 SQL Server 中的用法，并通过示例演示了这些概念。还有更多关于 JSON 处理的高级主题，我们将在下一篇文章中深入探讨它们。

 

Josip Saban

一位经验丰富的数据库开发人员和软件经理，在该领域拥有超过 15 年的经验，无论是在公司环境中还是在初创环境中。Josip 具有很强的解决问题的能力，并且在成功实施战略解决方案以满足不断变化的业务需求方面有着良好的记录，并且能够与利益相关者建立关键关系并进行良好沟通，其中大部分在金融行业。专注于 Microsoft 工具堆栈，特别是 SQL Server，尤其是数据仓库建模和开发。从 2000 版到最新版本。他对数据库架构师和工程管理职位感兴趣，对数据驱动的行业有很强的亲和力。