中文亚洲精品无码_熟女乱子伦免费_人人超碰人人爱国产_亚洲熟妇女综合网

當(dāng)前位置： 首頁 > news >正文

網(wǎng)頁設(shè)計模板套用步驟seo網(wǎng)絡(luò)搜索引擎優(yōu)化

news 2025/7/11 6:08:23
網(wǎng)頁設(shè)計模板套用步驟,seo網(wǎng)絡(luò)搜索引擎優(yōu)化,網(wǎng)站制作排版注意事項,wordpress 注冊 填寫密碼錯誤目錄 一、表轉(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. 更…

目錄

一、表轉(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

http://www.risenshineclean.com/news/55260.html

相關(guān)文章：

  • 網(wǎng)站常用模塊百度在線問答
  • 公司建網(wǎng)站做app要多少錢精準(zhǔn)網(wǎng)絡(luò)推廣
  • 專業(yè)電容層析成像代做網(wǎng)站排名檢測
  • 網(wǎng)頁制作與網(wǎng)站開發(fā)模板今日頭條新聞大事件
  • wordpress和bootstrap廣東seo網(wǎng)站優(yōu)化公司
  • 哪里做網(wǎng)站做得好免費網(wǎng)站收錄網(wǎng)站推廣
  • html5營銷網(wǎng)站建設(shè)app推廣注冊接單平臺
  • 中山網(wǎng)站建設(shè)找阿 n 2品牌整合營銷傳播
  • 幼兒園制度網(wǎng)站建設(shè)方案網(wǎng)頁設(shè)計圖片
  • 網(wǎng)站在線客服鏈接如何做一個網(wǎng)站
  • 怎么做刷會員的網(wǎng)站關(guān)鍵詞優(yōu)化快速
  • 企業(yè)營銷網(wǎng)站開發(fā)建設(shè)專家山東濰坊疫情最新消息
  • wordpress圖片無法顯示百度seo新算法
  • 鄭州網(wǎng)站制作推廣站長seo
  • 如何做網(wǎng)站結(jié)構(gòu)優(yōu)化微信推廣廣告在哪里做
  • 全flash網(wǎng)站欣賞百度搜索引擎優(yōu)化指南最新版
  • 做網(wǎng)站需要買什么電商平臺開發(fā)需要多少錢
  • 企業(yè)網(wǎng)站seo搜索引擎關(guān)鍵詞優(yōu)化技巧
  • 網(wǎng)站改名 備案佛山網(wǎng)站建設(shè)方案服務(wù)
  • 定制級高端網(wǎng)站建設(shè)seo搜索引擎實訓(xùn)心得體會
  • b站24小時免費直播下載迅雷bt磁力鏈 最好用的搜索引擎
  • 企業(yè)網(wǎng)站優(yōu)化方法包括sem推廣計劃
  • 呂梁做網(wǎng)站互聯(lián)網(wǎng)公司排名100強
  • 沈陽品牌網(wǎng)站建設(shè)推廣方案設(shè)計
  • 常州seo網(wǎng)絡(luò)推廣seo優(yōu)化大公司排名
  • 網(wǎng)站設(shè)計怎么收費免費企業(yè)網(wǎng)站模板源碼
  • 北京市保障房建設(shè)官方網(wǎng)站能讓網(wǎng)絡(luò)非常流暢的軟件
  • 人們做網(wǎng)站怎么賺錢seo系統(tǒng)優(yōu)化
  • 華為外包做的網(wǎng)站數(shù)據(jù)分析師一般一個月多少錢
  • 小語種網(wǎng)站建設(shè)關(guān)鍵詞分類