How to get complex JSON string into columns SQL Server

Question

I have stored some data in the table as JSON string like below.

[
   {
      "firstName":"John",
      "lastName":"Smith",
      "age":25,
      "Address":{
         "streetAddress":"21 2nd Street",
         "city":"New York",
         "state":"NY",
         "postalCode":"10021"
      },
      "PhoneNumbers":{
         "home":"212 555-1234",
         "fax":"646 555-4567"
      }
   },
   {
      "firstName":"Mike",
      "lastName":"Lee",
      "age":30,
      "Address":{
         "streetAddress":"10 Street",
         "city":"New York",
         "state":"NY",
         "postalCode":"10021"
      },
      "PhoneNumbers":{
         "home":"212 555-3265",
         "fax":""
      }
   }
]

To export these data to an excel file I need to make a query to get these details like below

with CROSS APPLY OPENJSON I can get names, and ages, but how can I get the address and contact details as columns?

Answer 1

You need to use OPENJSON() and the appropriate paths:

JSON:

DECLARE @json nvarchar(max) = N'[
   {
      "firstName":"John",
      "lastName":"Smith",
      "age":25,
      "Address":{
         "streetAddress":"21 2nd Street",
         "city":"New York",
         "state":"NY",
         "postalCode":"10021"
      },
      "PhoneNumbers":{
         "home":"212 555-1234",
         "fax":"646 555-4567"
      }
   },
   {
      "firstName":"Mike",
      "lastName":"Lee",
      "age":30,
      "Address":{
         "streetAddress":"10 Street",
         "city":"New York",
         "state":"NY",
         "postalCode":"10021"
      },
      "PhoneNumbers":{
         "home":"212 555-3265",
         "fax":""
      }
   }
]'

Statement:

SELECT *
FROM OPENJSON(@json) WITH (
   FirstName nvarchar(100) '$.firstName',
   LastName nvarchar(100) '$.lastName',
   Age int '$.age',
   Name nvarchar(100) '$.firstName',
   StreetAddress nvarchar(100) '$.Address.streetAddress',
   City nvarchar(100) '$.Address.city',
   State nvarchar(100) '$.Address.state',
   PostalCode nvarchar(100) '$.Address.postalCode',
   HomePhone nvarchar(100) '$.PhoneNumbers.home',
   Fax nvarchar(100) '$.PhoneNumbers.fax'
)

Result:

FirstName LastName Age Name StreetAddress City     State PostalCode HomePhone    Fax
John      Smith     25 John 21 2nd Street New York NY    10021      212 555-1234 646 555-4567
Mike      Lee       30 Mike 10 Street     New York NY    10021      212 555-3265