正如我之前提到的，我们这里除了会讲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) ) |
