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

本文介绍了在SQL Server 2016中如何处理JSON数据,包括使用JSON_VALUE、JSON_QUERY和OPENJSON函数进行数据提取和转换。示例展示了如何从JSON文本中提取值和对象,以及如何使用OPENJSON将JSON数组转换为关系型数据。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

正如我之前提到的,我们这里除了会讲Power BI 之外,还会给大家补充周边与之相关的知识,今天我们就来讲讲在SQL 2016中如何处理JSON 数据对象。

SQL Server中的JSON函数使您能够分析和查询JSON数据,将JSON转换为关系格式以及将SQL查询结果导出为JSON文本。6aa89ba28f316ab599bfc31b69ed1a5c.png

如果您有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

8feaf39405265eefdd39f1d967acb5b6.png

6e364d8ee357d13adb86c4372385446a.png

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)

 )

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值