正如我之前提到的,我们这里除了会讲Power BI 之外,还会给大家补充周边与之相关的知识,今天我们就来讲讲在SQL 2016中如何处理JSON 数据对象。
SQL Server中的JSON函数使您能够分析和查询JSON数据,将JSON转换为关系格式以及将SQL查询结果导出为JSON文本。
如果您有JSON文本,则可以使用内置函数JSON_VALUE,JSON_QUERY和ISJSON从JSON提取数据或验证JSON的格式正确。为了进行更高级的查询和分析,OPENJSON函数可以将JSON对象数组转换为一组行。可以对返回的结果集执行任何SQL查询。最后,有一个FOR JSON子句,使您可以将查询结果格式化为JSON文本。
SELECT TOP 1000 [Version] ,[OSType] ,[Online] ,[OnlineT] FROM [ApplicationTest].[dbo].[table1] for json aut |
我们可以从简单的例子开始。在下面的Transact-SQL代码中,我们将定义一个文本变量,用于放置JSON文本:
DECLARE @json NVARCHAR(4000) SET @json = N'{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" } |
现在,我们可以使用JSON_VALUE和JSON_QUERY函数从JSON文本中提取值和对象:
SELECT JSON_VALUE(@json, '$.type') as type, JSON_VALUE(@json, '$.info.address.town') as town, JSON_QUERY(@json, '$.info.tags') as tag |
该查询将返回“ Basic”,“ Bristol”和[“ Sport”,“ Water polo”]值。JSON_VALUE函数从放置在指定为第二个参数的JSON路径上的JSON文本(例如,字符串,数字,true / false)返回一个标量值。JSON_QUERY在JSON路径上返回一个对象或数组(在本示例中为标记数组)。JSON内置函数使用类似JavaScript的语法通过第二个参数引用JSON文本中的值和对象。
OPENJSON函数使您可以在JSON文本中引用某个数组,并从该数组返回元素:
SELECT value FROM OPENJSON(@json, '$.info.tags') INSERT INTO Orders(Number, Date, Customer, Quantity) SELECT Number, Date, Customer, Quantity OPENJSON (@orders) WITH ( Number varchar(200), Date datetime, Customer varchar(200), Quantity int ) AS OrdersArra |
WITH子句中定义了OPENJSON返回的结果集中的四列。OPENJSON将尝试在每个JSON对象中查找属性Number,Date,Customer和Quantity,并将其值转换为结果集中的列。默认情况下,如果找不到该属性,则将返回NULL。上面查询中的假设是@orders变量包含以下JSON数组:
'[ {"Number":1, "Date": "8/10/2012", "Customer": "Adventure works", "Quantity": 1200}, {"Number":4, "Date": "5/11/2012", "Customer": "Adventure works", "Quantity": 100}, {"Number":6, "Date": "1/3/2012", "Customer": "Adventure works", "Quantity": 250}, {"Number":8, "Date": "12/7/2012", "Customer": "Adventure works", "Quantity": 2200} ]' |
如您所见,从JSON文本到关系形式的转换非常简单。您只需要指定列名称和类型,OPENJSON就会在JSON中找到与这些列匹配的属性。在这个例子中,使用了普通的JSON。但是,OPENJSON可以处理JSON对象的任何嵌套/层次结构。
此外,OPENJSON可用于在同一查询中组合关系和JSON数据。如果我们假设上一示例中显示的JSON数组存储在Orders列中,则以下查询可以合并这些列和JSON字段:
SELECT Id, FirstName, LastName, Number, Date, Customer, Quantity FROM Person CROSS APPLY OPENJSON (OrdersJson) WITH ( Number varchar(200), Date datetime, Customer varchar(200), Quantity int ) AS OrdersArra |
select * from [dbo].[table1] for json auto declare @jsondata varchar(max) set @jsondata=N'[{"Version":"2.1.1.1","OSType":1,"Online":1,"OnlineT":"on"},{"Version":"2.1.1.2","OSType":1,"Online":1,"OnlineT":"on"}]'; select Version from openjson(@jsondata) with( Version varchar(50) ) |