網(wǎng)頁設(shè)計模板套用步驟seo網(wǎng)絡(luò)搜索引擎優(yōu)化
目錄
一、表轉(zhuǎn)Json
1.使用?for json path
2.如何返回單個Json?
3.如何給返回的Json增加一個根節(jié)點呢?
4.如何給返回的Json增加上一個節(jié)點?
二、對Json基本操作
1.判斷給的字符串是否是Json格式
2.從 JSON 字符串中提取標(biāo)量值
3.?從 JSON 字符串中提取對象或數(shù)組
4.?更新 JSON 字符串中屬性的值,并返回已更新的 JSON 字符串
三、Json轉(zhuǎn)成表
?1.使用OPENJSON??WITH?
?2.多層嵌套的Json如何轉(zhuǎn)成表呢?
總結(jié)
一、表轉(zhuǎn)Json
1.使用?for json path
代碼如下(示例):
set ROWCOUNT 2select * from AdministrativeDivision for json path
運行結(jié)果
[{"ID":100000,"Name":"中國","ParentId":0,"ShortName":"中國","LevelType":0,"CityCode":"","ZipCode":"","MergerName":"中國","lng":116.3683244,"Lat":39.915085,"Pinyin":"China"},{"ID":110000,"Name":"北京","ParentId":100000,"ShortName":"北京","LevelType":1,"CityCode":"","ZipCode":"","MergerName":"中國,北京","lng":116.405285,"Lat":39.904989,"Pinyin":"Beijing"}
]
表字段為key,對應(yīng)的值為Value,
?等效與 for json auto?
set ROWCOUNT 2select * from AdministrativeDivision for json auto
2.如何返回單個Json?
代碼如下(示例):
set ROWCOUNT 1;
select * from AdministrativeDivision for json auto
運行結(jié)果
[{"ID":100000,"Name":"中國","ParentId":0,"ShortName":"中國","LevelType":0,"CityCode":"","ZipCode":"","MergerName":"中國","lng":116.3683244,"Lat":39.915085,"Pinyin":"China"}
]
返回了一個Json對象,是以數(shù)組的形式返回的,沒有達(dá)到我們想要的效果
使用這個? WITHOUT_ARRAY_WRAPPER??去掉最外層[ ]中括號的包裹
代碼如下(示例):
set ROWCOUNT 1;select * from AdministrativeDivision for json auto, WITHOUT_ARRAY_WRAPPER
運行結(jié)果
{"ID":100000,"Name":"中國","ParentId":0,"ShortName":"中國","LevelType":0,"CityCode":"","ZipCode":"","MergerName":"中國","lng":116.3683244,"Lat":39.915085,"Pinyin":"China"
}
3.如何給返回的Json增加一個根節(jié)點呢?
代碼如下(示例):
set ROWCOUNT 1select * from AdministrativeDivision for json path ,root('業(yè)務(wù)信息')
運行結(jié)果
{"業(yè)務(wù)信息":[{"ID":100000,"Name":"中國","ParentId":0,"ShortName":"中國","LevelType":0,"CityCode":"","ZipCode":"","MergerName":"中國","lng":116.3683244,"Lat":39.915085,"Pinyin":"China"}]
}
注意??root('')? 和 WITHOUT_ARRAY_WRAPPER 不能同時使用
?4.如何給返回的Json增加上一個節(jié)點?
代碼如下(示例):
set ROWCOUNT 0;
select a.ID, a.Name , b.ID as 'child.Id' ,b.Name as 'child.Name' from AdministrativeDivision a
inner join AdministrativeDivision b on a.ID=b.ParentId
where a.Name like '%湖北省%'
for json path
運行結(jié)果
[{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市"}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黃石市"}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市"}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市"}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄陽市"}},{"ID":420000,"Name":"湖北省","child":{"Id":420700,"Name":"鄂州市"}},{"ID":420000,"Name":"湖北省","child":{"Id":420800,"Name":"荊門市"}},{"ID":420000,"Name":"湖北省","child":{"Id":420900,"Name":"孝感市"}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荊州市"}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市"}},{"ID":420000,"Name":"湖北省","child":{"Id":421200,"Name":"咸寧市"}},{"ID":420000,"Name":"湖北省","child":{"Id":421300,"Name":"隨州市"}},{"ID":420000,"Name":"湖北省","child":{"Id":422800,"Name":"恩施土家族苗族自治州"}},{"ID":420000,"Name":"湖北省","child":{"Id":429000,"Name":"直轄縣級"}}
]
增加多個子節(jié)點 如下
set ROWCOUNT 0;
select a.ID, a.Name , b.ID as 'child.Id' ,b.Name as 'child.Name',c.ID as 'child.child.Id' ,c.Name as 'child.child.Name'
from AdministrativeDivision a
inner join AdministrativeDivision b on a.ID=b.ParentId
inner join AdministrativeDivision c on c.ParentId=b.ID
where a.Name like '%湖北省%'
for json path
運行結(jié)果如下
[{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420102,"Name":"江岸區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420103,"Name":"江漢區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420104,"Name":"硚口區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420105,"Name":"漢陽區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420106,"Name":"武昌區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420107,"Name":"青山區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420111,"Name":"洪山區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420112,"Name":"東西湖區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420113,"Name":"漢南區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420114,"Name":"蔡甸區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420115,"Name":"江夏區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420116,"Name":"黃陂區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武漢市","child":{"Id":420117,"Name":"新洲區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黃石市","child":{"Id":420202,"Name":"黃石港區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黃石市","child":{"Id":420203,"Name":"西塞山區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黃石市","child":{"Id":420204,"Name":"下陸區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黃石市","child":{"Id":420205,"Name":"鐵山區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黃石市","child":{"Id":420222,"Name":"陽新縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黃石市","child":{"Id":420281,"Name":"大冶市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市","child":{"Id":420302,"Name":"茅箭區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市","child":{"Id":420303,"Name":"張灣區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市","child":{"Id":420304,"Name":"鄖陽區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市","child":{"Id":420322,"Name":"鄖西縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市","child":{"Id":420323,"Name":"竹山縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市","child":{"Id":420324,"Name":"竹溪縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市","child":{"Id":420325,"Name":"房縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市","child":{"Id":420381,"Name":"丹江口市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420502,"Name":"西陵區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420503,"Name":"伍家崗區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420504,"Name":"點軍區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420505,"Name":"猇亭區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420506,"Name":"夷陵區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420525,"Name":"遠(yuǎn)安縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420526,"Name":"興山縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420527,"Name":"秭歸縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420528,"Name":"長陽土家族自治縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420529,"Name":"五峰土家族自治縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420581,"Name":"宜都市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420582,"Name":"當(dāng)陽市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420583,"Name":"枝江市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄陽市","child":{"Id":420602,"Name":"襄城區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄陽市","child":{"Id":420606,"Name":"樊城區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄陽市","child":{"Id":420607,"Name":"襄州區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄陽市","child":{"Id":420624,"Name":"南漳縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄陽市","child":{"Id":420625,"Name":"谷城縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄陽市","child":{"Id":420626,"Name":"??悼h"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄陽市","child":{"Id":420682,"Name":"老河口市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄陽市","child":{"Id":420683,"Name":"棗陽市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄陽市","child":{"Id":420684,"Name":"宜城市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420700,"Name":"鄂州市","child":{"Id":420702,"Name":"梁子湖區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420700,"Name":"鄂州市","child":{"Id":420703,"Name":"華容區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420700,"Name":"鄂州市","child":{"Id":420704,"Name":"鄂城區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420800,"Name":"荊門市","child":{"Id":420802,"Name":"東寶區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420800,"Name":"荊門市","child":{"Id":420804,"Name":"掇刀區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420800,"Name":"荊門市","child":{"Id":420821,"Name":"京山縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420800,"Name":"荊門市","child":{"Id":420822,"Name":"沙洋縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420800,"Name":"荊門市","child":{"Id":420881,"Name":"鐘祥市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420900,"Name":"孝感市","child":{"Id":420902,"Name":"孝南區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420900,"Name":"孝感市","child":{"Id":420921,"Name":"孝昌縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420900,"Name":"孝感市","child":{"Id":420922,"Name":"大悟縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420900,"Name":"孝感市","child":{"Id":420923,"Name":"云夢縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420900,"Name":"孝感市","child":{"Id":420981,"Name":"應(yīng)城市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420900,"Name":"孝感市","child":{"Id":420982,"Name":"安陸市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420900,"Name":"孝感市","child":{"Id":420984,"Name":"漢川市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荊州市","child":{"Id":421002,"Name":"沙市區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荊州市","child":{"Id":421003,"Name":"荊州區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荊州市","child":{"Id":421022,"Name":"公安縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荊州市","child":{"Id":421023,"Name":"監(jiān)利縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荊州市","child":{"Id":421024,"Name":"江陵縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荊州市","child":{"Id":421081,"Name":"石首市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荊州市","child":{"Id":421083,"Name":"洪湖市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荊州市","child":{"Id":421087,"Name":"松滋市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市","child":{"Id":421102,"Name":"黃州區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市","child":{"Id":421121,"Name":"團風(fēng)縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市","child":{"Id":421122,"Name":"紅安縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市","child":{"Id":421123,"Name":"羅田縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市","child":{"Id":421124,"Name":"英山縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市","child":{"Id":421125,"Name":"浠水縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市","child":{"Id":421126,"Name":"蘄春縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市","child":{"Id":421127,"Name":"黃梅縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市","child":{"Id":421181,"Name":"麻城市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黃岡市","child":{"Id":421182,"Name":"武穴市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421200,"Name":"咸寧市","child":{"Id":421202,"Name":"咸安區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421200,"Name":"咸寧市","child":{"Id":421221,"Name":"嘉魚縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421200,"Name":"咸寧市","child":{"Id":421222,"Name":"通城縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421200,"Name":"咸寧市","child":{"Id":421223,"Name":"崇陽縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421200,"Name":"咸寧市","child":{"Id":421224,"Name":"通山縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421200,"Name":"咸寧市","child":{"Id":421281,"Name":"赤壁市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421300,"Name":"隨州市","child":{"Id":421303,"Name":"曾都區(qū)"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421300,"Name":"隨州市","child":{"Id":421321,"Name":"隨縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421300,"Name":"隨州市","child":{"Id":421381,"Name":"廣水市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":422800,"Name":"恩施土家族苗族自治州","child":{"Id":422801,"Name":"恩施市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":422800,"Name":"恩施土家族苗族自治州","child":{"Id":422802,"Name":"利川市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":422800,"Name":"恩施土家族苗族自治州","child":{"Id":422822,"Name":"建始縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":422800,"Name":"恩施土家族苗族自治州","child":{"Id":422823,"Name":"巴東縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":422800,"Name":"恩施土家族苗族自治州","child":{"Id":422825,"Name":"宣恩縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":422800,"Name":"恩施土家族苗族自治州","child":{"Id":422826,"Name":"咸豐縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":422800,"Name":"恩施土家族苗族自治州","child":{"Id":422827,"Name":"來鳳縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":422800,"Name":"恩施土家族苗族自治州","child":{"Id":422828,"Name":"鶴峰縣"}}},{"ID":420000,"Name":"湖北省","child":{"Id":429000,"Name":"直轄縣級","child":{"Id":429004,"Name":"仙桃市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":429000,"Name":"直轄縣級","child":{"Id":429005,"Name":"潛江市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":429000,"Name":"直轄縣級","child":{"Id":429006,"Name":"天門市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":429000,"Name":"直轄縣級","child":{"Id":429021,"Name":"神農(nóng)架林區(qū)"}}}
]
從結(jié)果來看 并沒有達(dá)到我們想要的效果? 同一個父節(jié)點,沒有包含到所有的子節(jié)點,?
如何解決這種情況呢 也是有解決方法的
代碼如下(示例):
set ROWCOUNT 0;select a.ID, a.Name ,
(select b.ID,b.Name,
(select c.ID,c.Name from AdministrativeDivision c where c.ParentId=b.ID
for json path
) as child
from AdministrativeDivision b where a.ID=b.ParentId
for json path
) as child
from AdministrativeDivision a
where a.Name like '%湖北省%'
for json path
運行結(jié)果
[{"ID":420000,"Name":"湖北省","child":[{"ID":420100,"Name":"武漢市","child":[{"ID":420102,"Name":"江岸區(qū)"},{"ID":420103,"Name":"江漢區(qū)"},{"ID":420104,"Name":"硚口區(qū)"},{"ID":420105,"Name":"漢陽區(qū)"},{"ID":420106,"Name":"武昌區(qū)"},{"ID":420107,"Name":"青山區(qū)"},{"ID":420111,"Name":"洪山區(qū)"},{"ID":420112,"Name":"東西湖區(qū)"},{"ID":420113,"Name":"漢南區(qū)"},{"ID":420114,"Name":"蔡甸區(qū)"},{"ID":420115,"Name":"江夏區(qū)"},{"ID":420116,"Name":"黃陂區(qū)"},{"ID":420117,"Name":"新洲區(qū)"}]},{"ID":420200,"Name":"黃石市","child":[{"ID":420202,"Name":"黃石港區(qū)"},{"ID":420203,"Name":"西塞山區(qū)"},{"ID":420204,"Name":"下陸區(qū)"},{"ID":420205,"Name":"鐵山區(qū)"},{"ID":420222,"Name":"陽新縣"},{"ID":420281,"Name":"大冶市"}]},{"ID":420300,"Name":"十堰市","child":[{"ID":420302,"Name":"茅箭區(qū)"},{"ID":420303,"Name":"張灣區(qū)"},{"ID":420304,"Name":"鄖陽區(qū)"},{"ID":420322,"Name":"鄖西縣"},{"ID":420323,"Name":"竹山縣"},{"ID":420324,"Name":"竹溪縣"},{"ID":420325,"Name":"房縣"},{"ID":420381,"Name":"丹江口市"}]},{"ID":420500,"Name":"宜昌市","child":[{"ID":420502,"Name":"西陵區(qū)"},{"ID":420503,"Name":"伍家崗區(qū)"},{"ID":420504,"Name":"點軍區(qū)"},{"ID":420505,"Name":"猇亭區(qū)"},{"ID":420506,"Name":"夷陵區(qū)"},{"ID":420525,"Name":"遠(yuǎn)安縣"},{"ID":420526,"Name":"興山縣"},{"ID":420527,"Name":"秭歸縣"},{"ID":420528,"Name":"長陽土家族自治縣"},{"ID":420529,"Name":"五峰土家族自治縣"},{"ID":420581,"Name":"宜都市"},{"ID":420582,"Name":"當(dāng)陽市"},{"ID":420583,"Name":"枝江市"}]},{"ID":420600,"Name":"襄陽市","child":[{"ID":420602,"Name":"襄城區(qū)"},{"ID":420606,"Name":"樊城區(qū)"},{"ID":420607,"Name":"襄州區(qū)"},{"ID":420624,"Name":"南漳縣"},{"ID":420625,"Name":"谷城縣"},{"ID":420626,"Name":"??悼h"},{"ID":420682,"Name":"老河口市"},{"ID":420683,"Name":"棗陽市"},{"ID":420684,"Name":"宜城市"}]},{"ID":420700,"Name":"鄂州市","child":[{"ID":420702,"Name":"梁子湖區(qū)"},{"ID":420703,"Name":"華容區(qū)"},{"ID":420704,"Name":"鄂城區(qū)"}]},{"ID":420800,"Name":"荊門市","child":[{"ID":420802,"Name":"東寶區(qū)"},{"ID":420804,"Name":"掇刀區(qū)"},{"ID":420821,"Name":"京山縣"},{"ID":420822,"Name":"沙洋縣"},{"ID":420881,"Name":"鐘祥市"}]},{"ID":420900,"Name":"孝感市","child":[{"ID":420902,"Name":"孝南區(qū)"},{"ID":420921,"Name":"孝昌縣"},{"ID":420922,"Name":"大悟縣"},{"ID":420923,"Name":"云夢縣"},{"ID":420981,"Name":"應(yīng)城市"},{"ID":420982,"Name":"安陸市"},{"ID":420984,"Name":"漢川市"}]},{"ID":421000,"Name":"荊州市","child":[{"ID":421002,"Name":"沙市區(qū)"},{"ID":421003,"Name":"荊州區(qū)"},{"ID":421022,"Name":"公安縣"},{"ID":421023,"Name":"監(jiān)利縣"},{"ID":421024,"Name":"江陵縣"},{"ID":421081,"Name":"石首市"},{"ID":421083,"Name":"洪湖市"},{"ID":421087,"Name":"松滋市"}]},{"ID":421100,"Name":"黃岡市","child":[{"ID":421102,"Name":"黃州區(qū)"},{"ID":421121,"Name":"團風(fēng)縣"},{"ID":421122,"Name":"紅安縣"},{"ID":421123,"Name":"羅田縣"},{"ID":421124,"Name":"英山縣"},{"ID":421125,"Name":"浠水縣"},{"ID":421126,"Name":"蘄春縣"},{"ID":421127,"Name":"黃梅縣"},{"ID":421181,"Name":"麻城市"},{"ID":421182,"Name":"武穴市"}]},{"ID":421200,"Name":"咸寧市","child":[{"ID":421202,"Name":"咸安區(qū)"},{"ID":421221,"Name":"嘉魚縣"},{"ID":421222,"Name":"通城縣"},{"ID":421223,"Name":"崇陽縣"},{"ID":421224,"Name":"通山縣"},{"ID":421281,"Name":"赤壁市"}]},{"ID":421300,"Name":"隨州市","child":[{"ID":421303,"Name":"曾都區(qū)"},{"ID":421321,"Name":"隨縣"},{"ID":421381,"Name":"廣水市"}]},{"ID":422800,"Name":"恩施土家族苗族自治州","child":[{"ID":422801,"Name":"恩施市"},{"ID":422802,"Name":"利川市"},{"ID":422822,"Name":"建始縣"},{"ID":422823,"Name":"巴東縣"},{"ID":422825,"Name":"宣恩縣"},{"ID":422826,"Name":"咸豐縣"},{"ID":422827,"Name":"來鳳縣"},{"ID":422828,"Name":"鶴峰縣"}]},{"ID":429000,"Name":"直轄縣級","child":[{"ID":429004,"Name":"仙桃市"},{"ID":429005,"Name":"潛江市"},{"ID":429006,"Name":"天門市"},{"ID":429021,"Name":"神農(nóng)架林區(qū)"}]}]}
]
二、對Json基本操作
?先給一段json字符串的代碼
DECLARE @json NVARCHAR(MAX);SET @json = N'[{"ID":420000,"Name":"湖北省","child":[{"ID":420100,"Name":"武漢市"},{"ID":420200,"Name":"黃石市"},{"ID":420300,"Name":"十堰市"},{"ID":420500,"Name":"宜昌市"},{"ID":420600,"Name":"襄陽市"},{"ID":420700,"Name":"鄂州市"},{"ID":420800,"Name":"荊門市"},{"ID":420900,"Name":"孝感市"},{"ID":421000,"Name":"荊州市"},{"ID":421100,"Name":"黃岡市"},{"ID":421200,"Name":"咸寧市"},{"ID":421300,"Name":"隨州市"},{"ID":422800,"Name":"恩施土家族苗族自治州"},{"ID":429000,"Name":"直轄縣級"}]}
]';
declare @t table (
temp nvarchar(max)
)
insert into @t
select @json
1.判斷給的字符串是否是Json格式
代碼如下(示例):
select isjson(temp) from @t
運行結(jié)果?
?
?返回1表示為json格式 返回0則不是
2.從 JSON 字符串中提取標(biāo)量值
獲取湖北省的ID?
代碼如下(示例):
select JSON_VALUE(temp,'$[0].ID') from @t
運行結(jié)果
3.?從 JSON 字符串中提取對象或數(shù)組
獲取黃石的節(jié)點?
代碼如下(示例):
select JSON_QUERY(temp ,'$[0].child[1]' ) from @t
運行結(jié)果
4.?更新 JSON 字符串中屬性的值,并返回已更新的 JSON 字符串
把湖北省的ID420000 改成 420001
代碼如下(示例):
select JSON_MODIFY(temp,'$[0].ID','420001') from @t
運行結(jié)果
三、Json轉(zhuǎn)成表
?1.使用OPENJSON??WITH?
代碼如下(示例):
DECLARE @json NVARCHAR(MAX);SET @json = N'[{"ID":420000,"Name":"湖北省"}
]';SELECT *
FROM OPENJSON(@json)
WITH (ID INT 'strict $.ID',Name NVARCHAR(50) '$.Name')
運行結(jié)果
?2.多層嵌套的Json如何轉(zhuǎn)成表呢?
代碼如下(示例):
DECLARE @json NVARCHAR(MAX);SET @json = N'{"ID":420000,"Name":"湖北省","child":[{"ID":420100,"Name":"武漢市"},{"ID":420200,"Name":"黃石市"},{"ID":420300,"Name":"十堰市"},{"ID":420500,"Name":"宜昌市"},{"ID":420600,"Name":"襄陽市"},{"ID":420700,"Name":"鄂州市"},{"ID":420800,"Name":"荊門市"},{"ID":420900,"Name":"孝感市"},{"ID":421000,"Name":"荊州市"},{"ID":421100,"Name":"黃岡市"},{"ID":421200,"Name":"咸寧市"},{"ID":421300,"Name":"隨州市"},{"ID":422800,"Name":"恩施土家族苗族自治州"},{"ID":429000,"Name":"直轄縣級"}]}
';SELECT ID,Name,ParentId,ParentName
FROM OPENJSON(@json)
WITH (ParentId INT 'strict $.ID',ParentName NVARCHAR(50) '$.Name',Names NVARCHAR(max) '$.child' AS JSON)
OUTER APPLY OPENJSON(Names) WITH (ID int '$.ID' , Name NVARCHAR(50) '$.Name');
運行結(jié)果
OUTER APPLY OPENJSON
?將第一級實體與子數(shù)組聯(lián)接,并返回平展的結(jié)果集。
總結(jié)
表和Json是可以做到相互轉(zhuǎn)化的
表轉(zhuǎn)json 使用for json path,json 轉(zhuǎn)表?使用OPENJSON??WITH?
多層的json轉(zhuǎn)表?OUTER APPLY OPENJSON
注意數(shù)據(jù)庫對json的操作,serversql數(shù)據(jù)庫的版本支持2016及以上
以上我是的總結(jié)內(nèi)容,要想了解更多相關(guān)知識?,查閱官方文檔
在 SQL Server 中使用 JSON 數(shù)據(jù) - SQL Server | Microsoft Learn