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.

2 Answers 2

7
+50

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
0
3

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
1
  • 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? May 28, 2019 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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