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

Table

Please [Edit](https://stackoverflow.com/posts/70843770/edit) your question to provide more information - as text. You've tagged `sql-server` but what specific version of Microsoft SQL Server do you mean? Not all supported versions of SQL Server actually support JSON queries and construction. What SQL queries have you tried so far? — AlwaysLearning, Jan 25 '22 at 06:02
@JustStartlDev, a good starting [point](https://stackoverflow.com/questions/61800927/read-any-json-into-list-of-key-value-pairs-eav-format-in-sql-server), using recursive CTE. — Zhorov, Jan 25 '22 at 07:07

Dordi · Answer 1 · 2022-01-25T09:34:05.267

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')

OMG, thanks so much, its helped me <3 – JustStartlDev Jan 25 '22 at 12:02 — JustStartlDev, Jan 25 '22 at 12:02

score 1 · Accepted Answer · answered Jan 25 '22 at 10:35

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

How can i apply it for new json string ? if i dont know its structure — JustStartlDev, Jan 25 '22 at 12:24
@JustStartlDev, simply assign the JSON cointent to the `@json` variable and execute the recursive statement again. — Zhorov, Jan 25 '22 at 12:26
I mean, if i have a new json string in a colums, and i want to query it to a table, how can i do it, cause i see you use 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 after with, how can i insert another variable ? IF there is a key: value before person ? — JustStartlDev, Jan 25 '22 at 14:34

Json query to a table?

2 Answers2