将嵌套Json数据解析到SQL Server表中

Question

我在t表中有一个列JsonObject。我需要解析这个JsonObject列在数组中有JSON值。

我的第一个解决方案是:

SELECT 
JSON_VALUE(JsonObject, '$.Id') AS Id,
JSON_VALUE(JsonObject, '$.Provider') AS Provider,
JSON_VALUE(JsonObject, '$.Messages[0].Sender') AS Sender,
JSON_VALUE(JsonObject, '$.Messages[0].Text') AS Text
FROM
dbo.t    

这只显示了提供者的第一条消息，但是一个提供者有多条消息。如果我按如下方式查询，它没有给出任何'Messages'数据

JSON_VALUE(JsonObject, '$.Messages.Sender') AS Sender,
JSON_VALUE(JsonObject, '$.Messages.Text') AS Text

消息数据如下:

{"Messages":[{"Sender":"John","Text":"xxxx"},{"Sender":"Peter","Text":"yyyy"}]}

我怎样才能显示每个'消息'数据在不同的行与他们'Id'和'提供者'?

Answer 1

您需要使用CROSS APPLY和OPENJSON。这样的:

declare @msg table(id int identity, msg nvarchar(max))
insert into @msg(msg) values ('{"Messages":[{"Sender":"John","Text":"xxxx"},{"Sender":"Peter","Text":"yyyy"}]}')
insert into @msg(msg) values ('{"Messages":[{"Sender":"Fred","Text":"xxxx"},{"Sender":"Akex","Text":"yyyy"}]}')

select m.id, parsedJson.*
from @msg m
cross apply openjson(m.msg,'$.Messages') 
with 
(
Sender nvarchar(200),
Text nvarchar(max)
) as parsedJson

输出

id          Sender     Text
----------- ---------- -------------------
1           John       xxxx
1           Peter      yyyy
2           Fred       xxxx
2           Akex       yyyy