您好,登錄后才能下訂單哦!
這篇文章主要介紹“SQL Server中的JSON函數(shù)怎么使用”,在日常操作中,相信很多人在SQL Server中的JSON函數(shù)怎么使用問題上存在疑惑,小編查閱了各式資料,整理出簡單好用的操作方法,希望對(duì)大家解答”SQL Server中的JSON函數(shù)怎么使用”的疑惑有所幫助!接下來,請(qǐng)跟著小編一起來學(xué)習(xí)吧!
下面是我們熟悉的SELECT及輸出格式,后面對(duì)JSON的演示基于此SQL:
要將SELECT語句的結(jié)果以JSON輸出,最簡單的方法是在后面加上FOR JSON AUTO:
若要為FOR JSON加上Root Key,可以用ROOT選項(xiàng)來自定義ROOT 節(jié)點(diǎn)的名稱:
若要自定義輸出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)。
為NULL的數(shù)據(jù)在輸出JSON時(shí),會(huì)被忽略,若想要讓NULL的字段也顯示出來,可以加上選項(xiàng)INCLUDE_NULL_VALUES,該選項(xiàng)也適用于AUTO。
比如下面的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}]}
實(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
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');
返回:OK
print json_value(@param, '$.info.address.town'); print json_value(@param, '$.info.tags[1]');
返回:Bristol,Water polo
print json_query(@param, '$.info'); { "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }
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');
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í)就結(jié)束了,希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí),快去試試吧!若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí),請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站,小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。