Find Ancestry From JSON
This seems to me to be a pretty vanilla recursion query so long as you unwrap the JSON dynamically.
If HandyD will excuse some slight plagiarism...
declare @Employees nvarchar(max) =
'{
"person": "Amy",
"staff": [
{ "person": "Bill" },
{
"person": "Chris",
"staff": [
{ "person": "Dan" },
{ "person": "Emma" }
]
}
]
}';
;WITH hier ([Level], Parent, Person, staff) AS (
SELECT
1 AS [Level],
convert(nvarchar(255),'') AS Parent,
l1.person,
l1.staff
FROM OPENJSON(@Employees) WITH (
person NVARCHAR(255),
staff NVARCHAR(MAX) AS JSON
) l1
UNION ALL
select
[Level]+1,
convert(nvarchar(255),h.Person) as Parent,
oj.person,
oj.staff
from hier h
outer apply openjson(h.staff) with (
person NVARCHAR(255),
staff NVARCHAR(MAX) AS JSON
) oj
where [Level] < 100 -- or relevant maxrecursion level
and oj.Person is not null
)
SELECT
[Level],
Parent,
Person
FROM hier
order by [Level];
You can use OPENJSON and a CTE to extract each person and their associated staff:
declare @Employees nvarchar(max) =
'{
"person": "Amy",
"staff": [
{ "person": "Bill" },
{
"person": "Chris",
"staff": [
{ "person": "Dan" },
{ "person": "Emma" }
]
}
]
}';
;WITH Level1 AS (
SELECT 1 AS Level, NULL AS Parent, l1.person, l1.staff
FROM OPENJSON(@Employees)
WITH
(
person NVARCHAR(255),
staff NVARCHAR(MAX) AS JSON
) l1
), Level2 AS (
SELECT 2 AS Level, l1.person AS Parent, l2.person, l2.staff
FROM Level1 l1
CROSS APPLY OPENJSON(l1.staff)
WITH
(
person NVARCHAR(255),
staff NVARCHAR(MAX) AS JSON
) l2
), Level3 AS (
SELECT 3 AS Level, l2.person AS Parent, l3.person, l3.staff
FROM Level2 l2
CROSS APPLY OPENJSON(l2.staff)
WITH
(
person NVARCHAR(255),
staff NVARCHAR(MAX) AS JSON
) l3
)
SELECT Level, Parent, Person FROM Level1
UNION
SELECT Level, Parent, Person FROM Level2
UNION
SELECT Level, Parent, Person FROM Level3
ORDER BY Level
Returns:
Level Parent Person
----------------------
1 NULL Amy
2 Amy Bill
2 Amy Chris
3 Chris Emma
3 Chris Dan