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
服务器之间交换数据的要紧格式。

图片 1

图片 2

图片 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')

图片 4

 

2.2  将 SQL Server 数据转变为 JSON 或导出
JSON

透过将 FOEvoqueJSON 子句加多到 SELECT 语句中,可将 SQL Server 数据或 SQL
查询结果的格式设置为 JSON。 使用 FO途达 JSON
委托从客户端应用程序到 SQL Server 的 JSON 输出格式。 有关详细音讯,请参阅 依赖 FO奇骏 JSON
将查询结果的格式设置为 JSON (SQL
Server)。

以下示例使用 PATH 形式和 FO奇骏 JSON
子句。

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

“应用程序池:” FO索罗德 JSON 子句将 SQL
结果的格式设置为 JSON 文本,该格式可提要求识别 JSON 的其余利用。 PATH 选项在 SELECT
子句中接纳以点分隔的外号,以嵌套查询结果中的对象。

图片 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 输出的格式。 你能够选用 FOEnclave 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 U大切诺基L 代表针对 ID 为 1 的成品的
    ProductID 和 ProductName 列的呼吁。 能够选择 FOEnclave JSON 按 SQL Server
    中所需的格式设置输出格式。

    1 SELECT ”
    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 或 吉优JSON –
有关详细新闻,请参阅 和移动应用程序中交换数据。Returning spatial data in GeoJSON
format(以
吉优JSON 格式重返空间数据)。

 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 二〇一六实例后,请解压缩示例文件,然后从 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. 清理脚本 – 假若你想要保留步骤 2 和 4
    中创造的囤积进程与视图,请不要运维此部件。

 

3   参考文献

 【01】

4   版权

 

  • 多谢您的开卷,若有不足之处,迎接指教,共同学习、共同提升。
  • 博主网站:
  • 极少一些文章利用读书、参考、引用、抄袭、复制和粘贴等多样措施结合而成的,大多数为原创。
  • 如您喜爱,麻烦推荐一下;如你有新主张,迎接建议,邮箱:二零一四177728@qq.com。
  • 能够转发该博客,但必须著名博客来源。

相关文章