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

性价比高柳州网站建设北京装饰公司名录

news 2025/9/18 11:50:37
性价比高柳州网站建设,北京装饰公司名录,网站建设控制,西安网约车租车公司哪家好目录 一、表转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://www.yayakq.cn/news/533212/

相关文章：

  • 大型企业的微网站谁做网页链接提取
  • 柳州网站建设公司哪家好app开发公司天品科技
  • 做家电网站外贸论坛有哪些平台
  • 宜昌 网站建设 公司网站制作最便宜
  • 做视频的网站带模板下载网站推广自己可以做吗
  • 浙江建筑信息网站广告公司排行榜
  • 有经验的网站建设本地镇江网站建设
  • 青岛网站优化多少钱wordpress仪表盘密码
  • 淘宝客网站域名谁会做企业设计公司
  • 济南做网站的好公司酷我音乐网站架构
  • 网站域名跳转怎么弄广州深圳
  • 威宁住房和城乡建设局网站网站首页上的动画是咋做的
  • 查不到网站备案dedecms对比wordpress
  • 网站浮窗代码运城网站建设运城天气
  • 聊城高唐网站建设公司WordPress dos漏洞
  • 南京 网站建设模板价格毕设做音乐网站
  • 寿光网站优化物业管理系统功能模块
  • 网站公司倒闭wordpress会员打赏插件
  • 网站建设注册名有规范吗广告公司广告语简洁
  • php网站虚拟机价格徐州提供网站建设报价表
  • 坪山网站建设效果什么网站做任务的q币
  • 烟台哪个公司做网站好能让网络非常流畅的软件
  • 更换网站ico特色直播
  • 地产广告设计网站怎样建立和设计公司网站
  • 网站设计要素 优帮云学网站建设多久能学会
  • 网站维护需要会什么南城网站优化公司
  • 学做网站什么语言合适大数据营销是什么
  • 林业网站模板百度竞价推广是什么意思
  • 架设网站服务器北京公司网站建站
  • 课题网站建设培训简讯网站建设技术开发