如何将树形结构的表聚合成单个嵌套的JSON对象？ [英] How to aggregate a table with tree-structure to a single nested JSON object?
本文介绍了如何将树形结构的表聚合成单个嵌套的JSON对象？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在Postgres 11.4数据库中有一个表，该表具有自引用树结构：
+------------+
| account |
+------------+
| id |
| code |
| type |
| parentId | -- references account.id
+------------+
每个子项可以有另一个子项，嵌套级别没有限制。
我想从它生成一个JSON对象，嵌套所有子对象(重新生成)。
可以用一个查询来解决这个问题吗？
或使用带有一个表的TypeORM的任何其他解决方案？
否则，我将不得不在服务器端手动绑定数据。
我尝试了此查询：
SELECT account.type, json_agg(account) as accounts
FROM account
-- LEFT JOIN "account" "child" ON "child"."parentId"="account"."id" -- tried to make one column child
GROUP BY account.type
结果：
[
...
{
"type": "type03",
"accounts": [
{
"id": 28,
"code": "acc03.001",
"type": "type03",
"parentId": null
},
{
"id": 29,
"code": "acc03.001.001",
"type": "type03",
"parentId": 28
},
{
"id": 30,
"code": "acc03.001.002",
"type": "type03",
"parentId": 28
}
]
}
...
]
我期望的是：
[
...
{
"type": "type03",
"accounts": [
{
"id": 28,
"code": "acc03.001",
"type": "type03",
"parentId": null,
"child": [
{
"id": 29,
"code": "acc03.001.001",
"type": "type03",
"parentId": 28
},
{
"id": 30,
"code": "acc03.001.002",
"type": "type03",
"parentId": 28
}
]
}
]
}
...
]
推荐答案
这很棘手。 这是一个递归问题，但标准recursive CTEs不足以处理它，因为我们需要在每个级别上进行聚合，而CTE不允许在递归项中进行聚合。
我用一个PL/pgSQL函数解决了这个问题：
CREATE OR REPLACE FUNCTION f_build_jsonb_tree(_type text = NULL)
RETURNS jsonb
LANGUAGE plpgsql AS
$func$
DECLARE
_nest_lvl int;
BEGIN
-- add level of nesting recursively
CREATE TEMP TABLE t ON COMMIT DROP AS
WITH RECURSIVE t AS (
SELECT *, 1 AS lvl
FROM account
WHERE "parentId" IS NULL
AND (type = _type OR _type IS NULL) -- default: whole table
UNION ALL
SELECT a.*, lvl + 1
FROM t
JOIN account a ON a."parentId" = t.id
)
TABLE t;
-- optional idx for big tables with many levels of nesting
-- CREATE INDEX ON t (lvl, id);
_nest_lvl := (SELECT max(lvl) FROM t);
-- no nesting found, return simple result
IF _nest_lvl = 1 THEN
RETURN ( -- exits functions
SELECT jsonb_agg(sub) -- AS result
FROM (
SELECT type
, jsonb_agg(sub) AS accounts
FROM (
SELECT id, code, type, "parentId", NULL AS children
FROM t
ORDER BY type, id
) sub
GROUP BY 1
) sub
);
END IF;
-- start collapsing with leaves at highest level
CREATE TEMP TABLE j ON COMMIT DROP AS
SELECT "parentId" AS id
, jsonb_agg (sub) AS children
FROM (
SELECT id, code, type, "parentId" -- type redundant?
FROM t
WHERE lvl = _nest_lvl
ORDER BY id
) sub
GROUP BY "parentId";
-- optional idx for big tables with many levels of nesting
-- CREATE INDEX ON j (id);
-- iterate all the way down to lvl 2
-- write to same table; ID is enough to identify
WHILE _nest_lvl > 2
LOOP
_nest_lvl := _nest_lvl - 1;
INSERT INTO j(id, children)
SELECT "parentId" -- AS id
, jsonb_agg(sub) -- AS children
FROM (
SELECT id, t.code, t.type, "parentId", j.children -- type redundant?
FROM t
LEFT JOIN j USING (id) -- may or may not have children
WHERE t.lvl = _nest_lvl
ORDER BY id
) sub
GROUP BY "parentId";
END LOOP;
-- nesting found, return nested result
RETURN ( -- exits functions
SELECT jsonb_agg(sub) -- AS result
FROM (
SELECT type
, jsonb_agg (sub) AS accounts
FROM (
SELECT id, code, type, "parentId", j.children
FROM t
LEFT JOIN j USING (id)
WHERE t.lvl = 1
ORDER BY type, id
) sub
GROUP BY 1
) sub
);
END
$func$;
调用(准确返回所需结果)：
SELECT jsonb_pretty(f_build_jsonb_tree());
db<；>；小提琴here-扩展测试用例
我选择了密钥名称children，而不是child，因为可以嵌套多个。
jsonb_pretty()美化显示是可选的。
这是假定引用完整性；应使用FK约束实现。
对于您的特定情况，解决方案可能更简单，使用code列-如果它显示(未公开)有用的属性。例如，我们可能会在没有rCTE的情况下派生嵌套级别，并添加临时表t。但我的目标是仅基于ID引用的一般解决方案。
该函数中有很多内容。我添加了内联注释。基本上，它是这样做的：
- 创建添加嵌套级别的临时表(
lvl) - 如果未找到嵌套，则返回简单结果
- 如果找到嵌套，则从顶层向下折叠到
jsonb。
将所有中间结果写入第二个临时表j。 - 到达第二个嵌套级别后，返回完整结果。
该函数以_type作为参数，仅返回给定类型。否则，将处理整个表。
"parentId"这样的大小写混合的标识符。请参阅：
相关答案使用递归函数：
这篇关于如何将树形结构的表聚合成单个嵌套的JSON对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
