13 号星期五 - JSON 即将登陆 SQL Server

Jovan Popovic(MSFT)

4.98/5 (51投票s)

2015年11月13日

CPOL

17分钟阅读

70888

概述新 SQL Server 中对 JSON 文本处理的内置支持。

介绍

今年秋天，J(a)SON 将进入新的 SQL Server！

如果您期待的是《13号星期五》系列的新电影，可能会感到失望——Jason Voorhees 不会进入 SQL Server。但是，JavaScript 对象表示法（JSON）支持将进入新的 SQL Server。以下示例展示了格式化为 JSON 的文本：

{
    "info":{  
      "type":1,
      "address":{ "town":"Bristol","country":"England" },
      "tags":["Sport", "Water polo"]
   },
   "type":"Basic"
}

JSON 是一种简单的人类可读格式，可以包含复杂的数据结构，如键值对、对象、数组等。新的 SQL Server 使您能够处理 JSON 文本。

您可能知道，关系型数据库和 NoSQL 概念之间正在进行一场“战争”。这两种方法都有各自的优缺点。

关系型数据库是传统的数据存储机制。直观的 SQL 语言、丰富的查询功能、事务、强大的索引以及对更新的优化是关系型模型最重要的优势。然而，缺点是您可能会最终得到复杂的数据库架构和大量的 JOIN 来检索数据。

NoSQL 提供了不同的概念——复杂的结构被放置在实体集合中，您可以一次读取操作获取所需的一切，或者一次写入操作插入复杂结构。缺点是，有时您想将信息组织在不同的集合中，然后会发现很难从两个集合中 `JOIN` 实体。

通过新的 SQL Server，您可以选择这两种概念，并利用两者的优点。在数据模型中，您可以选择何时使用传统的结构化关系，何时引入 NoSQL 概念。

如果您有父/子关系，其中相关的子信息不经常更改，并且您需要在不进行额外 JOIN 的情况下将子记录与父记录一起读取，您可以将子记录存储在父表中的 JSON 数组里。
如果您有经常更新的信息，则应将其存储在常规表中。常规表是处理经常更改数据的最佳选择。

SQL Server 2016 和 Azure SQL 数据库将内置对处理 JSON 格式文本的支持。首批功能——将 `SELECT` 查询结果格式化为 JSON 文本——已在 SQL Server 2016 CTP2 中可用，而在 SQL Server 2016 CTP3 中则添加了用于解析 JSON 文本的函数。一些额外的增强功能将在 RTM 版本中提供。

在本文中，我们将了解此版本将包含哪些内容。

背景

SQL Server 中的 JSON 支持是在 Microsoft Connect 上排名靠前的功能请求之一，获得了超过 1000 票。在 SQL Server 2016 中，将添加对 JSON 文本处理的内置支持。SQL Server 2016 中 JSON 功能的高级概述如下图所示：

SQL Server 将提供一组内置函数，使您能够验证 JSON 文本是否格式正确，在 JSON 文本中根据类似 JavaScript 的路径查找值，查找 JSON 片段等。

这些是允许您解析 JSON 文本的基本函数，无需 CLR 或复杂的正则表达式。

如果您需要更高级的查询，可以使用 `OPENJSON` 表值函数将 JSON 文本转换为标准表。此函数遍历 JSON 文本中的对象数组，并为每个对象创建一个表行。JSON 中的键：值对被转换为标题：单元格对。当您将 JSON 文本转换为表时，可以使用任何标准的 SQL Server 函数和运算符（GROUP BY、聚合等）。

最后，如果您有想要转换为 JSON 文本的表数据，那么 `SELECT` 语句中有一个新的 `FOR JSON` 子句。`FOR JSON` 子句会将结果集从表中格式化为 JSON 对象数组。此选项获取标题：单元格值，并将其转换为 JSON 文本中的键：值对。

入门

在本节中，我们将简要概述现有功能。JSON 以文本形式存在，因此您可以将其存储在标准的文本变量中。

DECLARE @json NVARCHAR(4000)
SET @json = 
N'{
    "info":{  
      "type":1,
      "address":{  
        "town":"Bristol",
        "county":"Avon",
        "country":"England"
      },
      "tags":["Sport", "Water polo"]
   },
   "type":"Basic"
}'

