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"
                              }'

例子一：獲取第一層數據：

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