Ttdfjt

I have a hierarchy that looks like this:

As JSON in TSQL it is this:

declare @Employees nvarchar(max) =
'
 "person": "Amy",
 "staff": [
 "person": "Bill" ,
 
 "person": "Chris",
 "staff": [
 "person": "Dan" ,
 "person": "Emma" 
 ]
 
 ]
';

This is just an example. The actual data could be any tree of indeterminate depth or breadth.

The documentation and all the examples I've found show top-down traversal. Every JSON path starts at the root node and navigates, by known node names, toward the desired node. I've found nothing about starting at an indeterminate depth within the hierarchy and working upward. I feel like I need something like transitive closure.

Given a name I would like to get the ancestry for that name. For example, given "Emma" the result will be "Emma / Chris / Amy". Given "Bill" the answer will be "Bill / Amy". The output format is unimportant; it can be JSON, a string or a resultset. The names are unique.

This is a learning exercise for myself. It's OK to change the original JSON representation to anything equivalent as long as it's still JSONy. A JSON_QUERY over an adjacency list representation of the hierarchy wouldn't achieve my goal.

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