MS SQL OPENJSON JSON

前段时间，有写过一个小练习《MS SQL读取JSON数据》https://www.cnblogs.com/insus/p/10911739.html

晚上为一个网友的问题，尝试获取较深层节点的数据。

根据网友的原始数据，改写一个相对较简单的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"

                              }'

Source Code

例子一：获取第一层数据：

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

Source Code

例子二：获取第二层数据：

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

Source Code

例子三：获取第三层数据：

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

Source Code

例子四：获取第四层数据：

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

Source Code

巴特西

MS SQL OPENJSON JSON

最新文章

热门文章