具有这种简单的多对多自引用结构。
一个项目通过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_id
1(伪代码)查询项目:
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 功能后,我通过重复嵌套查询来管理此类输出。简单但丑陋,并且受限于重复的数量。:/
我发现了递归查询。在这里和那里找到的例子并不是那么简单。很难找到一个切入点来理解这项技术并使其适应我的需要。
我希望这里的示例足够简单,可以从有经验的用户那里获得帮助。
这是一个示例查询,
请注意,我们实际上并没有合并路径以形成完整的树。您要么必须从根节点向下构建树,要么必须从叶节点到顶部构建树。在这种情况下,您必须合并离散路径。在 Javascript 中寻找深度 json 合并并将其与 plv8 绑定在一起。
递归 CTE (rCTE)不允许在递归项中进行聚合。所以没有简单的解决方案。
我建议一个优雅的解决方案的递归函数:
db<>在这里摆弄
裸电话:
剥离具有 NULL 值的对象(无子对象)并美化:
准确地产生您想要的输出(一棵完整的树):
jsonb_strip_nulls()
...这可以很好地删除所有空
children
字段 (NULL
)。如果您想保留其他具有 NULL 值的字段,则必须做更多的事情。后来,与替代方案密切相关的答案(最明显的是最大递归级别):