Json query to a table?

Question

I have a JSON string as shown below. How can I create a table below or similar using SQL Server with a procedure or function? Thanks all.

I'm using SQL Server 15.0.2080.9.

{    
    "Person": {
                  "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"
                  }
            }
}

Answer 1

An excellent starting point is this Q&A, but a simplified approach (if the parsed JSON has a variable structure with nested JSON objects, but without JSON arrays) is the folowing recursive statement:

JSON:

DECLARE @json nvarchar(max) = N'
{    
    "Person": {
                  "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"
                  }
            }
}'

Statement:

;WITH rCTE AS (
   SELECT 
      1 AS Id,
      CONVERT(nvarchar(max), NULL) COLLATE DATABASE_DEFAULT AS [Parent], 
      CONVERT(nvarchar(max), N'Person') COLLATE DATABASE_DEFAULT AS [Key], 
      CONVERT(nvarchar(max), JSON_QUERY(@json, '$.Person')) COLLATE DATABASE_DEFAULT AS [Value]
   UNION ALL
   SELECT
      r.Id + 1,
      CONVERT(nvarchar(max), r.[Key]) COLLATE DATABASE_DEFAULT,
      CONVERT(nvarchar(max), c.[Key]) COLLATE DATABASE_DEFAULT,
      CONVERT(nvarchar(max), c.[value]) COLLATE DATABASE_DEFAULT                                        
   FROM rCTE r
   CROSS APPLY OPENJSON(r.[Value]) c
   WHERE ISJSON(r.[Value]) = 1
)
SELECT [Parent], [Key], [Value]
FROM rCTE
ORDER BY Id

Result:

Parent	Key	Value
	Person	{"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" }}
Person	firstName	John
Person	lastName	Smith
Person	age	25
Person	Address	{"streetAddress":"21 2nd Street", "city":"New York", "state":"NY", "postalCode":"10021"}
Person	PhoneNumbers	{"home":"212 555-1234", "fax":"646 555-4567"}
PhoneNumbers	home	212 555-1234
PhoneNumbers	fax	646 555-4567
Address	streetAddress	21 2nd Street
Address	city	New York
Address	state	NY
Address	postalCode	10021

Answer 2

You can use Openjson, it would give you your desired result.

this is an example for your specific JSON:

DECLARE @Json NVARCHAR(max) = '{    
    "Person": {
                  "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"
                  }
            }
}'

SELECT NULL AS Parent
    ,[KEY]
    ,[value]
FROM openjson(@json, '$')

UNION ALL

SELECT 'Person' AS Parent,
[KEY]
    ,[value]
FROM openjson(@json, '$.Person')

UNION ALL

SELECT 'Address'AS Parent,
[KEY]
    ,[value]
FROM openjson(@json, '$.Person.Address')

UNION ALL

SELECT 'PhoneNumbers' AS Parent ,[KEY]
    ,[value]
FROM openjson(@json, '$.Person.PhoneNumbers')