MS SQL读取JSON数据
程序员文章站
2022-06-24 11:38:07
前段时间,有写过一个小练习《MS SQL读取JSON数据》https://www.cnblogs.com/insus/p/10911739.html 晚上为一个网友的问题,尝试获取较深层节点的数据。 根据网友的原始数据,改写一个相对较简单的json原始数据: DECLARE @json NVARCH ......
前段时间,有写过一个小练习《ms sql读取json数据》
晚上为一个网友的问题,尝试获取较深层节点的数据。
根据网友的原始数据,改写一个相对较简单的json原始数据:
declare @json nvarchar(max) = n'{"isok":"true", "statenumber":"1", "data":{"ok":true, "tables":{ "item":[ {"a":"e1","b":"e2","c":"e3"}, {"a":"f1","b":"f2","c":"f3"} ], "sbu":[ {"id":"001","qty":"2000","amount":"189.03"} ] } }, "records":"450", "pcounts":"150", "nzsum":"真实采购本页总金额:0.00", "sum":"本页总金额:2140.79", "zsum":"条件查询总金额:0.00" }'
例子一:获取第一层数据:
declare @path nvarchar(128) = n'$' declare @sql nvarchar(max) = n'select * from openjson(n''' + @json + ''', n''' + @path + ''')' execute sp_executesql @sql declare @sql1 nvarchar(max) = n'select * from openjson(n''' + @json + ''', n''' + @path + ''') with ( [isok] bit ''$.isok'', [statenumber] int ''$.statenumber'', [data] nvarchar(max) as json, [records] int ''$.records'', [pcounts] int ''$.pcounts'', [nzsum] nvarchar(100) ''$.nzsum'', [sum] nvarchar(100) ''$.sum'', [zsum] nvarchar(100) ''$.zsum'' ) ' execute sp_executesql @sql1
例子二:获取第二层数据:
declare @path nvarchar(128) = n'$."data"' declare @sql nvarchar(max) = n'select * from openjson(n''' + @json + ''', n''' + @path + ''')' execute sp_executesql @sql declare @sql1 nvarchar(max) = n'select * from openjson(n''' + @json + ''', n''' + @path + ''') with ( [ok] bit ''$.ok'', [tables] nvarchar(max) as json ) ' execute sp_executesql @sql1
例子三:获取第三层数据:
declare @path nvarchar(128) = n'$."data".tables' declare @sql nvarchar(max) = n'select * from openjson(n''' + @json + ''', n''' + @path + ''')' execute sp_executesql @sql declare @sql1 nvarchar(max) = n'select * from openjson(n''' + @json + ''', n''' + @path + ''') with ( [item] nvarchar(max) as json, [sbu] nvarchar(max) as json ) ' execute sp_executesql @sql1
例子四:获取第四层数据:
declare @path nvarchar(128) = n'$."data"."tables"."item"' declare @sql nvarchar(max) = n'select * from openjson(n''' + @json + ''', n''' + @path + ''')' execute sp_executesql @sql declare @sql1 nvarchar(max) = n'select * from openjson(n''' + @json + ''', n''' + @path + ''') with ( [a] nvarchar(10) ''$.a'', [b] nvarchar(10) ''$.b'', [c] nvarchar(10) ''$.c'' ) ' execute sp_executesql @sql1 declare @path_1 nvarchar(128) = n'$."data"."tables"."sbu"' declare @sql_1 nvarchar(max) = n'select * from openjson(n''' + @json + ''', n''' + @path_1 + ''')' execute sp_executesql @sql_1 declare @sql_2 nvarchar(max) = n'select * from openjson(n''' + @json + ''', n''' + @path_1 + ''') with ( [id] nvarchar(5) ''$.id'', [qty] decimal(18,2) ''$.qty'', [amount] decimal(18,2) ''$.amount'' ) ' execute sp_executesql @sql_2