I have a table like :
| ID | Args |
|---|---|
| 1 | {"requester":"Alexandre", "recipients":[{"first":"John", "last":"Smith"}, {"first":"Elisa", "last":"Martin"},{….},{"first":"Michael", "last":"Dubois"}] } |
| 2 | {"requester":"Martin", "recipients":[{"first":"Jean", "last":"Dupont"}, {"first":"Elisa", "last":"Martin"}] } |
| … | … |
| n | {"requester":"Jean", "recipients":[{"first":"Jacques", "last":"Dupont"}, {"first":"Elisa", "last":"Gardner"},{….},{"first":"Michael", "last":"Dupont"}] } |
What I would like to have:
| ID | Requester | Recipient first |
|---|---|---|
| 1 | Alexandre | John |
| 1 | Alexandre | Elisa |
| 1 | Alexandre | … |
| 1 | Alexandre | Michael |
| 2 | Martin | Jean |
| … | … | … |
PS : The number of recipients varies.
My tests :
select id, JSON_VALUE(args, '$.requester') requester,JSON_VALUE(args, '$.recipients[0].first') recipient
from table
But the idea would be to iterate here on the number of recipients (here only 0)
I also achieve to do this :
DECLARE @json nvarchar(max)
select @json = args from table
print @json
SELECT first
FROM OPENJSON( @json, '$.recipients' )
WITH ([first] NVARCHAR(25) '$.first');
And it allows to have the firstname list of the first line only.
Does anyone know how to get the desired result?
Thanks
>Solution :
Two levels of OPENJSON should do it:
SELECT t.id, j1.requester, j2.first_name
FROM t
CROSS APPLY OPENJSON(t.args) WITH (
requester NVARCHAR(100) '$.requester',
recipients NVARCHAR(MAX) AS JSON
) AS j1
CROSS APPLY OPENJSON(j1.recipients) WITH (
first_name NVARCHAR(100) '$.first'
) AS j2