Microsoft SQL Server JSON in Sql Server Join parent and child JSON entities using CROSS APPLY OPENJSON
Example
Join parent objects with their child entities, for example we want a relational table of each person and their hobbies
DECLARE @json nvarchar(1000) =
N'[
{
"id":1,
"user":{"name":"John"},
"hobbies":[
{"name": "Reading"},
{"name": "Surfing"}
]
},
{
"id":2,
"user":{"name":"Jane"},
"hobbies":[
{"name": "Programming"},
{"name": "Running"}
]
}
]'
Query
SELECT
JSON_VALUE(person.value, '$.id') as Id,
JSON_VALUE(person.value, '$.user.name') as PersonName,
JSON_VALUE(hobbies.value, '$.name') as Hobby
FROM OPENJSON (@json) as person
CROSS APPLY OPENJSON(person.value, '$.hobbies') as hobbies
Alternatively this query can be written using the WITH clause.
SELECT
Id, person.PersonName, Hobby
FROM OPENJSON (@json)
WITH(
Id int '$.id',
PersonName nvarchar(100) '$.user.name',
Hobbies nvarchar(max) '$.hobbies' AS JSON
) as person
CROSS APPLY OPENJSON(Hobbies)
WITH(
Hobby nvarchar(100) '$.name'
)
Result
| Id | PersonName | Hobby |
|---|---|---|
| 1 | John | Reading |
| 1 | John | Surfing |
| 2 | Jane | Programming |
| 2 | Jane | Running |
