SQL?Server中JSON函數(shù)的用法詳解
更新時(shí)間：2022年05月21日 16:16:30 作者：springsnow
本文詳細(xì)講解了SQL?Server中JSON函數(shù)的用法，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下
SQL Server 2005開(kāi)始支持XML數(shù)據(jù)類(lèi)型，提供原生的XML數(shù)據(jù)類(lèi)型、XML索引及各種管理或輸出XML格式的函數(shù)。
隨著JSON的流行，SQL Server 2016開(kāi)始支持JSON數(shù)據(jù)類(lèi)型，不僅可以直接輸出JSON格式的結(jié)果集，還能讀取JSON格式的數(shù)據(jù)。
下面是我們熟悉的SELECT及輸出格式，后面對(duì)JSON的演示基于此SQL：
一、 將查詢結(jié)果輸出JSON格式
1、FOR JSON AUTO：SELECT語(yǔ)句的結(jié)果以JSON輸出。
要將SELECT語(yǔ)句的結(jié)果以JSON輸出，最簡(jiǎn)單的方法是在后面加上FOR JSON AUTO:
2、FOR JSON AUTO，Root（’’） ：為JOSN加上根節(jié)點(diǎn)
若要為FOR JSON加上Root Key，可以用ROOT選項(xiàng)來(lái)自定義ROOT 節(jié)點(diǎn)的名稱(chēng)：
3、FOR JSON PATH輸出：可通過(guò)列別名來(lái)定義JSON對(duì)象的層次結(jié)構(gòu)
若要自定義輸出JSON格式的結(jié)構(gòu)時(shí)，必須使用JSONPATH。
- FOR JSON Auto，自動(dòng)按照查詢語(yǔ)句中使用的表結(jié)構(gòu)來(lái)創(chuàng)建嵌套的JSON子數(shù)組，類(lèi)似于For Xml Auto特性。
- FOR JSON Path，通過(guò)列名或者列別名來(lái)定義JSON對(duì)象的層次結(jié)構(gòu)，列別名中可以包含“.”，JSON的成員層次結(jié)構(gòu)將會(huì)與別名中的層次結(jié)構(gòu)保持一致。
這個(gè)特性非常類(lèi)似于早期SQL Server版本中的For Xml Path子句，可以使用斜線來(lái)定義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的字段也顯示出來(lái)，可以加上選項(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、通過(guò)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：測(cè)試字符串是否包含有效 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ù)組。返回類(lèi)型為 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)行了簡(jiǎn)單介紹，主要有如下要點(diǎn)：
- JSON能在SQLServer2016中高效的使用，但是JSON并不是原生數(shù)據(jù)類(lèi)型;
- 如果使用JSON格式必須為輸出結(jié)果是表達(dá)式的提供別名;
- JSON_VALUE 和 JSON_QUERY 函數(shù)轉(zhuǎn)移和獲取Varchar格式的數(shù)據(jù)，因此必須將數(shù)據(jù)轉(zhuǎn)譯成你需要的類(lèi)型。
- 在計(jì)算列的幫助下查詢JSON可以使用索引進(jìn)行優(yōu)化。
到此這篇關(guān)于SQL Server使用JSON函數(shù)的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
SQL查詢語(yǔ)句行轉(zhuǎn)列橫向顯示實(shí)例解析
這篇文章主要為大家詳細(xì)介紹了兩個(gè)關(guān)于SQL查詢語(yǔ)句行轉(zhuǎn)列橫向顯示的實(shí)例解析，感興趣的小伙伴們可以參考一下2016-05-05
這篇文章主要介紹了SQL Server DBA日常工作中檢查數(shù)據(jù)庫(kù)和表常用SQL語(yǔ)句,需要的朋友可以參考下2014-03-03
數(shù)據(jù)庫(kù)常見(jiàn)的join方式有三種：inner join, left outter join, right outter join(還有一種full join，因不常用，本文不討論)。這三種連接方式都是將兩個(gè)以上的表通過(guò)on條件語(yǔ)句，拼成一個(gè)大表。2009-06-06
sqlserver復(fù)制數(shù)據(jù)庫(kù)的方法步驟(圖文)
這篇文章主要介紹了sqlserver復(fù)制數(shù)據(jù)庫(kù)的方法步驟(圖文)，文中通過(guò)圖文示例介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-04-04
微信小程序 滾動(dòng)選擇器(時(shí)間日期)詳解及實(shí)例代碼
這篇文章主要介紹了微信小程序 滾動(dòng)選擇器(時(shí)間日期)詳解及實(shí)例代碼的相關(guān)資料,需要的朋友可以參考下2017-02-02
相信大家對(duì)于SQL Transcation再熟悉不過(guò)，它確保了數(shù)據(jù)庫(kù)的數(shù)據(jù)一致性和安全性，尤其在對(duì)數(shù)據(jù)執(zhí)行增刪時(shí)，如果發(fā)生異常和錯(cuò)誤它就會(huì)觸發(fā)事務(wù)回滾，從而確保了我們數(shù)據(jù)的一致性和安全性，下面我們將通過(guò)分四部分介紹事件（Transcation）2012-08-08
安裝sql server 2008 management提示已安裝 SQL Server 2005 Express的解決
SQL Server 2005 Express 工具不是sql server 2005 management，不用卸載sql server 2005 management，用一個(gè)小技巧就行2013-07-07
SQL語(yǔ)句練習(xí)實(shí)例之七 剔除不需要的記錄行
相信大家肯定經(jīng)常會(huì)把數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫(kù)中，但是可能會(huì)有些記錄行的所有列的數(shù)據(jù)是null，這為null的數(shù)據(jù)是我們不需要2011-10-10
mybatis動(dòng)態(tài)sql常用場(chǎng)景總結(jié)
在平時(shí)開(kāi)發(fā)中針對(duì)動(dòng)態(tài)sql經(jīng)常會(huì)使用到，為了加深對(duì)動(dòng)態(tài)sql的熟練度，小編給大家分享一篇教程關(guān)于mybatis動(dòng)態(tài)sql常用場(chǎng)景總結(jié)，需要的朋友可以參考下2021-08-08
深入SQL截取字符串(substring與patindex)的詳解
本篇文章是對(duì)SQL截取字符串(substring與patindex的使用)進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06