介绍
JSON（JavaScript 对象表示法的缩写）最初于 2000 年代初推出，是一种在应用程序和服务器之间交换数据的轻量级格式。然而，直到 2013 年 ECMA International 将其标准化为 ECMA-404 后，它的使用才得到广泛传播。几年后（从 SQL Server 2016 开始），微软添加了内置函数，允许开发人员直接使用 T-SQL 操作 JSON 数据。
您可以在此处查看 ECMA-404 标准的原始草案（第一版，日期为 2013 年 10 月） 。当前版本（发布于 2017 年 12 月）也可供下载。
除其他功能外，这些函数还允许您对 JSON 文本执行SELECT和UPDATE查询，并将 JSON 对象集视为表记录（反之亦然）。此外，过去十年来，物联网的迅猛发展以及数据的复杂性使得多家公司不得不采用 NoSQL 数据库。借助 SQL Server 2016 及更高版本，您可以使用同一引擎充分利用两全其美的优势（结构化和非结构化数据）。
在本指南中，您将学习如何使用在 SQL Server 2017 Express 及其关联的 SQL Server Management Studio 版本上运行的 Northwind 数据集实例来利用几个 JSON 内置函数。您可以从 Microsoft SQL Server 示例存储库下载数据库安装脚本，并根据 MIT 许可条款使用它。
以 JSON 格式查看查询结果
首先，让我们以 JSON 格式显示SELECT语句的结果。考虑以下 T-SQL 查询，它返回以下输出：
SELECT TOP 10
c.CompanyName,
c.City,
c.Country,
COUNT(o.OrderID) AS CountOrders
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CompanyName, c.City, c.Country
ORDER BY COUNT(o.OrderId) DESC
接下来，在查询末尾添加FOR JSON PATH（如下所示），然后再次执行。结果集现在由一行组成，其中包含一个链接，该链接包含相同的数据集，但格式为 JSON。单击链接可在单独的 SQL Server Management Studio 选项卡中查看它。
不幸的是，在撰写本文时，SSMS 尚未提供以友好方式显示 JSON 的本机方法。幸运的是，大多数文本编辑器（Notepad++ 或 VisualStudio Code，仅举两个例子）都有可以处理该问题的插件，如下所示。
如您所见，每条记录都转换为 JSON 对象，结果集成为此类对象的数组。最后，每个单独的单元格都由特定的键值对表示。或者，您可以通过将FOR JSON PATH替换为FOR JSON PATH, ROOT('Top10Customers')来添加根元素：
SELECT TOP 10
c.CompanyName,
c.City,
c.Country,
COUNT(o.OrderID) AS CountOrders
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CompanyName, c.City, c.Country
ORDER BY COUNT(o.OrderId) DESC
FOR JSON PATH, ROOT('Top10Customers')
该图显示了两个查询（有和没有根元素）之间的区别：
是否包含根元素取决于使用结果集的应用程序的需求。无论如何，借助FOR JSON PATH，您可以直接返回 JSON 数据，因此不必在应用程序级别单独序列化响应。
将 JSON 转换为表格格式
Northwind 数据库中的 Employees 表包含一个名为 Notes 的列，其中包含有关每个员工的信息，包括教育、兴趣和以前的经验 - 但都是文本。让我们创建一个新表，我们将从中获取此字段中的一些信息并将其转换为更易于操作的 JSON 对象。这将使我们能够模拟一组非结构化的属性。为了实现这一目标，我们将执行以下操作：
CREATE TABLE [Northwind].[dbo].NewestHires (
EmployeeID INT PRIMARY KEY,
FullName VARCHAR(100) NOT NULL,
Title VARCHAR(50) NOT NULL,
HireDate VARCHAR(50) NOT NULL,
Notes VARCHAR(MAX) NOT NULL
)
让我们将Employees表中最新的 5 名员工插入到名为NewestHires的新表中。请注意，我们将使用 JSON 数据填充Notes（最初为空，以便我们说明如何稍后更新它）：
INSERT INTO NewestHires
SELECT TOP 5
EmployeeID,
CONCAT(FirstName, ' ', LastName) AS FullName,
Title,
HireDate,
'{"education": "", "field": "", "bilingual": ""}' AS Notes
FROM Employees ORDER BY HireDate DESC
要将每个 JSON 对象的属性视为列，我们可以使用JSON_VALUE函数从 JSON 字符串中提取值（或使用JSON_QUERY检索数组）。第一个参数是包含对象的列的名称，而第二个参数是实际属性，美元符号$代表整个对象。
SELECT
FullName,
Title,
HireDate,
JSON_VALUE(Notes, '$.education') AS Education,
JSON_VALUE(Notes, '$.field') AS Field,
JSON_VALUE(Notes, '$.bilingual') AS Bilingual
FROM NewestHires
如果对象有一个名为info的根元素，则传递给JSON_VALUE的第二个参数应该分别为'$.info.education'、'$.info.field'和'$.info.bilingual'。
更新给定列中的键值对
要更新 Steven Buchanan (EmployeeID = 5) 的教育背景以包含学士学位，我们可以执行以下操作：
UPDATE NewestHires SET Notes =
JSON_MODIFY(Notes, '$.education', 'BA')
WHERE EmployeeID = 5
或者将 Michael Suyama (EmployeeID = 6) 的学历更新为 MA 和 MBA：
UPDATE NewestHires SET Notes =
JSON_MODIFY(Notes, '$.education', JSON_QUERY('["BA","MBA"]'))
WHERE EmployeeID = 6
需要使用上面的JSON_QUERY来提取或转换单引号内的常规文本为 JSON 片段。
还可以根据需要使用尽可能多的嵌套JSON_MODIFY函数一次更新多个属性：
UPDATE NewestHires SET Notes =
JSON_MODIFY(
JSON_MODIFY(
JSON_MODIFY(Notes, '$.education', 'BA'),
'$.field', 'Psychology'),
'$.bilingual', CAST(1 AS BIT)
)
WHERE EmployeeID = 8
最后，您甚至可以将值附加到数组属性。例如，如果 Michael Suyama 获得了博士学位，我们将需要相应地更新他的学历（请注意在传递给JSON_MODIFY的第二个参数中使用关键字append）：
UPDATE NewestHires SET Notes =
JSON_MODIFY(Notes, 'append $.education', 'PhD')
WHERE EmployeeID = 6
此图显示了前四个查询之前和之后的NewestHires表：
将 JSON 读入行和列
如果我们需要将整个对象解析为行和列（而不是只有一个 JSON 列，例如上面的Notes ），那么OPENJSON函数就是我们的好帮手。为了演示，让我们考虑已保存到名为@NewEmployees的变量中的以下 JSON 对象：
DECLARE @NewEmployees NVARCHAR(MAX);
SET @NewEmployees = N'[
{
"EmployeeID": 23,
"FullName": "James Hulse",
"Title": "Project Manager",
"HireDate": "2019-12-04"
},
{
"EmployeeID": 24,
"FullName": "Sally White",
"Title": "Sales Supervisor",
"HireDate": "2019-12-05"
}
]';
为了将这些 JSON 对象显示为行和列，我们将@NewEmployees作为参数传递给OPENJSON。接下来的步骤是告诉函数每个属性应该使用什么数据类型。为此，我们需要将列名、所需的数据类型和相应的属性括在WITH子句中。
SELECT *
FROM OPENJSON(@NewEmployees)
WITH (
EmployeeID INT 'strict $.EmployeeID',
FullName VARCHAR(100) '$.FullName',
Title VARCHAR(50) '$.Title',
HireDate DATETIME '$.HireDate'
);
这张图显示了上述查询的结果：
在此示例中，我们在EmployeeID中添加了strict前缀，以指定EmployeeID必须存在于 JSON 对象中（如果此字段是主键，则有意义）。需要注意的是，我们不一定必须返回 JSON 对象的所有组件（若要省略一个或多个组件，只需在WITH</font
免责声明：本内容来源于第三方作者授权、网友推荐或互联网整理，旨在为广大用户提供学习与参考之用。所有文本和图片版权归原创网站或作者本人所有，其观点并不代表本站立场。如有任何版权侵犯或转载不当之情况，请与我们取得联系，我们将尽快进行相关处理与修改。感谢您的理解与支持！
