5

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.

6
+50

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];
  • Nice. The recursive approach allows for data changes. Can you think of a JSONy way of returning only the single path from Emma to Amy? I know this output forms an adjacency list and I could perform a recursive path walk upwards. I was hoping for something in the JSON itself, if you have a suggestion. – Michael Green May 28 at 12:47
  • @MichaelGreen - is this what you were looking for? dbfiddle.uk/… – Peter Vandivier May 28 at 13:44
  • @PeterVandivier Plagiarism excused :) I was trying to get the recursion working myself so it would handle N levels automatically but couldn't get it quite right – HandyD May 28 at 23:52
  • @PeterVandivier Yes, that's it. I had hoped there was a JSON path "get parent" operator but it seems there is not. – Michael Green May 29 at 4:43
2

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
  • Thank you. Two questions 1) how would you return the just the Emma - Chris - Amy path without the other nodes? 2) if we added Frances, reporting to Emma, wouldn't this query have to be extended by another level? – Michael Green May 28 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.