Extract items from JSON containing a nested array

Question

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

Answer 1

My brute force approach would be to use CROSS APPLY to open up different parts of the JSON string...

SELECT
  *
FROM
  example
CROSS APPLY
(
  SELECT *
    FROM OPENJSON(example.json)
         WITH (
           [requester] NVARCHAR(25) '$.requester'
         )
)
  json_requester
CROSS APPLY
(
  SELECT *
    FROM OPENJSON(example.json, '$.recipients')
         WITH (
           [recipient_first] NVARCHAR(25) '$.first',
           [recipient_last]  NVARCHAR(25) '$.last'
         )
)
  json_recipient

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b79a6ecb271d4322487ede013d54c8e0