從遞歸樹結構創建 JSON 對象

具有這種簡單的多對多自引用結構。

一個項目通過joins表擁有其他項目：

CREATE TABLE items (
 item_id   serial PRIMARY KEY
, title     text
);

CREATE TABLE joins (
 id        serial PRIMARY KEY
, item_id   int
, child_id  int
);

INSERT INTO items (item_id, title) VALUES
 (1, 'PARENT')
, (2, 'LEVEL 2')
, (3, 'LEVEL 3.1')
, (4, 'LEVEL 4.1')
, (5, 'LEVEL 4.2')
, (6, 'LEVEL 3.2')
;

INSERT INTO joins (item_id, child_id) VALUES
 (1, 2)
, (2, 3)
, (3, 4)
, (3, 5)
, (2, 6)
;

db<>在這裡擺弄

我正在嘗試將整個樹結構檢索為給定項目的 JSON。

例如，用item_id1（虛擬碼）查詢項目：

SELECT i.*, fulltree from items i where item_id = 1;

所需的輸出fulltree：

{
 id: 1,
 title: "PARENT",
 children: [
  {
    id: 2,
    title: "LEVEL 2",
    children: [
     {
       id: 3,
       title: "LEVEL 3.1",
       children: [
         {
           id: 4,
           title: "LEVEL 4.1"
         },
         {
           id: 5,
           title: "LEVEL 4.2"
         }
       ]
     },
     {
       id: 6,
       title: "LEVEL 3.2"
     }
    ]
  }
 ]
}

在深入研究 Postgres 提供的 JSON 功能後，我通過重複嵌套查詢來管理此類輸出。簡單但醜陋，並且受限於重複的數量。：/

我發現了遞歸查詢。在這里和那裡找到的例子並不是那麼簡單。很難找到一個切入點來理解這項技術並使其適應我的需要。

我希望這裡的範例足夠簡單，可以從有經驗的使用者那裡獲得幫助。

遞歸 CTE (rCTE)不允許在遞歸項中進行聚合。所以沒有簡單的解決方案。

我建議一個優雅的解決方案的遞歸函式：

CREATE OR REPLACE FUNCTION f_item_tree(_item_id int)
 RETURNS jsonb
 LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT jsonb_agg(sub)
FROM  (
  SELECT i.*, f_item_tree(i.item_id) AS children
  FROM   joins j
  JOIN   items i ON i.item_id = j.child_id
  WHERE  j.item_id = _item_id
  ORDER  BY i.item_id
  ) sub
$func$;

db<>在這裡擺弄

裸電話：

SELECT to_jsonb(sub) AS tree
FROM  (
  SELECT *, f_item_tree(item_id) AS children
  FROM   items
  WHERE  item_id = 1  -- root item_id HERE
  ) sub;

剝離具有 NULL 值的對象（無子對象）並美化：

SELECT jsonb_pretty(jsonb_strip_nulls(to_jsonb(sub))) AS tree
FROM  (
  SELECT *, f_item_tree(item_id) AS children
  FROM   items
  WHERE  item_id = 1  -- root item_id HERE
  ) sub;

準確地產生您想要的輸出（一棵完整的樹）：

{
   "title": "PARENT",
   "item_id": 1,
   "children": [
       {
           "title": "LEVEL 2",
           "item_id": 2,
           "children": [
               {
                   "title": "LEVEL 3.1",
                   "item_id": 3,
                   "children": [
                       {
                           "title": "LEVEL 4.1",
                           "item_id": 4
                       },
                       {
                           "title": "LEVEL 4.2",
                           "item_id": 5
                       }
                   ]
               },
               {
                   "title": "LEVEL 3.2",
                   "item_id": 6
               }
           ]
       }
   ]
}

jsonb_strip_nulls()…

從給定的 JSON 值中遞歸刪除所有具有空值的對象欄位。

這可以很好地刪除所有空children欄位 ( NULL)。如果您想保留其他具有 NULL 值的欄位，則必須做更多的事情。

後來，與替代方案密切相關的答案（最明顯的是最大遞歸級別）：

• 如何將一組扁平的樹變成一棵有多個葉子的樹？

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