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

表和Json的相互操作

news 2025/7/8 18:27:43

一、表转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

表和Json的相互操作

目录一、表转Json 1.使用 for json path 2.如何返回单个Json 3.如何给返回的Json增加一个根节点呢 4.如何给返回的Json增加上一个节点二、对Json基本操作 1.判断给的字符串是否是Json格式 2.从 JSON 字符串中提取标量值 3. 从 JSON 字符串中提取对象或数组 4. 更…...

编程日记 2023/8/28 15:12:34

每日后端面试5题第八天

1.UDP和TCP协议的区别 1.UDP无连接，速度快，安全性低，适合高速传输、实时广播通信等。 2.TCP面向连接，速度慢，安全性高，适合传输质量要求高、大文件等的传输，比如邮件发送等。 （还…...

编程日记 2023/8/28 15:11:33

mysql数据库和数据表

常用的数据类型: int ： 整型用于定义整数类型的数据float ： 单精度浮点4字节32位准确表示到小数点后六位.double ：双精度浮点8字节64位char ：固定长度的字符类型用于定义字符类型数据varchar ：可变长度的字符类…...

编程日记 2023/8/28 15:10:32

MySQL执行更新的流程

一、加载缓存数据引擎要执行更新语句的时候 ，比如对“id10”这一行数据，他其实会先将“id10”这一行数据看看是否在缓冲池里，如果不在的话，那么会直接从磁盘里加载到缓冲池里来，而且接着还会对这行记录加独占锁。二…...

编程日记 2023/8/28 15:09:31

要获取 PHP 中当前时间的前一天、本周、本月、本季度和本年，可以使用 PHP 的内置日期和时间函数。

要获取 PHP 中当前时间的前一天、本周、本月、本季度和本年，可以使用 PHP 的内置日期和时间函数。下面是一些示例代码来帮助你实现这些功能： php // 获取当前时间的前一天 $yesterday date(Y-m-d, strtotime(-1 day));// 获取本周的开始日期和结束日期…...

编程日记 2023/8/28 15:08:30

java八股文面试[java基础]——如何实现不可变的类

知识来源： 【23版面试突击】如何实现不可变的类？_哔哩哔哩_bilibili 【2023年面试】怎样声明一个类不会被继承，什么场景下会用_哔哩哔哩_bilibili...

编程日记 2023/8/28 15:07:28

juc基础（四）

目录一、ThreadPool 线程池 1、参数说明 2、拒绝策略 3、线程池种类 （1）newCachedThreadPool(常用) （2）newFixedThreadPool(常用) （3）newSingleThreadExecutor(常用) （4）ne…...

编程日记 2023/8/28 15:06:26

C++智能指针weak_ptr的作用

当使用std::shared_ptr时，循环引用可能会导致资源泄漏的问题。下面是一个简单的示例，展示了循环引用导致资源泄漏的情况： #include <iostream> #include <memory>class A; class B;class A { public:std::shared_ptr<B> b…...

编程日记 2023/8/28 15:05:25

lintcode 344 · 歌曲时间【背包问题，动态规划】

题目链接，描述 https://www.lintcode.com/problem/344/ 给定长度为N的正整数数组song代表N首歌的时间请你任选其中若干首播放，在满足开始播放最后一首歌的时间小于M的情况下求播放歌曲的最长时间每首歌只能被播放一次你可以任意指定播放顺序1 \leq …...

编程日记 2023/8/28 15:04:24

Qt应用开发(基础篇)——对话框窗口 QDialog