我将在以下示例中使用此变量。

`JSON_VALUE` 函数允许您在 JSON 中根据类似 JavaScript 的路径获取值。

SELECT
  JSON_VALUE(@json, '$.type') a,
  JSON_VALUE(@json, '$.info.type') b,
  JSON_VALUE(@json, '$.info.address.town') c,
  JSON_VALUE(@json, '$.info.tags[0]') d

a	b	c	d
`Basic`	`1`	`布里斯托`	`体育`

`JSON_QUERY` 函数允许您在 JSON 文本中根据类似 JavaScript 的路径获取对象或数组。

SELECT
  JSON_QUERY(@json, '$') as [object],
  JSON_QUERY(@json, '$.info') as info,
  JSON_QUERY(@json, '$.info.address') as address,
  JSON_QUERY(@json, '$.info.tags') as tags

此查询将返回以下结果：

object	信息	address	标签
`{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }`	`{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }`	`{ "town":"Bristol", "county":"Avon", "country":"England" }`	`["Sport", "Water polo"]`

`ISJSON` 函数将在 JSON 文本格式正确时返回 1。

SELECT
  ISJSON(@json),
  ISJSON(JSON_QUERY(@json, '$.info')),
  ISJSON(JSON_VALUE(@json, '$.info.type'))

前两列返回 1，而第三列返回 0，因为 `$.info.type` 属性的值是数字而不是有效的 JSON。

`OPENJSON` 将使用 JSON 数组中的元素或 JSON 对象中的键：值对返回一组行。

SELECT [key] as property, value
FROM OPENJSON(@json, '$.info.address')

SELECT [key] as i, value
FROM OPENJSON(@json, '$.info.tags')

还有一个 `OPENJSON` 函数版本，它会从对象中提取元素并将其强转换为指定类型。

SELECT *
FROM OPENJSON(@json, '$.info')
WITH ( type int,
       town nvarchar(50) '$.address.town',
       country nvarchar(50) '$.address.country'
     )

最后，`FOR JSON` 允许您将 `SELECT` 查询结果格式化为 JSON 文本。

SELECT FirstName, LastName
FROM Person
FOR JSON PATH

在接下来的部分中，我们将了解如何在 SQL Server 中使用这些函数。

在 SQL Server 数据库中存储 JSON

如前所述，SQL Server 增加了处理 JSON 文本的能力。因此，您将像普通文本（`NVARCHAR` 列）一样将其存储在 SQL Server 中。如果您需要简单的 JSON 值集合，就像在 Azure `DocumentDB` 或 `MongoDB` 中一样，您可以创建一个简单的两列表。

CREATE TABLE Person
( _id int identity constraint PK_JSON_ID primary key,
  value nvarchar(max) 
       CONSTRAINT [Content should be formatted as JSON]
                 CHECK (ISJSON(value)>0)
)

您可以创建标准的检查约束，并使用 `ISJSON` 函数来确保存储在 JSON 列中的文本是有效的。如果您确信您的文本将格式化为 JSON，则可以关闭检查约束。

但是，SQL Server 更像是一个混合引擎，您可以结合关系型和 JSON 数据。以下示例展示了一个简单的 `Person` 表，其中包含标准列和作为 JSON 文本存储的变量数据。

CREATE TABLE Person(
    PersonID int IDENTITY PRIMARY KEY,
    FirstName dbo.Name NOT NULL,
    LastName dbo.Name NOT NULL,
    AdditionalInfo nvarchar(max) NULL,
    PhoneNumbers nvarchar(max) NULL,
    EmailAddresses nvarchar(max) NULL
        CONSTRAINT [Email addresses must be formatted as JSON array]
            CHECK  (isjson(EmailAddresses)>0)
)

如果您将此结构与 `AdventureWorks` 数据库中的 `Person` 表进行比较，您会发现您不需要为每个字符串或对象数组创建额外的表（`PersonPhoneNumbers`、`PersonEmailAddresses` 等）、外键和索引。在这里，我将电话号码和电子邮件地址与某些人存储为 JSON 数组。此外，我还可以将任何其他可变信息放在 `AdditionInfo` 列中。

