Nested Json Parsing issue

Question

Post reply

Nested Json Parsing issue

Paul Stasny

Mr or Mrs. 500

Points: 599
More actions
May 26, 2020 at 5:34 pm
Go to Answer
#3757504
Greetings,
I am having issues parsing the following Json string using openjson. I am having problems getting at the piece under custom fields. If I omit that, I can bring in the rest of the fields. Looking at other posts, it looks like it requires multiple cross apply statements but I haven't got the syntax quite right. If I omit the custom_fields pioece I can get the rest of the fields in. Please point me in the right direction. Thanks.
```
{"data": [{"id": "123456", "first_name": "John", "last_name": "Doe", "email": "jdoe@gmail.com", "phone_number": 333-333-3333, "href": "http://jdoe.com", "custom_fields": {"1": "John Doe | Jane Doe"}, "status": "active"}
```

Mark Cowne

One Orange Chip

Points: 26978

May 26, 2020 at 9:39 pm

Answer

#3757561

I think this is what you're after

declare @json nvarchar(max) = '
{"data": [{"id": "123456", "first_name": "John", "last_name": "Doe", "email": "jdoe@gmail.com", "phone_number": "333-333-3333", "href": "http://jdoe.com", "custom_fields": {"1": "John Doe | Jane Doe"}, "status": "active"}] }
';


select a.id,a.first_name,a.last_name,a.email,a.phone_number,a.href,a.status,b.[key] as customkey,b.value as customvalue
from  openjson(@json, '$.data') 
with (id   varchar(30)  '$.id',
      first_name varchar(30) '$.first_name',
      last_name varchar(30) '$.last_name',
      email varchar(30) '$.email',
      phone_number varchar(30) '$.phone_number',
      href varchar(30) '$.href',
      custom_fields nvarchar(max) '$.custom_fields' as json,
      status varchar(30) '$.status') a
cross apply openjson(a.custom_fields, '$') b;