Parse nested Json data into a SQL Server table

Question

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'?

Which DBMS? BigQuery, PostgreSQL, etc? – MatBailie Sep 02 '21 at 15:04 — MatBailie, Sep 02 '21 at 15:04
I am trying to parse in MSSQL – burcubelen Sep 02 '21 at 15:05 — burcubelen, Sep 02 '21 at 15:05

score 0 · Accepted Answer · answered Sep 02 '21 at 15:13

0

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

answered Sep 02 '21 at 15:13

David Browne - Microsoft

80,331
6
39
67

I have already a table in MSSQL. It has two columns 'Id' and 'JsonObject' which have JSON data. If I want to solve as you did, I can't declare a column of a table like @msg. It gives me an error : ""Must declare the scalar variable"" – burcubelen Sep 02 '21 at 17:21
That's just an example. Replace `@msg` with your existing table name. – David Browne - Microsoft Sep 02 '21 at 17:22
Solved it. Thank you so much :) – burcubelen Sep 03 '21 at 10:46

Parse nested Json data into a SQL Server table

1 Answers1