Postgres рекурсивный запрос с row_to_json
У меня есть таблица в Postgres 9.3.5, которая выглядит следующим образом:
CREATE TABLE customer_area_node
(
id bigserial NOT NULL,
customer_id integer NOT NULL,
parent_id bigint,
name text,
description text,
CONSTRAINT customer_area_node_pkey PRIMARY KEY (id)
)
Я запрашиваю с:
WITH RECURSIVE c AS (
SELECT *, 0 as level, name as path FROM customer_area_node WHERE customer_id = 2 and parent_id is null
UNION ALL
SELECT customer_area_node.*,
c.level + 1 as level,
c.path || '/' || customer_area_node.name as path
FROM customer_area_node
join c ON customer_area_node.parent_id = c.id
)
SELECT * FROM c ORDER BY path;
похоже, это работает для построения путей, таких как building1/floor1/room1, building1/floor1/room2 и т. д.
То, что я хотел бы сделать, это легко превратить это в любой json, который представляет древовидную структуру, что мне сказали, что я могу сделать с row_to_json.
В качестве разумной альтернативы, любым другим способом я могу отформатировать данные в более эффективный механизм, чтобы я мог легко превратить их в фактическую древовидную структуру, не имея тонны string.splits на /.
Есть ли достаточно простой способ сделать это с row_to_json?
3 ответа
Извините за очень поздний ответ, но я думаю, что нашел элегантное решение, которое могло бы стать приемлемым ответом на этот вопрос.
Основываясь на удивительном "маленьком хакере", найденном @pozs, я нашел решение, которое:
- решает ситуацию с "жуликами" с помощью очень небольшого кода (используя
NOT EXISTSсказуемое) - избегает всего расчета уровня / условия условия
WITH RECURSIVE customer_area_tree("id", "customer_id", "parent_id", "name", "description", "children") AS (
-- tree leaves (no matching children)
SELECT c.*, json '[]'
FROM customer_area_node c
WHERE NOT EXISTS(SELECT * FROM customer_area_node AS hypothetic_child WHERE hypothetic_child.parent_id = c.id)
UNION ALL
-- pozs's awesome "little hack"
SELECT (parent).*, json_agg(child) AS "children"
FROM (
SELECT parent, child
FROM customer_area_tree AS child
JOIN customer_area_node parent ON parent.id = child.parent_id
) branch
GROUP BY branch.parent
)
SELECT json_agg(t)
FROM customer_area_tree t
LEFT JOIN customer_area_node AS hypothetic_parent ON(hypothetic_parent.id = t.parent_id)
WHERE hypothetic_parent.id IS NULL
Обновление:
Протестировано с очень простыми данными, это работает, но, как указал posz в комментарии, с его образцами данных, некоторые узлы-листы-мошенники забыты. Но я обнаружил, что с еще более сложными данными предыдущий ответ также не работает, потому что перехватываются только листовые узлы-изгои, имеющие общего предка с листовыми узлами "максимального уровня" (когда "1.2.5.8" не существует "," 1.2.4 "и" 1.2.5 "отсутствуют, поскольку они не имеют общего предка с каким-либо листовым узлом" максимального уровня ").
Итак, вот новое предложение, смешивая работу Posz с моей, извлекая NOT EXISTS подзапрос и сделать его внутренним UNION, используя UNION Способности к дедупликации (используя возможности сравнения JSONB):
<!-- language: sql -->
WITH RECURSIVE
c_with_level AS (
SELECT *, 0 as lvl
FROM customer_area_node
WHERE parent_id IS NULL
UNION ALL
SELECT child.*, parent.lvl + 1
FROM customer_area_node child
JOIN c_with_level parent ON parent.id = child.parent_id
),
maxlvl AS (
SELECT max(lvl) maxlvl FROM c_with_level
),
c_tree AS (
SELECT c_with_level.*, jsonb '[]' children
FROM c_with_level, maxlvl
WHERE lvl = maxlvl
UNION
(
SELECT (branch_parent).*, jsonb_agg(branch_child)
FROM (
SELECT branch_parent, branch_child
FROM c_with_level branch_parent
JOIN c_tree branch_child ON branch_child.parent_id = branch_parent.id
) branch
GROUP BY branch.branch_parent
UNION
SELECT c.*, jsonb '[]' children
FROM c_with_level c
WHERE NOT EXISTS (SELECT 1 FROM c_with_level hypothetical_child WHERE hypothetical_child.parent_id = c.id)
)
)
SELECT jsonb_pretty(row_to_json(c_tree)::jsonb)
FROM c_tree
WHERE lvl = 0;
Протестировано на http://rextester.com/SMM38494;)
Вы не можете сделать это с обычным рекурсивным CTE, потому что почти невозможно установить значение json глубоко в его иерархии. Но вы можете сделать это в обратном порядке: построить дерево, начиная с его листьев, до его корня:
-- calculate node levels
WITH RECURSIVE c AS (
SELECT *, 0 as lvl
FROM customer_area_node
-- use parameters here, to select the root first
WHERE customer_id = 2 AND parent_id IS NULL
UNION ALL
SELECT customer_area_node.*, c.lvl + 1 as lvl
FROM customer_area_node
JOIN c ON customer_area_node.parent_id = c.id
),
-- select max level
maxlvl AS (
SELECT max(lvl) maxlvl FROM c
),
-- accumulate children
j AS (
SELECT c.*, json '[]' children -- at max level, there are only leaves
FROM c, maxlvl
WHERE lvl = maxlvl
UNION ALL
-- a little hack, because PostgreSQL doesn't like aggregated recursive terms
SELECT (c).*, array_to_json(array_agg(j)) children
FROM (
SELECT c, j
FROM j
JOIN c ON j.parent_id = c.id
) v
GROUP BY v.c
)
-- select only root
SELECT row_to_json(j) json_tree
FROM j
WHERE lvl = 0;
И это будет работать даже с PostgreSQL 9.2+
Обновление: вариант, который должен также обрабатывать жульнические конечные узлы (которые расположены с уровнем от 1 до максимального уровня):
WITH RECURSIVE c AS (
SELECT *, 0 as lvl
FROM customer_area_node
WHERE customer_id = 1 AND parent_id IS NULL
UNION ALL
SELECT customer_area_node.*, c.lvl + 1
FROM customer_area_node
JOIN c ON customer_area_node.parent_id = c.id
),
maxlvl AS (
SELECT max(lvl) maxlvl FROM c
),
j AS (
SELECT c.*, json '[]' children
FROM c, maxlvl
WHERE lvl = maxlvl
UNION ALL
SELECT (c).*, array_to_json(array_agg(j) || array(SELECT r
FROM (SELECT l.*, json '[]' children
FROM c l, maxlvl
WHERE l.parent_id = (c).id
AND l.lvl < maxlvl
AND NOT EXISTS (SELECT 1
FROM c lp
WHERE lp.parent_id = l.id)) r)) children
FROM (SELECT c, j
FROM c
JOIN j ON j.parent_id = c.id) v
GROUP BY v.c
)
SELECT row_to_json(j) json_tree
FROM j
WHERE lvl = 0;
Это должно работать и на PostgreSQL 9.2+, однако я не могу это проверить. (Я могу только проверить на 9.5+ прямо сейчас).
Эти решения могут обрабатывать любой столбец в любой иерархической таблице, но всегда будут добавлять int набранный lvl Свойство JSON для их вывода.
Немного дальше разработал ответ поз, чтобы получить рекурсивные листы со своими поддеревьями. Таким образом, этот ответ действительно возвращает полное дерево.
CREATE OR REPLACE FUNCTION pg_temp.getTree(bigint)
RETURNS TABLE(
id bigint,
customer_id integer,
parent_id bigint,
name text,
description text,
children json
)
AS $$
WITH RECURSIVE relations AS (
SELECT
can.id,
can.customer_id,
can.parent_id,
can.name,
can.description,
0 AS depth
FROM customer_area_node can
WHERE can.id = $1
UNION ALL
SELECT
can.id,
can.customer_id,
can.parent_id,
can.name,
can.description,
relations.depth + 1
FROM customer_area_node can
JOIN relations ON can.parent_id = relations.id AND can.id != can.parent_id
),
maxdepth AS (
SELECT max(depth) maxdepth FROM relations
),
rootTree as (
SELECT r.* FROM
relations r, maxdepth
WHERE depth = maxdepth
UNION ALL
SELECT r.* FROM
relations r, rootTree
WHERE r.id = rootTree.parent_id AND rootTree.id != rootTree.parent_id
),
mainTree AS (
SELECT
c.id,
c.customer_id,
c.parent_id,
c.name,
c.description,
c.depth,
json_build_array() children
FROM relations c, maxdepth
WHERE c.depth = maxdepth
UNION ALL
SELECT
(relations).*,
array_to_json(
array_agg(mainTree)
||
array(
SELECT t
FROM (
SELECT
l.*,
json_build_array() children
FROM relations l, maxdepth
WHERE l.parent_id = (relations).id
AND l.depth < maxdepth
AND l.id NOT IN (
SELECT id FROM rootTree
)
) r
JOIN pg_temp.getTree(r.id) t
ON r.id = t.id
))
children
FROM (
SELECT relations, mainTree
FROM relations
JOIN mainTree
ON (
mainTree.parent_id = relations.id
AND mainTree.parent_id != mainTree.id
)
) v
GROUP BY v.relations
)
SELECT
id,
customer_id,
parent_id,
name,
description,
children
FROM mainTree WHERE id = $1
$$
LANGUAGE SQL;
SELECT *
FROM
customer_area_node can
JOIN pg_temp.getTree(can.id) t ON t.id = can.id
WHERE can.parent_id IS NULL;