SQL?Server中JSON函數(shù)的用法詳解
更新時(shí)間：2022年05月21日 16:16:30 作者：springsnow
本文詳細(xì)講解了SQL?Server中JSON函數(shù)的用法，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
SQL Server 2005開始支持XML數(shù)據(jù)類型，提供原生的XML數(shù)據(jù)類型、XML索引及各種管理或輸出XML格式的函數(shù)。
隨著JSON的流行，SQL Server 2016開始支持JSON數(shù)據(jù)類型，不僅可以直接輸出JSON格式的結(jié)果集，還能讀取JSON格式的數(shù)據(jù)。
下面是我們熟悉的SELECT及輸出格式，后面對(duì)JSON的演示基于此SQL：
一、 將查詢結(jié)果輸出JSON格式
1、FOR JSON AUTO：SELECT語句的結(jié)果以JSON輸出。
要將SELECT語句的結(jié)果以JSON輸出，最簡單的方法是在后面加上FOR JSON AUTO:
2、FOR JSON AUTO，Root（’’） ：為JOSN加上根節(jié)點(diǎn)
若要為FOR JSON加上Root Key，可以用ROOT選項(xiàng)來自定義ROOT 節(jié)點(diǎn)的名稱：
3、FOR JSON PATH輸出：可通過列別名來定義JSON對(duì)象的層次結(jié)構(gòu)
若要自定義輸出JSON格式的結(jié)構(gòu)時(shí)，必須使用JSONPATH。
- FOR JSON Auto，自動(dòng)按照查詢語句中使用的表結(jié)構(gòu)來創(chuàng)建嵌套的JSON子數(shù)組，類似于For Xml Auto特性。
- FOR JSON Path，通過列名或者列別名來定義JSON對(duì)象的層次結(jié)構(gòu)，列別名中可以包含“.”，JSON的成員層次結(jié)構(gòu)將會(huì)與別名中的層次結(jié)構(gòu)保持一致。
這個(gè)特性非常類似于早期SQL Server版本中的For Xml Path子句，可以使用斜線來定義xml的層次結(jié)構(gòu)。
4、FOR JSON PATH+ROOT輸出：為JOSN加上根節(jié)點(diǎn)
5、INCLUDE_NULL_VALUES：值null的字段需要顯示出現(xiàn)。
為NULL的數(shù)據(jù)在輸出JSON時(shí)，會(huì)被忽略，若想要讓NULL的字段也顯示出來，可以加上選項(xiàng)INCLUDE_NULL_VALUES，該選項(xiàng)也適用于AUTO。
6、列的別名，可以增加帶有層級(jí)關(guān)系的節(jié)點(diǎn)。
比如下面的SQL，增加了一個(gè)“SN”節(jié)點(diǎn)，把欄位SERNUM和CLIMAT放在里面：
演示實(shí)例：
select TOP (2) id, Plies, Createtime from [dbo].[B3PliesData] ORDER BY ID ;
--1178 3 2020-07-21 14:33:18.480
--1179 3 2020-07-21 14:36:27.457
select TOP (2) id, Plies as [myObject.Plies], Createtime as [myObject.Createtime] from [dbo].[B3PliesData] ORDER BY ID for json auto;
--[{"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"},{"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"}]
select TOP (2) id, Plies, Createtime from [dbo].[B3PliesData] ORDER BY ID for json auto ,root('myRoot') ;
--{"myRoot":[{"id":1178,"Plies":3,"Createtime":"2020-07-21T14:33:18.480"},{"id":1179,"Plies":3,"Createtime":"2020-07-21T14:36:27.457"}]}
select TOP (2) id, Plies as [myObject.Plies], Createtime as [myObject.Createtime] from [dbo].[B3PliesData] ORDER BY ID for json path;
--[{"id":1178,"myObject":{"Plies":3,"Createtime":"2020-07-21T14:33:18.480"}},{"id":1179,"myObject":{"Plies":3,"Createtime":"2020-07-21T14:36:27.457"}}]
select TOP (2) id, Plies, Createtime,null as mynull from [dbo].[B3PliesData] ORDER BY ID for json path,root('myRoot');
--{"myRoot":[{"id":1178,"Plies":3,"Createtime":"2020-07-21T14:33:18.480"},{"id":1179,"Plies":3,"Createtime":"2020-07-21T14:36:27.457"}]}
select TOP (2) id, Plies, Createtime,null as mynull from [dbo].[B3PliesData] ORDER BY ID for json path,root('myRoot'),include_null_values;
--{"myRoot":[{"id":1178,"Plies":3,"Createtime":"2020-07-21T14:33:18.480","mynull":null},{"id":1179,"Plies":3,"Createtime":"2020-07-21T14:36:27.457","mynull":null}]}二、 解析JSON格式的數(shù)據(jù)
1、使用OPENJSON()函數(shù)：
2、通過WITH選項(xiàng)，自定義輸出列：
實(shí)例演示：
-------------1、-------------
declare @json as varchar(8000)
set @json='[
{"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"},
{"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"}]'
select * from openjson(@json);
--key value type
--0 {"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"} 5
--1 {"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"} 5
-------------2、-------------
declare @json1 as varchar(8000)
set @json1='[
{"id":1178,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:33:18.480"},
{"id":1179,"myObject.Plies":3,"myObject.Createtime":"2020-07-21T14:36:27.457"}]
'
select * from openjson(@json1)
with(
id varchar(10) '$.id',
Plies int '$."myObject.Plies"',
Createtime datetime '$."myObject.Createtime"'
);
--id Plies Createtime
--1178 3 2020-07-21 14:33:18.480
--1179 3 2020-07-21 14:36:27.457
-------------3、-------------
declare @json2 as varchar(8000)
set @json2='{"myRoot":[
{"id":1178,"myObject":{"Plies":3,"Createtime":"2020-07-21T14:33:18.480"}},
{"id":1179,"myObject":{"Plies":3,"Createtime":"2020-07-21T14:36:27.457"}}
]}'
select * from openjson(@json2,'$.myRoot')
with(
id varchar(10) ,
Plies int '$.myObject.Plies',
Createtime datetime '$.myObject.Createtime'
);
--id Plies Createtime
--1178 3 2020-07-21 14:33:18.480
--1179 3 2020-07-21 14:36:27.457三、JSON函數(shù)
declare @param nvarchar(max);
set @param = N'{
"info":{
"type":1,
"address":{
"town":"Bristol",
"county":"Avon",
"country":"England"
},
"tags":["Sport", "Water polo"]
},
"type":"Basic"
}';1、ISJSON：測試字符串是否包含有效 JSON。
print iif(isjson(@param) > 0, 'OK', 'NO');
返回：OK
2、JSON_VALUE ：從 JSON 字符串中提取標(biāo)量值。
print json_value(@param, '$.info.address.town'); print json_value(@param, '$.info.tags[1]');
返回：Bristol，Water polo
3、JSON_QUERY ：從 JSON 字符串中提取對(duì)象或數(shù)組。返回類型為 nvarchar(max) 的 JSON 片段
print json_query(@param, '$.info');
{
"type":1,
"address":{
"town":"Bristol",
"county":"Avon",
"country":"England"
},
"tags":["Sport", "Water polo"]
}4、JSON_MODIFY :更新 JSON 字符串中屬性的值，并返回已更新的 JSON 字符串。
print json_modify(@param, '$.info.address.town', 'London');
返回：
{
"info":{
"type":1,
"address":{
"town":"London",
"county":"Avon",
"country":"England"
},
"tags":["Sport", "Water polo"]
},
"type":"Basic"
}實(shí)例演示：
declare @param nvarchar(max);
set @param=N'{
"info":{
"type":1,
"address":{
"town":"Bristol",
"county":"Avon",
"country":"England"
},
"tags":["Sport", "Water polo"]
},
"type":"Basic"
}';
print iif(isjson(@param)>0, 'OK', 'NO');
print json_query(@param);
print json_value(@param, '$.info.address.town');
print json_value(@param, '$.info.tags[1]');
print json_query(@param, '$.info');
print json_query('["2020-1-8","2020-1-9"]');
print json_modify(@param, '$.info.address.town', 'London');四、注意事項(xiàng)
SQL2016 中的新增的內(nèi)置JSON進(jìn)行了簡單介紹，主要有如下要點(diǎn)：
- JSON能在SQLServer2016中高效的使用，但是JSON并不是原生數(shù)據(jù)類型;
- 如果使用JSON格式必須為輸出結(jié)果是表達(dá)式的提供別名;
- JSON_VALUE 和 JSON_QUERY 函數(shù)轉(zhuǎn)移和獲取Varchar格式的數(shù)據(jù)，因此必須將數(shù)據(jù)轉(zhuǎn)譯成你需要的類型。
- 在計(jì)算列的幫助下查詢JSON可以使用索引進(jìn)行優(yōu)化。
到此這篇關(guān)于SQL Server使用JSON函數(shù)的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要介紹了SQL中的DQL查詢語言，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2019-05-05
淺談SQL Server中統(tǒng)計(jì)對(duì)于查詢的影響分析
SQL Server查詢分析器是基于開銷的。通常來講，查詢分析器會(huì)根據(jù)謂詞來確定該如何選擇高效的查詢路線,比如該選擇哪個(gè)索引2012-05-05
最近需要用的oracle，所以大家好好的學(xué)習(xí)下基礎(chǔ)并整理下資料，希望能幫助到需要的朋友。2011-12-12
限制MSSQL使用內(nèi)存的方法(針對(duì)內(nèi)存小的云主機(jī))
使用騰訊云Windows云服務(wù)器安裝SQL SERVER數(shù)據(jù)庫,在我們使用的過程中,有時(shí)候在任務(wù)管理器發(fā)現(xiàn)SQL SERVE的進(jìn)程占用很高的內(nèi)存和CPU,本文介紹如何限制SQL SERVER內(nèi)存占用2023-08-08
SQL Server通過重建方式還原master數(shù)據(jù)庫
這篇文章主要為大家詳細(xì)介紹了SQL Server通過重建方式還原master數(shù)據(jù)庫的相關(guān)資料,需要的朋友可以參考下2016-09-09
這篇文章主要介紹了數(shù)據(jù)庫 關(guān)鍵字一覽表的相關(guān)資料,需要的朋友可以參考下2016-10-10
本文介紹了如何使用SQL語言查詢多個(gè)Excel表格,通過將所有Excel表格放入一個(gè).xlsx文件中,并使用pandas和pandasql庫進(jìn)行讀取和查詢,此外,還探討了如何實(shí)現(xiàn)一個(gè)自定義的calcite文件適配器來支持Excel文件的查詢,感興趣的朋友一起看看吧2025-01-01
防止SQLSERVER的事件探查器跟蹤軟件方法，兩種方法的原理相同，需要的朋友可以看下。2009-10-10
sqlserver CONVERT()函數(shù)用法小結(jié)
文章分析總結(jié)了關(guān)于CONVERT()函數(shù)在操作日期時(shí)的一些常見的用法分析下面來看看2012-09-09
vs code連接sql server數(shù)據(jù)庫步驟及遇到的問題小結(jié)
這篇文章主要介紹了用vs code連接sql server數(shù)據(jù)庫步驟及遇到的問題,本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-05-05