I have a column JsonObject in the t table. I need to parse this JsonObject column which has JSON values in the array.
My first solution is:
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
This is just shown the first message of the provider but a provider has more than one messages. If I query as follow, it didn't give any of 'Messages' data
JSON_VALUE(JsonObject, '$.Messages.Sender') AS Sender,
JSON_VALUE(JsonObject, '$.Messages.Text') AS Text
Messages data is like:
{"Messages":[{"Sender":"John","Text":"xxxx"},{"Sender":"Peter","Text":"yyyy"}]}
How can I show every 'Messages' data in different rows with them 'Id' and 'Provider'?
You need to use CROSS APPLY and OPENJSON. Like this:
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
outputs
id Sender Text
----------- ---------- -------------------
1 John xxxx
1 Peter yyyy
2 Fred xxxx
2 Akex yyyy