使用 T-SQL 查询和操作 JSON 数据

2019-12-13 08:00:00 · 飞浪

在本指南中，你将学习如何使用在 SQL Server 2017 Express 上运行的 Northwind 数据集实例来利用几个 JSON 内置函数

介绍

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

_{免责声明：本内容来源于第三方作者授权、网友推荐或互联网整理，旨在为广大用户提供学习与参考之用。所有文本和图片版权归原创网站或作者本人所有，其观点并不代表本站立场。如有任何版权侵犯或转载不当之情况，请与我们取得联系，我们将尽快进行相关处理与修改。感谢您的理解与支持！}

_查看原文

技术指南

阅读全文

使用 T-SQL 查询和操作 JSON 数据

杭州电子商务研究院

4年前 · 面向社会、服务行业、政产学研结合、整合资源、和谐发展

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

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 )

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

SELECT FullName, Title, HireDate, JSON_VALUE(Notes, '$.education') AS Education, JSON_VALUE(Notes, '$.field') AS Field, JSON_VALUE(Notes, '$.bilingual') AS Bilingual FROM NewestHires

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" } ]';