SQL Server – How to Find Ancestry from JSON

jsonsql servert-sql

I have a hierarchy that looks like this:

enter image description here

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.

Best Answer

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];
Related Question