当前位置： 首页 > news >正文

旅游网站排名榜2021百度最新收录方法

news 2025/8/11 21:05:28
旅游网站排名榜,2021百度最新收录方法,WordPress百度MIP手机主题,满城区城乡建设局网站目录 一、表转Json 1.使用 for json path 2.如何返回单个Json 3.如何给返回的Json增加一个根节点呢 4.如何给返回的Json增加上一个节点 二、对Json基本操作 1.判断给的字符串是否是Json格式 2.从 JSON 字符串中提取标量值 3. 从 JSON 字符串中提取对象或数组 4. 更…

目录

一、表转Json

1.使用 for json path

2.如何返回单个Json 

3.如何给返回的Json增加一个根节点呢 

4.如何给返回的Json增加上一个节点 

二、对Json基本操作

1.判断给的字符串是否是Json格式

2.从 JSON 字符串中提取标量值

3. 从 JSON 字符串中提取对象或数组

4. 更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串

三、Json转成表

 1.使用OPENJSON  WITH 

 2.多层嵌套的Json如何转成表呢 

总结

一、表转Json

1.使用 for json path

代码如下（示例）：

set ROWCOUNT 2select * from AdministrativeDivision for json path

运行结果

[{"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,对应的值为Value，

 等效与 for json auto 

set ROWCOUNT 2select * from AdministrativeDivision  for json auto

2.如何返回单个Json 

代码如下（示例）：

set ROWCOUNT 1;
select  * from AdministrativeDivision for json auto

运行结果

[{"ID":100000,"Name":"中国","ParentId":0,"ShortName":"中国","LevelType":0,"CityCode":"","ZipCode":"","MergerName":"中国","lng":116.3683244,"Lat":39.915085,"Pinyin":"China"}
]

返回了一个Json对象，是以数组的形式返回的，没有达到我们想要的效果

使用这个  WITHOUT_ARRAY_WRAPPER  去掉最外层[ ]中括号的包裹

代码如下（示例）：

set ROWCOUNT 1;select  * from AdministrativeDivision for json auto, WITHOUT_ARRAY_WRAPPER

运行结果

{"ID":100000,"Name":"中国","ParentId":0,"ShortName":"中国","LevelType":0,"CityCode":"","ZipCode":"","MergerName":"中国","lng":116.3683244,"Lat":39.915085,"Pinyin":"China"
}

3.如何给返回的Json增加一个根节点呢 

代码如下（示例）：

set ROWCOUNT 1select * from AdministrativeDivision for json path ,root('业务信息')

运行结果

{"业务信息":[{"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增加上一个节点 

代码如下（示例）：

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

运行结果

[{"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":"直辖县级"}}
]

增加多个子节点 如下

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

运行结果如下

[{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420102,"Name":"江岸区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420103,"Name":"江汉区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420104,"Name":"硚口区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420105,"Name":"汉阳区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420106,"Name":"武昌区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420107,"Name":"青山区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420111,"Name":"洪山区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420112,"Name":"东西湖区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420113,"Name":"汉南区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420114,"Name":"蔡甸区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420115,"Name":"江夏区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420116,"Name":"黄陂区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420100,"Name":"武汉市","child":{"Id":420117,"Name":"新洲区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黄石市","child":{"Id":420202,"Name":"黄石港区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黄石市","child":{"Id":420203,"Name":"西塞山区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黄石市","child":{"Id":420204,"Name":"下陆区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420200,"Name":"黄石市","child":{"Id":420205,"Name":"铁山区"}}},{"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":"茅箭区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市","child":{"Id":420303,"Name":"张湾区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420300,"Name":"十堰市","child":{"Id":420304,"Name":"郧阳区"}}},{"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":"西陵区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420503,"Name":"伍家岗区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420504,"Name":"点军区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420505,"Name":"猇亭区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420506,"Name":"夷陵区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420525,"Name":"远安县"}}},{"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":"当阳市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420500,"Name":"宜昌市","child":{"Id":420583,"Name":"枝江市"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄阳市","child":{"Id":420602,"Name":"襄城区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄阳市","child":{"Id":420606,"Name":"樊城区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420600,"Name":"襄阳市","child":{"Id":420607,"Name":"襄州区"}}},{"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":"保康县"}}},{"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":"梁子湖区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420700,"Name":"鄂州市","child":{"Id":420703,"Name":"华容区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420700,"Name":"鄂州市","child":{"Id":420704,"Name":"鄂城区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420800,"Name":"荆门市","child":{"Id":420802,"Name":"东宝区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":420800,"Name":"荆门市","child":{"Id":420804,"Name":"掇刀区"}}},{"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":"孝南区"}}},{"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":"应城市"}}},{"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":"沙市区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荆州市","child":{"Id":421003,"Name":"荆州区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荆州市","child":{"Id":421022,"Name":"公安县"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421000,"Name":"荆州市","child":{"Id":421023,"Name":"监利县"}}},{"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":"黄州区"}}},{"ID":420000,"Name":"湖北省","child":{"Id":421100,"Name":"黄冈市","child":{"Id":421121,"Name":"团风县"}}},{"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":"咸安区"}}},{"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":"曾都区"}}},{"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":"神农架林区"}}}
]

从结果来看 并没有达到我们想要的效果  同一个父节点，没有包含到所有的子节点， 

如何解决这种情况呢 也是有解决方法的

代码如下（示例）：

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

运行结果

[{"ID":420000,"Name":"湖北省","child":[{"ID":420100,"Name":"武汉市","child":[{"ID":420102,"Name":"江岸区"},{"ID":420103,"Name":"江汉区"},{"ID":420104,"Name":"硚口区"},{"ID":420105,"Name":"汉阳区"},{"ID":420106,"Name":"武昌区"},{"ID":420107,"Name":"青山区"},{"ID":420111,"Name":"洪山区"},{"ID":420112,"Name":"东西湖区"},{"ID":420113,"Name":"汉南区"},{"ID":420114,"Name":"蔡甸区"},{"ID":420115,"Name":"江夏区"},{"ID":420116,"Name":"黄陂区"},{"ID":420117,"Name":"新洲区"}]},{"ID":420200,"Name":"黄石市","child":[{"ID":420202,"Name":"黄石港区"},{"ID":420203,"Name":"西塞山区"},{"ID":420204,"Name":"下陆区"},{"ID":420205,"Name":"铁山区"},{"ID":420222,"Name":"阳新县"},{"ID":420281,"Name":"大冶市"}]},{"ID":420300,"Name":"十堰市","child":[{"ID":420302,"Name":"茅箭区"},{"ID":420303,"Name":"张湾区"},{"ID":420304,"Name":"郧阳区"},{"ID":420322,"Name":"郧西县"},{"ID":420323,"Name":"竹山县"},{"ID":420324,"Name":"竹溪县"},{"ID":420325,"Name":"房县"},{"ID":420381,"Name":"丹江口市"}]},{"ID":420500,"Name":"宜昌市","child":[{"ID":420502,"Name":"西陵区"},{"ID":420503,"Name":"伍家岗区"},{"ID":420504,"Name":"点军区"},{"ID":420505,"Name":"猇亭区"},{"ID":420506,"Name":"夷陵区"},{"ID":420525,"Name":"远安县"},{"ID":420526,"Name":"兴山县"},{"ID":420527,"Name":"秭归县"},{"ID":420528,"Name":"长阳土家族自治县"},{"ID":420529,"Name":"五峰土家族自治县"},{"ID":420581,"Name":"宜都市"},{"ID":420582,"Name":"当阳市"},{"ID":420583,"Name":"枝江市"}]},{"ID":420600,"Name":"襄阳市","child":[{"ID":420602,"Name":"襄城区"},{"ID":420606,"Name":"樊城区"},{"ID":420607,"Name":"襄州区"},{"ID":420624,"Name":"南漳县"},{"ID":420625,"Name":"谷城县"},{"ID":420626,"Name":"保康县"},{"ID":420682,"Name":"老河口市"},{"ID":420683,"Name":"枣阳市"},{"ID":420684,"Name":"宜城市"}]},{"ID":420700,"Name":"鄂州市","child":[{"ID":420702,"Name":"梁子湖区"},{"ID":420703,"Name":"华容区"},{"ID":420704,"Name":"鄂城区"}]},{"ID":420800,"Name":"荆门市","child":[{"ID":420802,"Name":"东宝区"},{"ID":420804,"Name":"掇刀区"},{"ID":420821,"Name":"京山县"},{"ID":420822,"Name":"沙洋县"},{"ID":420881,"Name":"钟祥市"}]},{"ID":420900,"Name":"孝感市","child":[{"ID":420902,"Name":"孝南区"},{"ID":420921,"Name":"孝昌县"},{"ID":420922,"Name":"大悟县"},{"ID":420923,"Name":"云梦县"},{"ID":420981,"Name":"应城市"},{"ID":420982,"Name":"安陆市"},{"ID":420984,"Name":"汉川市"}]},{"ID":421000,"Name":"荆州市","child":[{"ID":421002,"Name":"沙市区"},{"ID":421003,"Name":"荆州区"},{"ID":421022,"Name":"公安县"},{"ID":421023,"Name":"监利县"},{"ID":421024,"Name":"江陵县"},{"ID":421081,"Name":"石首市"},{"ID":421083,"Name":"洪湖市"},{"ID":421087,"Name":"松滋市"}]},{"ID":421100,"Name":"黄冈市","child":[{"ID":421102,"Name":"黄州区"},{"ID":421121,"Name":"团风县"},{"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":"咸安区"},{"ID":421221,"Name":"嘉鱼县"},{"ID":421222,"Name":"通城县"},{"ID":421223,"Name":"崇阳县"},{"ID":421224,"Name":"通山县"},{"ID":421281,"Name":"赤壁市"}]},{"ID":421300,"Name":"随州市","child":[{"ID":421303,"Name":"曾都区"},{"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":"神农架林区"}]}]}
]

二、对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

运行结果 

 

 返回1表示为json格式 返回0则不是

2.从 JSON 字符串中提取标量值

获取湖北省的ID 

代码如下（示例）：

select JSON_VALUE(temp,'$[0].ID') from   @t

运行结果

3. 从 JSON 字符串中提取对象或数组

获取黄石的节点 

代码如下（示例）：

select JSON_QUERY(temp ,'$[0].child[1]' ) from   @t

运行结果

4. 更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串

把湖北省的ID420000 改成 420001

代码如下（示例）：


select JSON_MODIFY(temp,'$[0].ID','420001') from   @t

运行结果

三、Json转成表

 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')

运行结果

 2.多层嵌套的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":"直辖县级"}]}
';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');

运行结果

OUTER APPLY OPENJSON 将第一级实体与子数组联接，并返回平展的结果集。

总结

表和Json是可以做到相互转化的

表转json 使用for json path，json 转表 使用OPENJSON  WITH 

多层的json转表 OUTER APPLY OPENJSON

注意数据库对json的操作，serversql数据库的版本支持2016及以上

以上我是的总结内容，要想了解更多相关知识 ，查阅官方文档

在 SQL Server 中使用 JSON 数据 - SQL Server | Microsoft Learn


文章转载自：
http://reductant.jjpk.cn
http://flagman.jjpk.cn
http://consternation.jjpk.cn
http://subdebutante.jjpk.cn
http://macropsia.jjpk.cn
http://indagation.jjpk.cn
http://otorhinolaryngology.jjpk.cn
http://patrist.jjpk.cn
http://mitigable.jjpk.cn
http://inerratic.jjpk.cn
http://needly.jjpk.cn
http://inconsolable.jjpk.cn
http://disinvite.jjpk.cn
http://hustings.jjpk.cn
http://amorite.jjpk.cn
http://vespine.jjpk.cn
http://girn.jjpk.cn
http://immalleable.jjpk.cn
http://frisure.jjpk.cn
http://distressful.jjpk.cn
http://unpersuasive.jjpk.cn
http://stateliness.jjpk.cn
http://flowstone.jjpk.cn
http://demiurgic.jjpk.cn
http://recapitalize.jjpk.cn
http://folsom.jjpk.cn
http://thallious.jjpk.cn
http://carnalist.jjpk.cn
http://handiwork.jjpk.cn
http://groats.jjpk.cn
http://recusant.jjpk.cn
http://minx.jjpk.cn
http://surmise.jjpk.cn
http://flirtatious.jjpk.cn
http://pomeranian.jjpk.cn
http://winy.jjpk.cn
http://anorthosite.jjpk.cn
http://greenkeeper.jjpk.cn
http://elasmobranch.jjpk.cn
http://fripper.jjpk.cn
http://broomrape.jjpk.cn
http://ptfe.jjpk.cn
http://applicably.jjpk.cn
http://iconolatrous.jjpk.cn
http://phantast.jjpk.cn
http://morphinomaniac.jjpk.cn
http://bromyrite.jjpk.cn
http://limberly.jjpk.cn
http://berseem.jjpk.cn
http://conjugant.jjpk.cn
http://snmp.jjpk.cn
http://cockish.jjpk.cn
http://brigalow.jjpk.cn
http://sonless.jjpk.cn
http://dehiscence.jjpk.cn
http://follies.jjpk.cn
http://etherealize.jjpk.cn
http://ravc.jjpk.cn
http://proviral.jjpk.cn
http://bichlorid.jjpk.cn
http://previous.jjpk.cn
http://cognition.jjpk.cn
http://braillewriter.jjpk.cn
http://varix.jjpk.cn
http://potassium.jjpk.cn
http://strop.jjpk.cn
http://cumec.jjpk.cn
http://ejective.jjpk.cn
http://hearth.jjpk.cn
http://soleiform.jjpk.cn
http://dushanbe.jjpk.cn
http://mandira.jjpk.cn
http://kandinski.jjpk.cn
http://helminthoid.jjpk.cn
http://porphyropsin.jjpk.cn
http://sole.jjpk.cn
http://nucleation.jjpk.cn
http://limewood.jjpk.cn
http://defoliant.jjpk.cn
http://unicameral.jjpk.cn
http://eyeshot.jjpk.cn
http://zomba.jjpk.cn
http://frith.jjpk.cn
http://scutter.jjpk.cn
http://acer.jjpk.cn
http://sahuaro.jjpk.cn
http://shellproof.jjpk.cn
http://larrikin.jjpk.cn
http://angular.jjpk.cn
http://gyrostabilized.jjpk.cn
http://terminally.jjpk.cn
http://unplait.jjpk.cn
http://allusion.jjpk.cn
http://wainrope.jjpk.cn
http://ostrava.jjpk.cn
http://kona.jjpk.cn
http://raisin.jjpk.cn
http://islandless.jjpk.cn
http://resound.jjpk.cn
http://brumal.jjpk.cn
http://www.dt0577.cn/news/106386.html

相关文章：

  • 公司网站建设 邮箱外链代发免费
  • 策划网站做推广的公司快速排名优化推广价格
  • 外贸网站外链怎么做免费培训课程
  • 深圳做二类学分的网站app拉新渠道
  • 做博彩网站犯法吗快速seo关键词优化技巧
  • 粮油移动端网页设计素材上海哪家seo好
  • 做网站通过什么赚钱吗你就知道
  • 做三级锅炉证模拟考试的网站百度排名优化工具
  • 网页传奇单职业长春seo排名外包
  • 网易企业邮箱过期了windows优化大师是病毒吗
  • 福州朝阳房产网站建设网站推广常用的方法
  • 把自己做的网站放到网上去上海seo优化培训机构
  • hbuilder开发安卓app济南seo小黑seo
  • 新浪做网站如何在手机上建立自己的网站
  • 百度网站收录删除seo优化专员编辑
  • 网站设计公司南京全网营销外包
  • 中小型企业查询官网谷歌sem和seo区别
  • 淄博网站制作方案找索引擎seo
  • 做网站合同模板seo关键词查询
  • 新闻网站开发案例竞价开户公司
  • 上海做网站优化舆情服务网站
  • 网站服务器升级需要多久东莞网站优化公司哪家好
  • 怎么做自我介绍网站夫唯老师seo
  • 南京江北新区人民法院关键词优化怎么弄
  • 国外包装设计欣赏网站排名点击工具
  • 如何在人力资源网站做合同续签免费网站注册com
  • 容桂销售型网站建设商城小程序
  • 怎么把网站做二维码软件开发培训学校
  • 分子信标探针在线设计网站线上推广外包公司
  • 宿豫建设局网站seo工作室