当前位置: 萬仟网 > IT编程>数据库>MSSQL > MS SQL读取JSON数据

MS SQL读取JSON数据

2019年07月11日 14:31  | 萬仟网IT编程  | 我要评论

前段时间,有写过一个小练习《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

 

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com