【SqlServer系列】JSON数据

1   概述

本文将组成MSDN简要概述JSON数据。

2   具体内容

JSON 是如出一辙栽流行的数量格式,用于在现代 Web
和动应用程序中交换数据。 JSON 还可用来在
Microsoft Azure DocumentDB 等 NoSQL 数据库中蕴藏非结构化数据。 许多 REST Web 服务因 JSON
文本格式返回结果,或收受以 JSON 格式的多少。 例如,大多数 Azure 服务(如 Azure 搜索、Azure
存储和 Azure DocumentDb)都提供返回或行使 JSON 的 REST 终结点。 JSON 也是用以通过 AJAX 调用在网页和 Web
服务器之间交换数据的重点格式。

NoSQL 1

NoSQL 2

NoSQL 3

2.1  将 JSON 集合转换为行集

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

NoSQL 4

 

2.2  将 SQL Server 数据易为 JSON 或导出
JSON

通过以 FOR
JSON 子句添加到 SELECT 语句子被,可将 SQL Server 数据要 SQL
查询结果的格式设置也 JSON。 使用 FOR JSON
委托从客户端应用程序到 SQL Server 的 JSON 输出格式。 有关详细信息,请参阅 赖 FOR JSON
将查询结果的格式设置也 JSON (SQL
Server)。

以下示例使用 PATH 模式与 FOR JSON
子句。

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

“应用程序池:” FOR JSON 子句以 SQL
结果的格式设置也 JSON 文本,该格式可资给识别 JSON 的别样利用。 PATH 选项于 SELECT
子句被行使以碰分隔的别名,以嵌套查询结果负之靶子。

NoSQL 5

2.3  合并关系数据和 JSON 数据

 SQL Server 提供混合模型,用于通过规范
Transact-SQL 语言存储和处理关系数据与 JSON 数据。 可以将 JSON
文档的联谊组织到表中,在她中间成立关系,将表明中存储的强类型标量列与
JSON 列中蕴藏的灵活键/值对联合,以及使用完全 Transact SQL
查询一个要多只表中的标量值和 JSON 值。

JSON 文本通常存储于 varchar 或 nvarchar
列中,并编写了纯文本形式的目录。 任何支持文件的 SQL Server 功能或机件都支持
JSON,因此 JSON 和其余 SQL Server 功能中的竞相几乎无其余约束。你可拿 JSON 存储于内存中要临时表中、对 JSON
文本用行级别安全性谓词等。

若在单的 JSON
工作负荷着,你想如果使专用于处理 JSON 文档的自定义查询语言,可以考虑
Microsoft
Azure DocumentDB。

以下用例说明如何以 SQL Server中以内置的 JSON 支持。

2.4 从格式化为 JSON 的 SQL Server 表返回数据

只要您的 Web 服务从数额库层提取数额并因
JSON 格式返回数据,或者当纳曾格式化为 JSON 的数量的 JavaScript
框架或库中回到数据,则好直接当 SQL 查询中安 JSON 输出的格式。 你可以使用 FOR JSON 将 JSON 格式设置委托给 SQL
Server,而非修代码或者隐含一个仓房来换表格查询结果对象,然后将对象序列化为
JSON 格式。

譬如,你或许想使扭转符合 OData 规范之 JSON
输出。 Web
服务用动用以下格式的求与应。

  • 请求: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName

  • 响应: {"@odata.context":"http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity","ProductID":1,"ProductName":"Chai"}

    是 OData URL 代表对 ID 为 1 的出品的
    ProductID 和 ProductName 列的请求。 可以应用 FOR JSON 按 SQL Server
    中所要的格式设置输出格式。

    1 SELECT ‘http://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity’
    2 AS ‘@odata.context’,
    3 ProductID, Name as ProductName
    4 FROM Production.Product
    5 WHERE ProductID = 1
    6 FOR JSON AUTO

这询问的出口是完全符合 OData 规范之 JSON
文本。 格式设置及转义由 SQL Server
处理。 SQL Server
还可将查询结果的格式设置为其他格式,如 OData JSON 或 GeoJSON –
有关详细信息,请参阅 Returning spatial data in GeoJSON
format(以
GeoJSON 格式返回空间数据)。

 2.5  使用 SQL 查询分析 JSON 数据

假使要筛选或聚合 JSON
数据以用来报告,可以动用 OPENJSON 将 JSON 转换为涉嫌格式。 然后,使用规范 Transact-SQL 和搭函数来准备报。

 1 SELECT Tab.Id, SalesOrderJsonData.Customer, SalesOrderJsonData.Date  
 2 FROM   SalesOrderRecord AS Tab  
 3           CROSS APPLY  
 4      OPENJSON (Tab.json, N'$.Orders.OrdersArray')  
 5            WITH (  
 6               Number   varchar(200) N'$.Order.Number',   
 7               Date     datetime     N'$.Order.Date',  
 8               Customer varchar(200) N'$.AccountNumber',   
 9               Quantity int          N'$.Item.Quantity'  
10            )  
11   AS SalesOrderJsonData  
12 WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'  
13 ORDER BY JSON_VALUE(Tab.json, '$.Group'), Tab.DateModified

