postgresql-用递归构建树并将其导出为json [英] postgresql - building a tree with recursive and export that as json
本文介绍了postgresql-用递归构建树并将其导出为json的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在从表开始构建树时遇到问题。
我正在关注教程来自D.Fontaine，但我的查询中有问题。
I'm having issues on building a tree starting from a table. I'm following the tutorial from D.Fontaine but something is wrong in my query.
这是此表中的行的子集：
here is a subset of rows from this table:
abs=# select * from myproj_loparentrelation where root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e' order by level ASC;
id | root_id | level | display_sequence | lo_id | parent_id
--------+--------------------------------------+-------+------------------+--------------------------------------+--------------------------------------
90468 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 2 | 1 | 5c51558b-1180-495f-88c3-f7af49bafcf3 | 57b2e67b-5862-499a-a471-0f2f6b23440e
55209 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 2 | 16 | 3962f997-9e14-4cac-a95f-dc20c077a531 | 57b2e67b-5862-499a-a471-0f2f6b23440e
14890 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 3 | 17 | 0f335cd1-74d0-4a5e-831d-1aba0c0dc396 | 3962f997-9e14-4cac-a95f-dc20c077a531
116513 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 3 | 4 | 78b6cac3-307f-449a-bb3c-fe4442f4d1e8 | 5c51558b-1180-495f-88c3-f7af49bafcf3
122691 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 3 | 6 | 7ecb01da-078a-49b9-b488-86dc6bb24ab4 | 5c51558b-1180-495f-88c3-f7af49bafcf3
95729 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 3 | 14 | 62e889fe-b0e6-4fd4-a89f-512a1f31e210 | 5c51558b-1180-495f-88c3-f7af49bafcf3
141390 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 3 | 2 | 932353b7-bb63-4bad-87e7-70bcebcbbc98 | 5c51558b-1180-495f-88c3-f7af49bafcf3
96022 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 3 | 26 | 6346657d-a41e-45ab-87bc-abf32a8c616c | 3962f997-9e14-4cac-a95f-dc20c077a531
61116 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 22 | 3f07cece-0a7c-411e-b7d1-0370d18c6405 | 0f335cd1-74d0-4a5e-831d-1aba0c0dc396
72097 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 5 | 49c43039-84e6-4f36-a8a1-5f57ad1bc67e | 78b6cac3-307f-449a-bb3c-fe4442f4d1e8
81260 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 15 | 51e9ce01-9c90-437a-b17c-8b0b19c3a2b4 | 62e889fe-b0e6-4fd4-a89f-512a1f31e210
84177 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 12 | 553150c1-ff43-44e0-a52d-d6361813de73 | 7ecb01da-078a-49b9-b488-86dc6bb24ab4
89331 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 24 | 5b1ede40-66a6-4320-be79-69f0a4d0340e | 0f335cd1-74d0-4a5e-831d-1aba0c0dc396
111377 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 21 | 73f05e3c-012e-4b41-acc0-14791c83f710 | 0f335cd1-74d0-4a5e-831d-1aba0c0dc396
114868 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 33 | 778bf627-e83f-4385-bbb9-3537d45d18b6 | 6346657d-a41e-45ab-87bc-abf32a8c616c
129785 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 27 | 8650b402-bb6f-4be2-9585-800d189ded83 | 6346657d-a41e-45ab-87bc-abf32a8c616c
151263 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 10 | 9f29806f-d04d-4952-9743-9b2eedeecfee | 7ecb01da-078a-49b9-b488-86dc6bb24ab4
152546 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 34 | a0958fb5-21c2-440b-8dc9-02659b34691e | 6346657d-a41e-45ab-87bc-abf32a8c616c
158562 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 13 | a7f96063-e78e-4248-a512-68d726b0ff09 | 7ecb01da-078a-49b9-b488-86dc6bb24ab4
165779 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 7 | aeacbddd-37d6-4eda-9695-bace3406f415 | 7ecb01da-078a-49b9-b488-86dc6bb24ab4
177880 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 28 | bc18337c-899c-416b-91e4-93e4c7699d58 | 6346657d-a41e-45ab-87bc-abf32a8c616c
188650 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 11 | c6a82ea7-2156-492e-9f0f-a73c36afba70 | 7ecb01da-078a-49b9-b488-86dc6bb24ab4
204117 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 30 | d7924927-55c3-4085-bdd6-5420ef7162a9 | 6346657d-a41e-45ab-87bc-abf32a8c616c
10528 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 19 | 0be4c51c-3d52-499e-98fb-3dac9b2b1e49 | 0f335cd1-74d0-4a5e-831d-1aba0c0dc396
223079 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 3 | ebb92498-a730-4b61-b1dd-44696aae4a8e | 932353b7-bb63-4bad-87e7-70bcebcbbc98
11987 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 29 | 0ccd9922-85fa-45de-bd87-257b77e2a5a3 | 6346657d-a41e-45ab-87bc-abf32a8c616c
22808 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 32 | 17f12743-e0c4-4f32-a1d6-0e680fa762d5 | 6346657d-a41e-45ab-87bc-abf32a8c616c
28627 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 31 | 1eb6653a-de11-42a2-9766-4222996ff75e | 6346657d-a41e-45ab-87bc-abf32a8c616c
35421 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 20 | 26418eb9-22d4-41f4-a117-ae1884987080 | 0f335cd1-74d0-4a5e-831d-1aba0c0dc396
36110 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 25 | 27198280-5cff-462c-87be-5fb8b6a7b309 | 0f335cd1-74d0-4a5e-831d-1aba0c0dc396
46312 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 23 | 319838f6-aebc-460e-ae77-009858f2f858 | 0f335cd1-74d0-4a5e-831d-1aba0c0dc396
48878 | 57b2e67b-5862-499a-a471-0f2f6b23440e | 4 | 9 | 33bf5a6f-5b72-4bf0-9e1b-a68f7ad69a64 | 7ecb01da-078a-49b9-b488-86dc6bb24ab4
- 根是
57b2e67b-5862-499a-a471-0f2f6b23440e - 在根下，有两个孩子
5c51558b-1180-495f- 88c3-f7af49bafcf3和3962f997-9e14-4cac-a95f-dc20c077a531 - 其中每个都有其他儿童等。
- root is
57b2e67b-5862-499a-a471-0f2f6b23440e - under the root, there are two children
5c51558b-1180-495f-88c3-f7af49bafcf3and3962f997-9e14-4cac-a95f-dc20c077a531 - each one of those has other children and so on.
预期结果如下：
[
{'Name': '57b2e67b-5862-499a-a471-0f2f6b23440e',
'Sub Classes': [
{'Name': '5c51558b-1180-495f-88c3-f7af49bafcf3',
'Subclasses': [
]},
{'Name': '3962f997-9e14-4cac-a95f-dc20c077a531',
'Subclasses': [
{'Name': '5c51558b-1180-495f-88c3-f7af49bafcf3',
'Subclasses': [
...
]}
]}
}
]
但事实并非如此我搞砸了查询如下：
but it's not so I'm messing up something. here's the query:
with recursive rels_from_parents as
(
select sel.lo_id, '{}'::uuid[] as parents, sel.level as _level
from (select * from myproj_loparentrelation where root_id = '57b2e67b-5862-499a-a471-0f2f6b23440e') as sel
where sel.parent_id = sel.root_id
union all
select c.lo_id, parents || c.parent_id, c.level as _level
from rels_from_parents p
join myproj_loparentrelation c
on c.parent_id = p.lo_id
where not c.lo_id = any(parents)
),
rels_from_children as
(
select c.parent_id,
json_agg(jsonb_build_object('lo_id', c.lo_id::text))::jsonb as js
from rels_from_parents tree
join myproj_loparentrelation c using(lo_id)
where level > 0 and not lo_id = any(parents)
group by c.parent_id
union all
select c.parent_id,
jsonb_build_object('Name', c.lo_id::text)
|| jsonb_build_object('Sub Classes', js) as js
from rels_from_children tree
join myproj_loparentrelation c on c.lo_id = tree.parent_id
)
select jsonb_pretty(jsonb_agg(js))
from rels_from_children;
有人可以提供帮助吗？
推荐答案
WITH RECURSIVE tree(lo_id, ancestor, child, path, json) AS (
SELECT
t1.lo_id,
NULL::text,
t2.lo_id,
'{Subclasses}'::text[] || (row_number() OVER (PARTITION BY t1.lo_id ORDER BY t2.display_sequence) - 1)::text,
jsonb_build_object('Name', t2.lo_id, 'Subclasses', array_to_json(ARRAY[]::text[]))
FROM test t1
LEFT JOIN test t2 ON t1.lo_id = t2.parent_id
WHERE t1.parent_id IS NULL
UNION
SELECT
t1.lo_id,
t1.parent_id,
t2.lo_id,
tree.path || '{Subclasses}' || (row_number() OVER (PARTITION BY t1.lo_id ORDER BY t2.display_sequence) - 1)::text,
jsonb_build_object('Name', t2.lo_id, 'Subclasses', array_to_json(ARRAY[]::text[]))
FROM test t1
LEFT JOIN test t2 ON t1.lo_id = t2.parent_id
INNER JOIN tree ON (t1.lo_id = tree.child)
WHERE t1.parent_id = tree.lo_id
)
SELECT
child as lo_id, path, json
FROM tree
WHERE child IS NOT NULL ORDER BY path
因为您无法填写最后一个递归结构中的JSON对象，您将需要一个用于保存全局变量的函数：
Since you cannot fill a final JSON object in a recursive structure, you'll need a function for holding a global variable:
CREATE OR REPLACE FUNCTION json_tree() RETURNS jsonb AS $$
DECLARE
_json_output jsonb;
_temprow record;
BEGIN
SELECT
jsonb_build_object('Name', lo_id, 'Subclasses', array_to_json(ARRAY[]::text[]))
INTO _json_output
FROM test
WHERE parent_id IS NULL;
FOR _temprow IN
-- <the query from above>
LOOP
SELECT jsonb_insert(_json_output, _temprow.path, _temprow.json) INTO _json_output;
END LOOP;
RETURN _json_output;
END;
$$ LANGUAGE plpgsql;
由于此解决方案很安静，因此请在此处进行进一步说明。
Because the solution is quiet similar as this one, please look HERE for further explanation.
这篇关于postgresql-用递归构建树并将其导出为json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