一、前言 QDialog类继承于QWidget，是Qt基于对话框窗口(消息窗口QMessageBox、颜色选择窗口QColorDialog、文件选择窗口QFileDialog等)的基类。 QDialog窗口是顶级的窗口，一般情况下，用来当做用户短期任务(确认、输入、选择)或者和用户交流(提…...

编程日记 2023/8/28 15:03:22

Linux系统：CentOS 7 CA证书服务器部署

目录一、理论 1.CA认证中心 2.CA证书服务器部署二、实验 1. CA证书服务器部署三、总结一、理论 1.CA认证中心 （1）概念 CA ：CertificateAuthority的缩写，通常翻译成认证权威或者认证中心，主要用途是为用户…...

编程日记 2023/8/28 15:02:21

C++图形界面编程-MFC

C控制台程序是命令行黑框，如果要写一个图形界面，VS也提供了图形界面编程MFC。建项目的时候选如下选项： 类似于QT。问：那么MFC项目的运行入口main()或WinMain()在哪里呢？ 答：其实，在MFC应用程…...

编程日记 2023/8/28 15:01:19

知识扩展贴圆越大,其圆接触的无知面就越多

CSDN 排行榜 https://blog.csdn.net/rank/list/total?spm1001.2014.3001.5476 顺其自然~_-CSDN博客...

编程日记 2023/8/28 15:00:17

怎么把pdf转换成jpg格式？

怎么把pdf转换成jpg格式？在我们日常的办公过程中，PDF文件是一个经常被使用来传输文件的格式。它能够确保我们的文件内容不会混乱，并以更加完美的方式呈现出来。然而，PDF文件也存在一些缺陷。例如，它无法直接编辑&#…...

编程日记 2023/8/28 14:59:16

Android SDK 上手指南||第六章用户交互

第六章用户交互在这篇教程中，我们将对之前所添加的Button元素进行设置以实现对用户点击的检测与响应。为了达成这一目标，我们需要在应用程序的主 Activity类中略微涉及Java编程内容。如果大家在Java开发方面的经验不太丰富也没必要担心，只…...

编程日记 2023/8/28 14:58:15

Vue3+Pinia+Koa+Three.js 全栈电商项目总结复盘

前言前几天一个朋友去义乌旅游，带回来很多小商品，就是一整个物美价廉，但是为什么线下购物和网购有的时候差别这么大（网购经常要退换货啊😭😭😭），为此我萌生了一个想法&…...

编程日记 2023/8/28 14:57:14

【大模型AIGC系列课程 2-3】动手为ChatGPT打造第二大脑

文本向量的应用 one-hot 文本向量 !pip install jiebaimport jieba # 中文分词包text = 6月27日，世界经济论坛发布了《2023年10大新兴技术》报告。重点介绍了在未来3—5年对全球经济、工作、生活、医疗等产生积极影响的创新技术。其中，生成式AI首次入选并排名第2位。世界经…...

编程日记 2023/8/28 14:56:13

【ARM AMBA AXI 入门 10 - AXI 总线 DATA信号与 STRB 信号之间的关系】

文章目录 AXI STRB 信号 AXI STRB 信号 AXI总线是ARM公司设计的高性能处理器接口，其中STRB和DATA信号在AXI协议中有特殊的含义和关系。 DATA信号：在AXI中，DATA信号用于在读写操作中传输实际的数据。数据的大小可以根据AXI接口的位宽来变化&…...

编程日记 2023/8/28 14:55:11

软引用的使用场景-链路日志

我司自研的链路系统中的agent层记录日志时，使用的是异步打印日志的机制。异步打印会使用队列，现将待打印的日志对象，记录在队列中。但这块的日志，为了不影响业务，例如不能因为链路记录的日志过多，导致业务…...

编程日记 2023/8/28 14:54:10

【java】【项目实战】[外卖七]手机短信开发

目录一、发送短信 1 短信服务介绍 2 阿里云短信服务（个人现在不太好申请了） 2.1 介绍 2.2 注册账号 2.3 设置短信签名 2.4 设置短信模版 2.5 设置AccessKey 3 代码开发 3.1 导包 3.2 短信发送工具类SMSUtils 二、手机验证码登录 1 需求分析 …...

编程日记 2023/8/28 14:53:09

unix/linux，sudo，其发展历程详细时间线、由来、历史背景

sudo 的诞生和演化，本身就是一部 Unix/Linux 系统管理哲学变迁的微缩史。来，让我们拨开时间的迷雾，一同探寻 sudo 那波澜壮阔（也颇为实用主义）的发展历程。历史背景：su的时代与困境 ( 20 世纪 70 年代 - 80 年代初) 在 sudo 出现之前，Unix 系统管理员和需要特权操作的…...

编程新知 2025/7/7 7:03:24

SpringCloudGateway 自定义局部过滤器

场景： 将所有请求转化为同一路径请求（方便穿网配置）在请求头内标识原来路径，然后在将请求分发给不同服务 AllToOneGatewayFilterFactory import lombok.Getter; import lombok.Setter; import lombok.extern.slf4j.Slf4j; impor…...

编程新知 2025/7/5 22:43:06

Redis数据倾斜问题解决

Redis 数据倾斜问题解析与解决方案什么是 Redis 数据倾斜 Redis 数据倾斜指的是在 Redis 集群中，部分节点存储的数据量或访问量远高于其他节点，导致这些节点负载过高，影响整体性能。数据倾斜的主要表现部分节点内存使用率远高于其他节…...

编程新知 2025/7/4 15:57:09

精益数据分析（97/126）：邮件营销与用户参与度的关键指标优化指南

精益数据分析（97/126）：邮件营销与用户参与度的关键指标优化指南在数字化营销时代，邮件列表效度、用户参与度和网站性能等指标往往决定着创业公司的增长成败。今天，我们将深入解析邮件打开率、网站可用性、页面参与时…...

编程新知 2025/6/25 2:48:04

Java线上CPU飙高问题排查全指南

一、引言在Java应用的线上运行环境中，CPU飙高是一个常见且棘手的性能问题。当系统出现CPU飙高时，通常会导致应用响应缓慢，甚至服务不可用，严重影响用户体验和业务运行。因此，掌握一套科学有效的CPU飙高问题排查方法&…...

编程新知 2025/7/8 9:03:00

LLMs 系列实操科普（1）

写在前面： 本期内容我们继续 Andrej Karpathy 的《How I use LLMs》讲座内容，原视频时长 ~130 分钟，以实操演示主流的一些 LLMs 的使用，由于涉及到实操，实际上并不适合以文字整理，但还是决定尽量整理一份笔…...

编程新知 2025/7/7 4:04:47

Python常用模块：time、os、shutil与flask初探

一、Flask初探 & PyCharm终端配置目的：快速搭建小型Web服务器以提供数据。工具：第三方Web框架 Flask (需 pip install flask 安装)。安装 Flask：建议：使用 PyCharm 内置的 Terminal (模拟命令行) 进行安装，避免频繁切换。 PyCharm Terminal 配置建议：打开 Py…...

编程新知 2025/6/16 14:43:58