表和Json的相互操作
目录
一、表转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 auto2.如何返回单个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
相关文章：
表和Json的相互操作
目录 一、表转Json 1.使用 for json path 2.如何返回单个Json 3.如何给返回的Json增加一个根节点呢 4.如何给返回的Json增加上一个节点 二、对Json基本操作 1.判断给的字符串是否是Json格式 2.从 JSON 字符串中提取标量值 3. 从 JSON 字符串中提取对象或数组 4. 更…...
每日后端面试5题 第八天
1.UDP和TCP协议的区别 1.UDP无连接，速度快，安全性低，适合高速传输、实时广播通信等。 2.TCP面向连接，速度慢，安全性高，适合传输质量要求高、大文件等的传输，比如邮件发送等。 （还…...
mysql数据库和数据表
常用的数据类型: int ： 整型 用于定义整数类型的数据float ： 单精度浮点4字节32位 准确表示到小数点后六位.double ：双精度浮点8字节64位char ：固定长度的字符类型 用于定义字符类型数据varchar ：可变长度的字符类…...
MySQL执行更新的流程
一、加载缓存数据 引擎要执行更新语句的时候 ，比如对“id10”这一行数据，他其实会先将“id10”这一行数据看看是否在缓冲池里，如果不在的话，那么会直接从磁盘里加载到缓冲池里来，而且接着还会对这行记录加独占锁。 二…...
要获取 PHP 中当前时间的前一天、本周、本月、本季度和本年，可以使用 PHP 的内置日期和时间函数。
要获取 PHP 中当前时间的前一天、本周、本月、本季度和本年，可以使用 PHP 的内置日期和时间函数。下面是一些示例代码来帮助你实现这些功能： php // 获取当前时间的前一天 $yesterday date(Y-m-d, strtotime(-1 day));// 获取本周的开始日期和结束日期…...
java八股文面试[java基础]——如何实现不可变的类
知识来源： 【23版面试突击】如何实现不可变的类？_哔哩哔哩_bilibili 【2023年面试】怎样声明一个类不会被继承，什么场景下会用_哔哩哔哩_bilibili...
juc基础（四）
目录 一、ThreadPool 线程池 1、参数说明 2、拒绝策略 3、线程池种类 （1）newCachedThreadPool(常用) （2）newFixedThreadPool(常用) （3）newSingleThreadExecutor(常用) （4）ne…...
C++智能指针weak_ptr的作用
当使用std::shared_ptr时，循环引用可能会导致资源泄漏的问题。下面是一个简单的示例，展示了循环引用导致资源泄漏的情况： #include <iostream> #include <memory>class A; class B;class A { public:std::shared_ptr<B> b…...
lintcode 344 · 歌曲时间【背包问题，动态规划】
题目链接，描述 https://www.lintcode.com/problem/344/ 给定长度为N的正整数数组song代表N首歌的时间 请你任选其中若干首播放，在满足开始播放最后一首歌的时间小于M的情况下求播放歌曲的最长时间 每首歌只能被播放一次 你可以任意指定播放顺序1 \leq …...
Qt应用开发(基础篇)——对话框窗口 QDialog
一、前言 QDialog类继承于QWidget，是Qt基于对话框窗口(消息窗口QMessageBox、颜色选择窗口QColorDialog、文件选择窗口QFileDialog等)的基类。 QDialog窗口是顶级的窗口，一般情况下，用来当做用户短期任务(确认、输入、选择)或者和用户交流(提…...
Linux系统：CentOS 7 CA证书服务器部署
目录 一、理论 1.CA认证中心 2.CA证书服务器部署 二、实验 1. CA证书服务器部署 三、总结 一、理论 1.CA认证中心 （1）概念 CA ：CertificateAuthority的缩写，通常翻译成认证权威或者认证中心，主要用途是为用户…...
C++图形界面编程-MFC
C控制台程序是命令行黑框，如果要写一个图形界面，VS也提供了图形界面编程MFC。建项目的时候选如下选项： 类似于QT。 问：那么MFC项目的运行入口main()或WinMain()在哪里呢？ 答：其实，在MFC应用程…...
知识扩展贴 圆越大,其圆接触的无知面就越多
CSDN 排行榜 https://blog.csdn.net/rank/list/total?spm1001.2014.3001.5476 顺其自然~_-CSDN博客...
怎么把pdf转换成jpg格式？
怎么把pdf转换成jpg格式？在我们日常的办公过程中，PDF文件是一个经常被使用来传输文件的格式。它能够确保我们的文件内容不会混乱，并以更加完美的方式呈现出来。然而，PDF文件也存在一些缺陷。例如，它无法直接编辑&#…...
Android SDK 上手指南||第六章 用户交互
第六章 用户交互 在这篇教程中，我们将对之前所添加的Button元素进行设置以实现对用户点击的检测与响应。为了达成这一目标，我们需要在应用程序的主 Activity类中略微涉及Java编程内容。如果大家在Java开发方面的经验不太丰富也没必要担心，只…...
Vue3+Pinia+Koa+Three.js 全栈电商项目总结复盘
前言 前几天一个朋友去义乌旅游，带回来很多小商品，就是一整个物美价廉，但是为什么线下购物和网购有的时候差别这么大（网购经常要退换货啊😭😭😭），为此我萌生了一个想法&…...
【大模型AIGC系列课程 2-3】动手为ChatGPT打造第二大脑
文本向量的应用 one-hot 文本向量 !pip install jiebaimport jieba # 中文分词包text = 6月27日，世界经济论坛发布了《2023年10大新兴技术》报告。重点介绍了在未来3—5年对全球经济、工作、生活、医疗等产生积极影响的创新技术。其中，生成式AI首次入选并排名第2位。世界经…...
【ARM AMBA AXI 入门 10 - AXI 总线 DATA信号与 STRB 信号之间的关系 】
文章目录 AXI STRB 信号 AXI STRB 信号 AXI总线是ARM公司设计的高性能处理器接口，其中STRB和DATA信号在AXI协议中有特殊的含义和关系。 DATA信号：在AXI中，DATA信号用于在读写操作中传输实际的数据。数据的大小可以根据AXI接口的位宽来变化&…...
软引用的使用场景-链路日志
我司自研的链路系统中的agent层记录日志时，使用的是异步打印日志的机制。异步打印会使用队列，现将待打印的日志对象，记录在队列中。 但这块的日志，为了不影响业务，例如不能因为链路记录的日志过多，导致业务…...
【java】【项目实战】[外卖七]手机短信开发
目录 一、发送短信 1 短信服务介绍 2 阿里云短信服务（个人现在不太好申请了） 2.1 介绍 2.2 注册账号 2.3 设置短信签名 2.4 设置短信模版 2.5 设置AccessKey 3 代码开发 3.1 导包 3.2 短信发送工具类SMSUtils 二、手机验证码登录 1 需求分析 …...
Web 开发 Django 模板
上次为大家介绍了 Django 的模型和自带的管理工具，有了这个工具就可以全自动地根据模型创建后台管理界面，以供网站管理者更方便的管理网站数据。有了网站数据，那怎么样更方便又好看的展示给用户看呢？目前流行的 Web 框架基本都采用…...
动态可编辑表单项
遇到的问题：业务需要用户输入对应的username以发送私信给指定对象 方案1-input 输入就完事了 缺陷：要输入，麻烦 <form><label for"recipient-name">发给：</label><input type"text"…...
【Docker入门第一篇】
Docker简介 Docker 是一个开源的应用容器引擎，基于 Go 语言 并遵从 Apache2.0 协议开源。 Docker 可以让开发者打包他们的应用以及依赖包到一个轻量级、可移植的容器中，然后发布到任何流行的 Linux 机器上，也可以实现虚拟化。 容器是完全使…...
数据集收集列表（opencv,机器学习，深度学习）持续更新
opencv 车牌识别数据集 opencv 手写数字识别数据集 机器学习 印第安糖尿病 Pima Indians数据集 ，下载地址 Boston波士顿房价数据集 ，下载...
springboot整合rabbitmq发布确认高级
在生产环境中由于一些不明原因，导致 rabbitmq 重启，在 RabbitMQ 重启期间生产者消息投递失败，导致消息丢失，需要手动处理和恢复。于是，我们如何才能进行 RabbitMQ 的消息可靠投递。 发布确认 发布确认方案 架构 配置…...
【linux命令讲解大全】010. mapfile命令和tempfile命令的用法及示例
文章目录 mapfile概要主要用途选项参数返回值例子 tempfile补充说明tempfile 命令$$ 变量 从零学 python mapfile 从标准输入读取行并赋值到数组。 概要 mapfile [-d delim] [-n count] [-O origin] [-s count] [-t] [-u fd] [-C callback] [-c quantum] [array] 主要用途 …...
在 Python 中构建卷积神经网络； 从 0 到 9 的手绘数字的灰度图像预测数字
一、说明 为了预测从0到9的数字，我选择了一个基于著名的Kaggle的MNIST数据集的数据集。数据集包含从 <0> 到 <9> 的手绘图数字的灰度图像。在本文中，我将根据像素数据（即数值数据）和卷积神经网络预测数字。 二、 卷积…...
前端分页处理
页面中实现的分页效果，要么后端提供接口，每次点击下一页就调用接口，若不提供接口，分页得前端自己去截取。 方法一：slice方法 slice(参数1，参数2)方法是返回一个新的数组对象，左开右闭 参数1&…...
【C语言】位操作符的一些题目与技巧
初学者在学完位操作符之后，总是不能很好的掌握，因此这篇文章旨在巩固对位操作符的理解与使用。 有的题目可能会比较难以接受，但是看完一定会有收获 目录 位操作符：一些题目：不创建临时变量交换整数整数转换二进制中1的…...
爬虫逆向实战（二十二）--某恩数据电影票房
一、数据接口分析 主页地址：某恩数据 1、抓包 通过抓包可以发现数据接口是API/GetData.ashx 2、判断是否有加密参数 请求参数是否加密？ 无请求头是否加密？ 无响应是否加密？ 通过查看“响应”模块可以发现，响应是…...
网站上怎么做产品介绍/排名函数
1. 涉及平台 平台管理、商家端（PC端、手机端）、买家平台（H5/公众号、小程序、APP端（IOS/Android）、微服务平台（业务服务） 2. 核心架构 Spring Cloud、Spring Boot、Mybatis、Redis 3. 前端框架…...
万网域名中文网站查询/网站seo招聘
初步自学了vim，把一些基础的东西记录下来，方便以后查阅 1、启动 vim example.c vim -R example.c 只读模式 2、在命令模式下 wq 或 x 保存退出 q！ 强行退出不保存 w 保存命令 3、普通模式 –> 编辑模式 a , c, i, o, s 4、狂按es…...
河北省建设厅注册中心网站首页/友链提交入口
首先参考轮子库Linux的实现，Linux实现的主干逻辑如下： Xtensa架构有其特殊性，最核心的一点是所谓的window ABI，通过window ABI减少了函数调用过程中压栈出栈的操作，获取了性能上的提升。不知道是不是由于 Window ABI引…...
龙岗做棋牌网站建设/谷歌推广开户
这两天体检，抽了下血给我这营养不良抽的浑身无力...刚开始可能String用到的比较多，但是String可能不适用于很多情况，于是就写一下StringBuilder和StringBuffer。Java平台提供了两种字符串类型：String和StringBuffer、StringBuilde…...
广东网页空间网站平台/什么平台可以免费打广告
如果想跟我一起讨论的话，就快加入我的知识星球吧。星球里有一千多位同样爱好安全技术的小伙伴一起交流！ 常用工具： Nmap使用详解Sqlmap使用详解Metasploit Framework(MSF)的使用MSF中kiwi模块的使用MSF中mimikatz模块的使用Msfvenonm生成后门木马...
行业网站建设哪家好/百度竞价排名费用
这两年，线上办公逐渐常态化，相信大家对ftp这个概念也比较熟悉了。ftp，即文件传输协议，线上办公就是用ftp软件进行文件传输的。那ftp传输文件大小有限制吗,ftp文件传输工具有哪些我们一起来看看。 一、ftp传输文件大小有限制吗 f…...