Extract items from JSON containing a nested array-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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