SQL Server 中的 JSON。第一部分
4.97/5 (53投票s)
用于在 SQL Server 中处理 JSON 的支持例程
引言
本文介绍了 TSQL 例程，这些例程在 SQL Server 中提供对 JSON 数据的支持。重点在于性能和灵活性。
背景
来自维基百科：“JSON 或 JavaScript 对象表示法，是一种开放标准格式，使用人类可读的文本来传输由属性-值对组成的数据对象。它主要用于服务器和 Web 应用程序之间传输数据，作为 XML 的替代方案。”
JSON 已广泛用作 XML 的更简单替代方案。在过去的 8 年里，它获得了极大的普及，现在几乎出现在各种场景中：作为数据传输格式、数据存储、定义模式或模板……
2016 年之前的 SQL Server 不包含原生的 JSON 支持，对于那些希望在数据库级别使用 JSON 的用户，必须选择
- 通过 CLR 例程为数据库添加 JSON 支持
- 通过 TSQL 例程添加 JSON 支持
使用 CLR 可提供灵活性和出色的性能，但存在以下问题：
- 需要启用 CLR 集成，这可能（或不）超出数据库开发者的授权范围。
- 需要使用其他语言（通常是 C#）进行开发。
- 部署可能“更棘手”，因为它涉及创建程序集、注册等……
鉴于其普及程度，早期的 TSQL-JSON 工作确实存在。我见过的最全面的一个是由 Phil Factor 完成的（https://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server）。正如他在文章中所述，无论在数据库级别处理 JSON 看起来多么复杂，都有许多原因值得探索。
JSON 存储
在 JSON 中，只有两种结构化数据类型：对象和对象列表。后者的定义很明显：“零个、一个或多个对象的集合”，并依赖于前者。这导致我们对对象的定义：对象是属性的容器。属性只是一个名称，它包含简单值（字符串、数字、布尔值或 NULL）或结构化数据。
以人类可读的形式，JSON 定义了一些语法约定，通过示例可以轻松理解。
{
"firstName": "John",
"lastName": "Smith",
"age": 25,
"address":{
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021"
},
"phoneNumber": [
{"type": "home","number": "212 555-1234"},
{"type": "fax","number": "646 555-4567"}
]
}
JSON 对象与其属性之间只有一个链接，“owns”属性。由于属性可以表示其他对象，因此一个对象表示一个对象层次结构是很直接的。由于只有一个连接路径（“owns”属性），JSON 对象与其属性之间的关系可以用一个简单的层次表来表示。建模此层次结构只需要两个字段：一个用于标识对象和属性，另一个用于定义“owns”属性。
注意：由于我们稍后将使用 JSON 自顶向下解析器，我发现将此链接命名为“parent”而不是“owns”更具直观性。概念上，它们表达的意思相同：如果属性 B 的父对象是对象 A，则对象 A 拥有属性 B。
除了对象-属性关系，某些属性可以包含简单值，因此我们将使用两个附加字段来扩展我们的层次模型：一个用于值类型（字符串、数字等），另一个用于实际值。可以使用表类型在 TSQL 中表达此模型。
CREATE TYPE [dbo].[pjsonData] AS TABLE(
[id] [int] NOT NULL,
[parent] [int] NOT NULL,
[name] [nvarchar](100) NOT NULL,
[kind] [nvarchar](10) NOT NULL,
[value] [nvarchar](max) NOT NULL
)
使用此结构，上面的 JSON 示例将存储为
ID Parent Name Kind Value
-----------------------------------------------------
1 0 OBJECT
2 1 firstName STRING John
3 1 lastName STRING Smith
4 1 age NUMBER 25
5 1 address OBJECT
6 5 streetAddress STRING 21 2nd Street
7 5 city STRING New York
8 5 state STRING NY
9 5 postalCode STRING 10021
10 1 phoneNumber ARRAY
11 10 OBJECT
12 11 type STRING home
13 11 number STRING 212 555-1234
14 10 OBJECT
15 14 type STRING fax
16 14 number STRING 646 555-4567
解析 JSON：json_parse
给定一个表示 JSON 编码数据的 `string`，此存储过程将生成一个存储数据的 `pjsonData` 表。此过程的功能与其他现有解析器非常相似，但实现完全不同。
解析过程称为 `pjsonData`，其用法非常简单。正如预期的那样，它接收一个 JSON 文本 `string`。
select * from json_Parse('{
"firstName": "John",
"lastName": "Smith",
"age": 25,
"address":{
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021"
},
"phoneNumber": [
{"type": "home","number": "212 555-1234"},
{"type": "fax","number": "646 555-4567"}
]
}')
并返回一个 `pJsonData` 表。
生成 JSON：json_toJson
这是前一个存储过程的对应项。此过程为 `pjsonData` 表中的项生成 JSON 字符串表示。同样，通过示例可以更好地理解其工作原理。
首先，我们创建一个 `pjsonData` 表。
declare @data pJsonData
insert into @data select * from json_Parse('[
{"Name": "John Smith" ,
"address":{"streetAddress":
"21 2nd Street","city": "New York"}},
{"Name": "Jane Doe" ,
"address":{"streetAddress": "22 Madison Ave","city": "New York"}},
{"Name": "George Williams",
"address":{"streetAddress": "18 3rd Street","city": "Chicago"}}
]')
这会设置以下内容：
可以通过执行 `dbo.json_toJson(@data pjsonData,@id int)` 来生成 JSON 数据。
其中 `@data` 是一个 `pjsonData` 表，`@id` 是我们希望显示为 JSON 的元素的 ID。所以
请注意，没有 `ID=0` 的对象，执行 `dbo.json_toJson(@data,0)` 将引发错误。
查询 JSON：json_value 和 json_value2
这些过程将查询表达式应用于 JSON 数据，如果找到匹配查询表达式的属性，则返回该属性的值，否则返回 `NULL`。此过程有两个版本：一个版本接收 `string` 格式的 JSON 数据（内部使用 `json_Parse` 进行解析），另一个版本接收 `pJsonData` 格式的 JSON 数据。当同一个 JSON 数据被查询多次时，请使用第二个版本，以避免重复解析。
它们的语法是：
dbo.json_Value(@json_string NVARCHAR(MAX),@query NVARCHAR(MAX))
dbo.json_Value2(@json_data pJsonData,@query NVARCHAR(MAX))
查询是路径表达式，详细说明了从根对象开始到达属性的有效路径。路径表达式使用‘.’ 作为分隔符，并使用索引号或属性名作为路由路径。
declare @json_string nvarchar(max)='[
{"Name": "John Jr" ,
"Family":[
{"relationship": "brother","Name": "Michael"},
{"relationship": "father" ,"Name": "John"},
{"relationship": "mother" ,"Name": "Marge"},
{"relationship": "sister","Name": "Jane"}
]
},
{"Name": "June" ,
"Family":[
{"relationship": "brother","Name": "George"},
{"relationship": "father" ,"Name": "Derek"},
{"relationship": "mother" ,"Name": "Lucy"}
]
}
]'
declare @json_data pJsonData
insert into @json_data select * from json_Parse(@json_string)
可以使用 `json_value` 或 `json_value2` 进行查询：
通过内联 JSON 解析并与序列生成器连接，可以实现极大的灵活性。在下面的示例中，我们正在查询 `people(name,family)` 表。此表是即时生成的，其中 `family` 列包含一个 JSON 数组。
select * from
(
select
people.name
,k.num
,dbo.json_value(people.family,k.num+'.name') [relative]
,dbo.json_value(people.family,k.num+'.relationship') relation
from (values ('John','[ {"relationship": "brother","Name": "Michael"},
{"relationship": "father" ,"Name": "John"},
{"relationship": "mother" ,"Name": "Marge"},
{"relationship": "sister","Name": "Jane"}]'),
('Jane','[ {"relationship": "brother","Name": "George"},
{"relationship": "father" ,"Name": "Derek"},
{"relationship": "mother" ,"Name": "Lucy"}]')) people(name,family)
,(select '0' num union select '1' union select '2' union select '3' union select '4' union select '5') k
) md where md.relation is not null order by name,num
结果是：
实现
此存储过程不是使用正则表达式（*或 SQL 类替代方案：PATINDEX、STUFF、临时字符串表……*），而是实现了一个完整的词法分析器/语法分析器引擎，该引擎会遍历输入的 `string`，并在找到项时将它们写入层次表。这是通过大量使用 TSQL 中实现的 SQL 过程扩展来完成的。这有利有弊。
缺点
- 这种方法离“SQL 的办事方式”相去甚远。代码包含循环、递归……足以让一个纯粹主义者皱眉。
- 无法移植到其他 SQL 方言（PSQL、PL/SQL、SQL PL、PL/pgSQL 等）。
- 支持函数会污染命名空间。
优点
- 执行流程简单易懂（也易于修改和自定义）。
- 字符串的“垃圾回收”被减到最低限度，因此内存开销非常小，从而提高了性能。
为了测试性能，我使用 www.json-generator.com 生成了不同大小的相同示例 JSON 数据。这些数据已被插入到一个表中（jdata），并执行了以下测试：
- 对于 `jData` 表中的每个记录：
- 打印 JSON 数据的大小。
- 使用本文的存储过程解析器和来自 www.simple-talk.com 网站的非存储过程解析器解析 JSON 数据。
- 打印两种情况下生成的 JSON 元素数量（两种解析器应相同）。
- 打印解析所需秒数。
结果是：
结论和未来展望
在数据库级别使用 JSON 数据无疑是一个有效的选择。它提供了极大的灵活性，在某些情况下，它可以成为简化应用程序-数据库资源实现的有效技术：配置、简单的查找或连接表、过滤器甚至动态查询。
这样做有许多优点：
- 在某些情况下减少数据流量。
- 逻辑实现更接近数据库级别（这可能好也可能坏，取决于具体情况）。
- 更简单的数据存储设计。
尽管层次模型强大，并且提供了与其底层关系模型相同的查询可能性，但有两个主要缺点：
- 性能：尽管使用过程技术提高了性能，但仍远不如 C# 版本。在当前版本中，我不建议将其用于大量 JSON 记录或大型 JSON 文件。
- 查询语法相当复杂，不易理解。
在本系列文章的下一部分，我们将通过以下方式进一步开发服务器端 JSON 的使用：
- 通过创建辅助查询来改进语法。
- 使用一些基于 JSON 的技术来提供扩展功能。
- 让我们来看看 CLR 替代方案。