Post reply

Nested Json Parsing issue

  • 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"}

  • 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;

    • This reply was modified 4 years, 6 months ago by  Mark Cowne.
    • This reply was modified 4 years, 6 months ago by  Mark Cowne.

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • May 28, 2020 at 4:21 pm

    #3758228

    That was it. Thanks Mark

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply