SQL Server 中的 JSON 数据

客官°小女子只卖身不卖艺 2021-12-11 03:21 146阅读 0赞

下面是 JSON 文本的示例

\[\{  
"name": "John",  
"skills": \["SQL", "C\#", "Azure"\]  
\}, \{  
"name": "Jane",  
"surname": "Doe"  
\}\]

通过使用 SQL Server 内置函数和运算符，你可以对 JSON 文本执行以下操作：

*  分析 JSON 文本和读取或修改值。
 *  将 JSON 对象数组转换为表格式。
 *  在转换后的 JSON 对象上运行任意 Transact-SQL 查询。
 *  将 Transact-SQL 查询的结果设置为 JSON 格式。

![内置的 JSON 支持概述][JSON]

在以下示例中，查询同时使用表中的关系数据和 JSON 数据（存储在名为 `jsonCol` 的列中）：

SELECT Name,Surname,  
JSON\_VALUE(jsonCol,'$.info.address.PostCode') AS PostCode,  
JSON\_VALUE(jsonCol,'$.info.address."Address Line 1"')+' '  
\+JSON\_VALUE(jsonCol,'$.info.address."Address Line 2"') AS Address,  
JSON\_QUERY(jsonCol,'$.info.skills') AS Skills  
FROM People  
WHERE ISJSON(jsonCol)>0  
AND JSON\_VALUE(jsonCol,'$.info.address.Town')='Belgrade'  
AND Status='Active'  
ORDER BY JSON\_VALUE(jsonCol,'$.info.address.PostCode')

### 将 JSON 集合转换为行集 ###

在 SQL Server 中查询 JSON 不需要自定义查询语言。 可以使用标准的 T-SQL 查询 JSON 数据。 如果必须基于 JSON 数据创建查询或报表，可以通过调用 OPENJSON 行集函数，轻松地将 JSON 数据转换为行与列 。 有关详细信息，请参阅[用 OPENJSON 将 JSON 数据转换为行和列 (SQL Server)][OPENJSON _ JSON _ _SQL Server]。

以下示例调用 OPENJSON，并且将 `@json` 变量中存储的对象数组转换为可使用标准 SQL SELECT 语句查询的行集 ：

DECLARE @json NVARCHAR(MAX)  
SET @json =  
N'\[  
\{ "id" : 2,"info": \{ "name": "John", "surname": "Smith" \}, "age": 25 \},  
\{ "id" : 5,"info": \{ "name": "Jane", "surname": "Smith" \}, "dob": "2005-11-04T12:00:00" \}  
\]'  
  
SELECT \*  
FROM OPENJSON(@json)  
WITH (id int 'strict $.id',  
firstName nvarchar(50) '$.info.name', lastName nvarchar(50) '$.info.surname',  
age int, dateOfBirth datetime2 '$.dob')

OPENJSON 将 JSON 对象的数组转换为表，其中每个对象表示为一行，键/值对将作为单元返回。 输出遵循下列规则：

*  OPENJSON 将 JSON 值转换为 WITH 子句中指定的类型 。
 *  OPENJSON 可以处理规则的键/值对以及分层组织的对象。
 *  不需要返回 JSON 文本中包含的所有字段。
 *  如果 JSON 值不存在，OPENJSON 返回 NULL 值。
 *  可以选择在类型规范后指定一个路径，以引用嵌套属性或按不同的名称引用属性。
 *  路径中可选的 strict 前缀指定 JSON 文本中必须存在指定属性的值。

SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth as dob  
FROM People  
FOR JSON PATH

应用程序池:” FOR JSON 子句将 SQL 结果的格式设置为 JSON 文本，该格式可提供给识别 JSON 的任何应用。 PATH 选项在 SELECT 子句中使用以点分隔的别名，以嵌套查询结果中的对象。

\[\{  
"id": 2,  
"info": \{  
"name": "John",  
"surname": "Smith"  
\},  
"age": 25  
\}, \{  
"id": 5,  
"info": \{  
"name": "Jane",  
"surname": "Smith"  
\},  
"dob": "2005-11-04T12:00:00"  
\}\]

转载于:https://www.cnblogs.com/PerfectBeauty/p/11093093.html

[JSON]: /images/20211211/73cbd6488e884e2e865de57653d6ffb8.png
[OPENJSON _ JSON _ _SQL Server]: https://docs.microsoft.com/zh-cn/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-2017