vba 提取 json某个值_SQL Server 2016中的JSON

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