SQL?Server中JSON函數(shù)的用法詳解
更新時(shí)間：2022年05月21日 16:16:30 作者：springsnow
本文詳細(xì)講解了SQL?Server中JSON函數(shù)的用法，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(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及輸出格式，后面對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對象的層次結(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對象的層次結(jié)構(gòu)，列別名中可以包含“.”，JSON的成員層次結(jié)構(gòu)將會與別名中的層次結(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í)，會被忽略，若想要讓NULL的字段也顯示出來，可以加上選項(xiàng)INCLUDE_NULL_VALUES，該選項(xiàng)也適用于AUTO。
6、列的別名，可以增加帶有層級關(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 字符串中提取對象或數(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ù)的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
SQL查詢語句行轉(zhuǎn)列橫向顯示實(shí)例解析
這篇文章主要為大家詳細(xì)介紹了兩個(gè)關(guān)于SQL查詢語句行轉(zhuǎn)列橫向顯示的實(shí)例解析，感興趣的小伙伴們可以參考一下2016-05-05
這篇文章主要介紹了SQL Server DBA日常工作中檢查數(shù)據(jù)庫和表常用SQL語句,需要的朋友可以參考下2014-03-03
數(shù)據(jù)庫常見的join方式有三種：inner join, left outter join, right outter join(還有一種full join，因不常用，本文不討論)。這三種連接方式都是將兩個(gè)以上的表通過on條件語句，拼成一個(gè)大表。2009-06-06
sqlserver復(fù)制數(shù)據(jù)庫的方法步驟(圖文)
這篇文章主要介紹了sqlserver復(fù)制數(shù)據(jù)庫的方法步驟(圖文)，文中通過圖文示例介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-04-04
微信小程序 滾動(dòng)選擇器(時(shí)間日期)詳解及實(shí)例代碼
這篇文章主要介紹了微信小程序 滾動(dòng)選擇器(時(shí)間日期)詳解及實(shí)例代碼的相關(guān)資料,需要的朋友可以參考下2017-02-02
相信大家對于SQL Transcation再熟悉不過，它確保了數(shù)據(jù)庫的數(shù)據(jù)一致性和安全性，尤其在對數(shù)據(jù)執(zhí)行增刪時(shí)，如果發(fā)生異常和錯(cuò)誤它就會觸發(fā)事務(wù)回滾，從而確保了我們數(shù)據(jù)的一致性和安全性，下面我們將通過分四部分介紹事件（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語句練習(xí)實(shí)例之七 剔除不需要的記錄行
相信大家肯定經(jīng)常會把數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，但是可能會有些記錄行的所有列的數(shù)據(jù)是null，這為null的數(shù)據(jù)是我們不需要2011-10-10
mybatis動(dòng)態(tài)sql常用場景總結(jié)
在平時(shí)開發(fā)中針對動(dòng)態(tài)sql經(jīng)常會使用到，為了加深對動(dòng)態(tài)sql的熟練度，小編給大家分享一篇教程關(guān)于mybatis動(dòng)態(tài)sql常用場景總結(jié)，需要的朋友可以參考下2021-08-08
深入SQL截取字符串(substring與patindex)的詳解
本篇文章是對SQL截取字符串(substring與patindex的使用)進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06