Find Ancestry From JSON

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.

db<>fiddle

previous, simpler db<>fiddle
declare @json nvarchar(max) =
'{
"person": "Amy",
"staff": [
{ "person": "Bill" },
{
"person": "Chris",
"staff": [
{ "person": "Dan" },
{ "person": "Emma" }
]
}
]
}';

with level_0 as (
select
convert(int,0) as [Level],
convert(nvarchar(4000),N'$') as [Key],
@json as [Value],
convert(
int,
case left(@json,1)
when N'[' then 4
when N'{' then 5
else 0
end
) as [Type]
)
, key_value_unwrap as(
select
l0.[Level] + 1 as [Level],
convert(nvarchar(max),null) as Parent,
l0.[Key] + iif(l0.[Type] = 5, '.' + oj.[Key], quotename(-1 + row_number() over (order by (select null)))) collate database_default as [Key],
oj.[Value],
oj.[Type]
from level_0 l0
outer apply openjson(l0.[Value]) as oj
where l0.[Value] is not null
union all
select
kvu.[Level] + 1 as [Level],
convert(nvarchar(max),kvu.[Key]) as Parent,
kvu.[Key] + iif(kvu.[Type] = 5, '.' + oj.[Key], quotename(-1 + row_number() over (order by (select null)))) as [Key],
oj.[Value],
oj.[Type]
from key_value_unwrap as kvu
outer apply openjson(kvu.[Value], 'lax $') as oj
where kvu.[Type] in (4,5)
), _union as (
select
l0.[Level],
convert(nvarchar(max),null) as Parent,
l0.[Key] + N'.' as [Key],
l0.[Value],
l0.[Type]
from level_0 as l0
union all
select
kvu.[Level],
kvu.Parent,
kvu.[Key],
kvu.[Value],
kvu.[Type]
from key_value_unwrap as kvu
)
select
u.[Level],
iif(u.[Level]=1,N'$.',u.Parent) as Parent,
u.[Key],
u.[Value],
u.[Type]
from _union as u
where u.[Type] not in (4,5);


Run in-line or bundled up into a function, you can parse out the JSON path for each node with a corresponding parent path.
select *
from dbo.json_shred(@Employees)
where [Type] not in (4,5);


Level
Parent
Key
Value
Type

1
$.
$.person
Amy
1

3
$.staff[1]
$.staff[1].person
Chris
1

5
$.staff[1].staff[1]
$.staff[1].staff[1].person
Emma
1

5
$.staff[1].staff[0]
$.staff[1].staff[0].person
Dan
1

3
$.staff[0]
$.staff[0].person
Bill
1

StackExchange Database Administrators Q#239180, answer score: 7