足以跟一个询问中行使正规表列和根源 JSON
文本的价。 可以于 JSON_VALUE(Tab.json, '$.Status') 表达式上添加索引为增长查询的性能。 有关详细信息,请参阅 本着 JSON
数据编制索引。

2.6 将 JSON 数据导入 SQL Server 表

若果非得以 JSON 数据由表面服务加载到 SQL
Server,则好下 OPENJSON 将数据导入 SQL
Server,而无分析应用程序层中之数额。

 1 DECLARE @jsonVariable NVARCHAR(MAX)
 2 
 3 SET @jsonVariable = N'[  
 4         {  
 5           "Order": {  
 6             "Number":"SO43659",  
 7             "Date":"2011-05-31T00:00:00"  
 8           },  
 9           "AccountNumber":"AW29825",  
10           "Item": {  
11             "Price":2024.9940,  
12             "Quantity":1  
13           }  
14         },  
15         {  
16           "Order": {  
17             "Number":"SO43661",  
18             "Date":"2011-06-01T00:00:00"  
19           },  
20           "AccountNumber":"AW73565",  
21           "Item": {  
22             "Price":2024.9940,  
23             "Quantity":3  
24           }  
25        }  
26   ]'
27 
28 INSERT INTO SalesReport  
29 SELECT SalesOrderJsonData.*  
30 FROM OPENJSON (@jsonVariable, N'$.Orders.OrdersArray')  
31            WITH (  
32               Number   varchar(200) N'$.Order.Number',   
33               Date     datetime     N'$.Order.Date',  
34               Customer varchar(200) N'$.AccountNumber',   
35               Quantity int          N'$.Item.Quantity'  
36            )  
37   AS SalesOrderJsonData;

表面 REST 服务可供 JSON
变量的始末,这些内容以于客户端 JavaScript
框架作为参数发送,或者打表文件加载。 你可以当 SQL Server 表中轻轻松松插入、更新或联合来自
JSON 文本的结果。 有关这个方案的详细信息,请参考以下博客文章。

  • Importing JSON data in SQL
    Server
  • Upsert JSON documents in SQL Server
    2016
  • Loading GeoJSON data into SQL Server
    2016。

2.7 将 JSON 文件加载到 SQL Server

文件被储存的音讯而格式化为正式 JSON
或施行分隔的 JSON。 SQL Server 可以导入 JSON
文件之始末,使用 OPENJSON 或 JSON_VALUE 函数分析内容,并拿那加载到表中。

  • 使 JSON 文档存储于可由于 SQL Server
    访问的当地文件、共享网络驱动器或 Azure
    文件存储位置,可以下批量导入将 JSON 数据加载到 SQL Server。 有关这方案的详细信息,请参阅 Importing JSON
    files into SQL Server using OPENROWSET
    (BULK)(使用
    OPENROWSET (BULK) 将 JSON 文件导入 SQL Server)。

  • 倘若实施分隔的 JSON 文件存储于 Azure
    Blob 存储或 Hadoop 文件系统中,你可以动用 Polybase 来加载 JSON
    文本,在 Transact-SQL 代码中剖析文本,然后将该载入表中。

2.8  测试驱动内置的 JSON 支持

利用 AdventureWorks 示例数据库测试驱动内置
JSON 支持。 若要拿走 AdventureWorks
示例数据库,必须从 此处。 将示例数据库还原到 SQL Server 2016
实例后,请解压缩示例文件,然后于 JSON 文件夹着开辟“JSON Sample Queries
procedures views and indexes.sql”文件。 运行是文件中之本子,将某些现有数量的格式重新安也
JSON 数据,对 JSON 数据运行示例查询及喻,为 JSON
数据编制索引,然后导入和导出 JSON。

下是您得对拖欠公文中富含的脚本执行之操作。

  1. 如若现有架构非规范化以创建 JSON
    数据的排。


    1. SalesReasons、SalesOrderDetails、SalesPerson、Customer
      和含销售订单相关信息的表中的消息囤积到 SalesOrder_json 表的
      JSON 列中。

    2. 用 EmailAddresses/PersonPhone
      表中之信作 JSON 对象的数组存储到 Person_json 表中。

  2. 始建查询 JSON
    数据的历程和视图。

  3. 也 JSON 数据编制索引 – 为 JSON
    属性和全文索引创建索引。

  4. 导入和导出 JSON – 创建并运行为 JSON
    结果形式导出 Person 和 SalesOrder 表内容,并采取 JSON 输入导入和换代
    Person 与 SalesOrder 表的进程。

  5. 运转查询示例 –
    运行有查询,用于调用步骤 2 暨 4 中创造的囤积过程和视图。

  6. 理清脚本NoSQL – 如果你想使保存步骤 2 同 4
    中创造的储存过程和视图,请不要运行是部件。

 

3   参考文献

 【01】https://docs.microsoft.com/zh-cn/sql/relational-databases/json/json-data-sql-server

4   版权

 

  • 谢你的读,若有不足之处,欢迎指教,共同学习、共同进步。
  • 博主网址:http://www.cnblogs.com/wangjiming/。
  • 最为少一些文章使读、参考、引用、抄袭、复制和糊等多方法结合而变成的,大部分啊原创。
  • 倘您喜爱,麻烦推荐一下;如你有新想法,欢迎提出,邮箱:2016177728@qq.com。
  • 可转载该博客,但不能不著名博客来源。
网站地图xml地图