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 @SqlDECLARE @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 @SqlDECLARE @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 @SqlDECLARE @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 @SqlDECLARE @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 @Sql1DECLARE @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_1DECLARE @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