现在，一个重要的问题——为什么您要将地址和电话存储为 JSON 而不是单独的表？在某些情况下，您可能会提高查询的性能。想象一个标准的 SQL 查询，它读取人员、电话、电子邮件信息。您需要扫描三个表并使用两个 JOIN 来收集数据。通过将信息反规范化到一个表中，您只需要扫描一个表。

另一个用例是导入。如果您将人员、电话和电子邮件导入到单个表中，这将比导入到人员表中、获取生成的 ID 标识符、使用该标识符导入电话和电子邮件，然后为每个人重复此过程更快。

缺点是如果您需要查询单个表，查询性能可能会受到影响。引用列将比引用 JSON 文本中的属性更快，因此仅当您想在父行中获取相关表的列时才使用此方法。

在接下来的部分中，我们将了解如何在 `Person` 表中查询 JSON 文本。

查询

信息被格式化为 JSON 并不意味着它们丢失了。SQL Server 并未忽略 JSON 数据。当前可用的函数有：

`ISJSON` - 验证文本是否格式化为 JSON。
`JSON_VALUE` - 在类似 JavaScript 的路径上从 JSON 文本返回标量值。
`JSON_QUERY` - 从 JSON 文本返回片段（例如，JSON 数组或子对象）。

您可以使用 `JSON_VALUE` 和 `JSON_QUERY` 函数从 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')

您可以在查询的任何部分（如 `WHERE` 子句、`ORDER BY`、`GROUP BY`、`HAVING` 等）中使用 JSON 文本中的属性以及常规列。请注意，所有 JSON 内置函数都接受类似 JavaScript 的路径，该路径引用 JSON 文本中的某个属性。JSON 路径以 `$` 开头，代表函数的第一个参数，后跟引用 JSON 字段的常规 JavaScript 语法。如果您的键包含非字母数字字符，则应将其放在双引号中，例如 `$."Sales Person"."First Name"`。

OPENJSON

JSON 文本使用新的 `OPENJSON` 函数进行解析。在最简单的形式中，`OPENJSON` 返回 JSON 对象中的所有键：值对。

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

SELECT * 
FROM OPENJSON(@json)

此函数将返回所有键（`Salary`、`PaymentInfo`、`Company`、`Skills`）及其值。

键	value
`标题`	`先生`
`PaymentInfo`	`{"Salary":1500,"Type":"Weekly"}`
`Company`	`AdventureWorks`
`技能`	`["SQL",".Net","C#"]`

`OPENJSON` 函数可以解析任何 JSON 对象并返回强类型行，可用于任何查询，例如：

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

SELECT Title, Salary, Company 
FROM OPENJSON(@json)
     WITH (Title nvarchar(20), Salary int '$.PaymentInfo.Salary', Company  nvarchar(20))

`OPENJSON` 函数将解析 JSON 对象，使用 `WITH` 子句中定义的“列名”匹配 JSON 属性，并返回具有强类型列的等效行。如果某个列名与 JSON 中的属性路径不匹配，您可以在类型后添加引用路径（参见 `'$.Payment.Info'` 示例）。

标题	薪资	Company
`先生`	`1500`	`AdventureWorks`

这对于您拥有复杂的嵌套对象很有用。

此外，`OPENJSON` 还可以解析 JSON 数组。例如，我们可以从 JSON 对象中的 `Skills` 数组返回值。

SET @json = 
N'{"Title":"Mr","PaymentInfo":{"Salary":1500,
"Type":"Weekly"},"Company":"AdventureWorks",
   "Skills":["SQL",".Net","C#"]
}'
SELECT value
FROM OPENJSON(@json, '$.Skills')

`OPENJSON` 函数将查找 `Skills` 属性，返回 `Skills` 数组中的值（每个元素一行）。

对象中 $.Skills 数组的元素
键	value
0	SQL
1	.NET
2	C#

在此示例中，我有一个简单的 `string` 值数组，但它适用于对象数组。

APPLY 和 OPENJSON

当您有一个 JSON 对象数组存储在某个列中时，您可能需要提取该数组并将其连接到父行。例如，我们可以查看以下结构：

ID	名称	…	电话号码
`1`	`John`		`[{"Number":"06472643","Type":"Work"},` `{"Number":"01164322","Type":"Home"}]`
`2`	`Jane`		`[{"Number":"01726443","Type":"Work"},` `{"Number":"06243344","Type":Mobile"}]`
`3`	`Jack`		`[{"Number":"01167343","Type":"Home"}]`

