從 JSON 中查找祖先

我有一個看起來像這樣的層次結構：

作為 TSQL 中的 JSON，它是這樣的：

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

這只是一個例子。實際數據可以是任何深度或寬度不確定的樹。

我發現的文件和所有範例都顯示了自上而下的遍歷。每個 JSON 路徑都從根節點開始，並通過已知節點名稱導航到所需節點。我沒有發現從層次結構中的不確定深度開始向上工作。我覺得我需要像傳遞閉包這樣的東西。

給定一個名字，我想得到這個名字的祖先。例如，給定“Emma”，結果將是“Emma / Chris / Amy”。給定“Bill”，答案將是“Bill / Amy”。輸出格式不重要；它可以是 JSON、字元串或結果集。名字是獨一無二的。

這是我自己的學習練習。可以將原始 JSON 表示更改為任何等效的表示，只要它仍然是 JSONy。在層次結構的鄰接列表表示上的 JSON_QUERY 不會達到我的目標。

在我看來，這似乎是一個非常普通的遞歸查詢，只要您動態地解開 JSON。

如果HandyD會原諒一些輕微的抄襲……

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

引用自：https://dba.stackexchange.com/questions/239180