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?

Tag the question with the RDBMS you're using. I'm assuming SQL Server given the NVARCHAR, but why make anyone assume. — Jeroen Mostert
– Jeroen Mostert, Commented Mar 1, 2022 at 10:15

Salman Arshad · Accepted Answer · 2022-03-01 10:31:39Z

2

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

answered Mar 1, 2022 at 10:31

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MatBailie · Accepted Answer · 2022-03-01 10:23:40Z

2

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

answered Mar 1, 2022 at 10:23

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

1 Comment

Jeroen Mostert Over a year ago

Solid, but seems unnecessarily complicated given that we only need one value from the root object.

SELECT id, JSON_VALUE(json, '$.requester') AS Requester, [first] AS [Recipient first] FROM example CROSS APPLY OPENJSON(json, '$.recipients') WITH ([first] NVARCHAR(25))

should suffice.

Collectives™ on Stack Overflow

Extract items from JSON containing a nested array

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related