如果您将电话号码数组视为一个内部虚拟电话号码表，您可能希望将 `Person` 行与一组电话号码 `JOIN`。您可以使用 `CROSS APPLY` 运算符将 JSON 对象数组与父行 `JOIN`。

SELECT ID,Name,Number,Type
FROM Person
  CROSS APPLY
  OPENJSON(PhoneNumbers)
  WITH (Number nvarchar(100), Type nvarchar(20))

`OPENJSON` 将转换电话号码数组并提取 `Number` 和 `Type` 属性作为表列。然后 `CROSS APPLY` 将此表与父 `Person` 行连接。

`CROSS APPLY` 和 `OPENJSON` 将是在查询关系型列和 JSON 文本时非常常见的操作。请注意，您可能会用到两个运算符——`CROSS APPLY` 和 `OUTER APPLY`。关键区别在于：

`CROSS APPLY` 如果 `OPENJSON` 未从 JSON 数组返回任何行，则会抑制父行。
`OUTER APPLY` 即使 `OPENJSON` 未返回任何内容，也会返回一个父行。
如果 `OPENJSON` 返回至少一行，则 `CROSS` 和 `OUTER APPLY` 的行为相同。

您可以使用 `OPENJSON` 展开某些列中的值数组，并按元素搜索行，例如：

SELECT PersonID,FirstName,LastName,EmailAddresses, PhoneNumbers
FROM Person
    CROSS APPLY OPENJSON(EmailAddresses) EA
WHERE EA.value = 'john@mail.com'

`OPENJSON` 函数将获取 `EmailAdresseses` 列中的文本，遍历地址的元素，获取每个对象，并返回每个元素的值。查询将仅过滤 `EA.value` 等于 `'john@mail.com'` 的行。

`CROSS APPLY`/`OPENJSON` 对能够处理非常复杂的 JSON 结构。例如，我们可以解析像 GeoJSON 结构这样的复杂 JSON 结构，用于表示多边形。

{ "type": "MultiPolygon", 
    "coordinates": [
        [
            [[40, 40], [20, 45], [45, 30], [40, 40]]
        ], 
        [
            [[20, 35], [10, 30], [10, 10], [30, 5], [45, 20], [20, 35]], 
            [[30, 20], [20, 15], [20, 25], [30, 20]]
        ]
    ]
}

以 GeoJSON 格式表示的多边形有一个多边形数组，每个多边形有一个线数组，每条线有一个坐标数组，每个坐标表示为两个元素的数组。在关系型结构中，多边形将表示为四个表的集合。然而，在 JSON 格式中，您拥有数组的嵌套结构。

您可以使用以下查询来查询此嵌套结构。

SELECT polygons.[key] as polygon, lines.[key] as line, x, y
FROM OPENJSON(@multipolygon, '$.coordinates') as polygons
       CROSS APPLY OPENJSON(polygons.value) as lines
              CROSS APPLY OPENJSON(lines.value)
                     WITH (x float '$[0]', y float '$[1]')

每个 `CROSS APPLY OPENJSON` 都等同于关系模型中的 `JOIN`。第一个 `OPENJSON` 调用将解析第一级的多边形列表。来自此 `OPENJSON` 的 `polygons.value` 包含一个线数组，因此 `CROSS APPLY OPENSON(polygons.value)` 将返回该多边形中的线数组。`CROSS APPLY OPENJSON(lines.value)` 将解析线数组中的坐标，并在索引 0 和 1 处返回 `x` 和 `y` 坐标。

正如您可能注意到的，通过几个嵌套的 `CROSS APPLY` 运算符，您可以解析任何 JSON 结构。

计算列

如果您不想直接在查询中使用 JSON 函数，可以使用计算列和视图将它们封装起来。

例如，如果您频繁使用 JSON 列中的 `Title` 和 `Payment.Salary` 字段，您可以添加引用这些字段的计算列。

ALTER TABLE Person
ADD vCompany AS JSON_VALUE(AdditionalInfo, '$.Title'),
    vSalary  AS JSON_VALUE(AdditionalInfo, '$.PaymentInfo.Salary')

现在，您可以使用计算列代替直接调用 `JSON_VALUE` 函数。

如果您想要 JSON 文本的关系视图，这也是可能的。您只需在 JSON 文本上应用 `OPENJSON` 函数，指定 JSON 中的路径并返回列类型。

CREATE VIEW PersonInfo AS
SELECT PersonID, MiddleName, Salutation, Salary
FROM Person CROSS APPLY
    OPENJSON(AdditionalInfo)
    WITH(
        MiddleName nvarchar(40) '$.Info.MiddleName',
        Salutation nvarchar(40) '$.Info.Salutation',
        Salary float '$.PaymentInfo.Salary'
    )

`OPENJSON` 将打开 `AdditionalInfo` 列中的 JSON 文本，并返回 `Info.MiddleName`、`Info.Salutation` 和 `PaymentInfo.Salary` 路径上的值。它将 JSON 文本中的值转换为指定类型，您将看到它们作为虚拟子表。如果 `AdditionalInfo` 包含单个 JSON 对象，`OPENJSON` 将返回单个行。否则，它将返回一个表，其中每个元素对应 JSON 数组中的一个元素。

排序规则感知的 JSON 函数

JSON 函数返回的文本继承源列的排序规则。以下查询将始终有效。

SELECT PersonID,FirstName,LastName,EmailAddresses, PhoneNumbers 
FROM Person
WHERE JSON_VALUE(AdditionalInfo, '$.isActive') = 'true'

但是，如果您将包含 JSON 文本的原始文本列定义为不区分大小写，则以下查询也有效。

SELECT PersonID,FirstName,LastName,EmailAddresses, PhoneNumbers 
FROM Person
WHERE JSON_VALUE(AdditionalInfo, '$.isActive') = 'TRUE'

如果 JSON 列 `AdditionalInfo` 不区分大小写，则相等谓词将忽略大小写。

索引 JSON 数据

SQL Server 使您能够创建常规表列和 JSON 文本中属性的标准索引。

例如，如果您想索引 `AdditionalInfo` 列中 `$.Salary` 属性，您可以创建以下索引。

ALTER TABLE Person
ADD vCompany AS JSON_VALUE(AdditionalInfo, '$.Company'),
    vSalary  AS JSON_VALUE(AdditionalInfo, '$.PaymentInfo.Salary')
    vTitle   AS JSON_VALUE(AdditionalInfo, '$.Title'),
    vSkills  AS JSON_QUERY(AdditionalInfo, '$.Skills')

CREATE INDEX idx_Person_1
    ON Person(vCompany)
    INCLUDE(FirstName, LastName, vTitle, vSkills, vSalary)

首先，您需要创建引用 JSON 列中属性的虚拟计算列。请注意，这些列没有额外的空间——它们仅在使用时计算。

然后，您可以对这些列创建常规索引。请注意，SQL Server 允许您在设计索引时进行细粒度控制。您可以选择哪些字段将用于过滤或排序记录，哪些字段将仅用作索引中的包含列（针对 `SELECT` 列表中的附加列进行了优化）。

如果您运行初始查询，您将看到它使用了新索引而不是完全表扫描。您无需重写查询，SQL Server 将知道查询中的 `JSON_VALUE` 函数将被映射到已索引的虚拟列。有关索引 JSON 文档的更多详细信息，请参阅 MSDN 博客文章。

JSON 以文本形式表示的事实允许您创建标准全文搜索索引。如果您有一个 JSON 数字或字符串数组，例如上面的电子邮件地址数组，这是一个不错的选择。您可以使用以下代码在 `EmaillAddress` 列上创建全文搜索索引。

CREATE FULLTEXT INDEX ON Person(EmailAddresses)
    KEY INDEX PK_Person_ID
    ON jsonFullTextCatalog;

FTS 索引将分割 JSON 数组中的文本，并索引每个文本元素。以下查询可用于查找包含电子邮件地址 `john@mail.com` 的所有 `Person` 行。

SELECT PersonID,FirstName,LastName,EmailAddresses, PhoneNumbers 
FROM Person
WHERE CONTAINS(EmailAddresses, 'john@mail.com')

全文搜索允许您使用 `AND`、`OR` 和 `NOT` 运算符创建复杂的查询，例如：

SELECT PersonID,FirstName,LastName,EmailAddresses, PhoneNumbers
FROM Person
WHERE CONTAINS(EmailAddresses, 'john@mail.com OR jovan@mail.com')

正如您所见，SQL Server 提供了一个简单但强大的机制来查询和索引 JSON 文本。您可以在以下文章中找到更多详细信息：使用全文搜索索引索引 JSON 数组。

将关系型数据格式化为 JSON

如果您想以 JSON 格式返回表中的信息，只需在查询末尾添加 `FOR JSON PATH` 子句即可。

SELECT PersonID,FirstName,LastName,
       JSON_QUERY(EmailAddresses) AS 'Contact.Emails',
       JSON_QUERY(PhoneNumbers) AS 'Contact.Phones'
FROM Person
WHERE CONTAINS(EmailAddresses, 'john@mail.com')
FOR JSON PATH

`FOR JSON PATH` 子句将连接返回的行集中的所有记录，并使用列名/别名和行中的值创建键：值对。结果可能如下例所示：

[
 { "FirstName":"John",
   "LastName":"Doe",
   "Contact": {
        "Emails": ["john@mail.com","john@aw.com"],
        "Phones": ["+381 55 555 555"]
 },
 { "FirstName":"Jane",
   "LastName":"Doe",
   "Contact": {
        "Emails": ["jane@mail.com","jane@aw.com"],
        "Phones": []
 },
 ...
]

由于电子邮件和电话都有 `Contact` 前缀，`FOR JSON PATH` 将它们组合在一个对象中。

注意一个重要细节——我用 `JSON_QUERY()` 包装了 `EmailAddreses` 和 `PhoneNumbers`。如果我将这些列作为纯文本返回，`FOR JSON` 会将它们视为普通文本，用引号括起来，并根据 JSON 转义规则转义文本中的字符。然而，如果我将值传递给 `JSON_QUERY` 函数，`FOR JSON` 会将这些文本值视为 JSON，并将其包含在输出中。这看起来像是将 JSON 文本转换为 JSON。

`FOR JSON` 子句在您有一个一对多结构并且需要将父行（例如 `Person`）连接到一组子行（例如 `PersonPhones`、`PersonPhoneTypes`）时可能很有用。设想一个关系型结构，其中人员电话号码存储在 `PersonProne` 和 `PersonPhoneNumberType` 表中。常规的 `JOIN` 会增加行数，因为它为每个 `Person:Phone` 对返回一行结果集，因此您需要在客户端处理结果。使用 `FOR JSON`，您可以将所有相关的电话号码作为单个单元格返回到一行中。

SELECT PersonID,FirstName,LastName,
        (SELECT PersonPhone.PhoneNumber, PhoneNumberType.Name AS PhoneNumberType
            FROM  Person.PersonPhone
            INNER JOIN PhoneNumberType
                ON PersonPhone.PhoneNumberTypeID = PhoneNumberType.PhoneNumberTypeID
         WHERE Person.PersonID = PersonPhone.PersonID
        FOR JSON PATH) AS PhoneNumbers

您将使用 `FOR JSON` 的另一个场景是反规范化。与其连接相关表，不如简化架构，并将每个人的相关电话号码集移至 `Person.PhoneNumbers` 列中的电话号码数组。您可以使用类似以下的查询：

UPDATE Person
SET PhoneNumbers =
        (SELECT PersonPhone.PhoneNumber, PhoneNumberType.Name AS PhoneNumberType
            FROM  Person.PersonPhone
            INNER JOIN PhoneNumberType
                ON PersonPhone.PhoneNumberTypeID = PhoneNumberType.PhoneNumberTypeID
         WHERE Person.PersonID = PersonPhone.PersonID
        FOR JSON PATH)

内部查询将选择当前处理人员的所有电话和类型。`FOR JSON PATH` 子句会将行集格式化为单个 JSON 数组，该数组可以作为文本存储在 `PhoneNumbers` 列中。

现在，您的 select 查询将只有一个表扫描，而不是三个表扫描和两个 `JOIN` 运算符，这可能会提高您的性能。

导入 JSON 数据

最后，我们将了解如何将 JSON 文本导入表中。假设您想将 JSON 格式的文本导入数据库。使用 `OPENJSON` 函数，您可以轻松地将 JSON 格式的文本导入任何表。`OPENJSON` 函数将解析 JSON 文本并返回所有数组元素。您需要做的就是将 `OPENJSON` 返回的行插入表中。

INSERT INTO Person(FirstName,LastName,EmailAddresses, PhoneNumbers)
SELECT FirstName,LastName,EmailAddresses, PhoneNumbers
FROM OPENJSON(@PersonJSON)
    WITH(
        FirstName nvarchar(40),
        LastName nvarchar(40),
        AdditionalInfo NVARCHAR(MAX) AS JSON,
        EmailAddresses NVARCHAR(MAX) AS JSON,
        PhoneNumbers NVARCHAR(MAX) AS JSON
    )

在 `WITH` 子句中，您可以指定要获取的 JSON 对象中的哪些键。`OPENJSON` 将根据列名匹配键并返回值。如果 JSON 对象中的某个键引用嵌套的 JSON 对象（例如，`EmailAddresses` 和 `PhoneNumbers` 键引用内部数组而不是简单的 `string`），您需要使用 `AS JSON` 选项。

在本例中，假设 `@PersonJSON` 变量/参数包含具有扁平字段结构的 JSON 文本。但是，`OPENJSON` 甚至可以解析具有嵌套元素的复杂结构。

在以下示例中，显示了使用 `OPENROWSET BULK` 函数读取文件系统中国家内容的 SQL 代码，并将文件内容（`BulkColumn`）传递给 `OPENJSON` 函数。

INSERT INTO Person(FirstName,LastName,EmailAddresses, PhoneNumbers)
SELECT person.* FROM
OPENROWSET(BULK N't:\share\people.json', SINGLE_CLOB) AS json
            CROSS APPLY OPENJSON(BulkColumn)
                        WITH(
                            FirstName nvarchar(40),    LastName nvarchar(40),
                            AdditionalInfo NVARCHAR(MAX) AS JSON,
                            EmailAddresses NVARCHAR(MAX) AS JSON,
                            PhoneNumbers NVARCHAR(MAX) AS JSON ) AS person

`OPENJSON` 函数将解析文件中的 JSON 内容并将其插入 `Person` 表。

此代码既可以处理本地文件，也可以从云端加载文件。Azure 文件存储支持 SMB 协议，因此您可以像映射磁盘一样映射 Azure 文件中的某个共享，并直接引用云中的文件，就像它们在本地文件系统上一样。这可能是将 JSON 文件从云导入数据库的最简单方法。您可以在此帖子中找到更多示例：将 JSON 文本插入 SQL Server 表。

实用工具

由于 JSON 是一种简单格式，您可以使用它将值列表传递给 SQL Server 作为参数。假设您想根据主键值列表从数据库返回一组 `Person` 行。您可以创建一个简单的存储过程，如下面的代码所示。

create procedure
dbo.GetPersonList(@PersonIds nvarchar(100))
 as begin

 SELECT *
 FROM Person.Person 
         JOIN OPENJSON(@PersonIds)
                ON BusinessEntityID = value
  -- Equivalent to "WHERE BusinessEntityID IN (1,4,6,9)"

end

OPENJSON 将遍历 JSON 数组，为每个元素返回一个值，您可以将结果与 `Person` 表 `JOIN`。现在，如果您想按逗号分隔的 ID 列表搜索 `Person` 行，您只需传递格式化为 JSON 的 CSV 列表，例如：

EXEC dbo.GetPersonList '[1,4,6,9]'

如果您关心性能，可以参考以下文章：OPENJSON - 按 ID 列表选择行的最佳方法之一。使用 XML、表值参数的替代方法可以在这里找到：SQL Wizardry 第四部分 - 将数据列表传递给 SQL Server。

要点

在本文中，我介绍了 SQL Server 处理 JSON 文本的能力。如果您需要组合数据模型，需要存储和处理关系型和 JSON 数据，这可能是 SQL Server 中提供功能的有用概述。其中大部分功能在 SQL Server 2016 CTP3 中可用，并且很快将在 Azure SQL 数据库中提供。

历史

2015 年 11 月 13 日：初始版本