使用row_to_json的Postgres递归查询 [英] Postgres recursive query with row_to_json
问题描述
我在postgres 9.3.5中有一个表，看起来像这样:
I've got a table in postgres 9.3.5 that looks like this:
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之类的路径.
this seems to work to build paths like building1/floor1/room1, building1/floor1/room2, etc.
我想做的就是轻松地将其转换为表示可以用row_to_json执行的树形结构的json.
What I'd like to be able to do is easily turn that into either json that represents the tree structure which I've been told I can do with row_to_json.
作为一种合理的选择，我可以通过其他任何方式将数据格式化为更有效的机制，这样我实际上就可以轻松地将其转换为实际的树结构，而无需花费大量的字符串.在/上拆分.
As a reasonable alternative, any other way I can format the data to a more efficient mechanism such that I can actually easily turn it into an actual tree structure without having a ton of string.splits on /.
使用row_to_json是否有合理简便的方法?
Is there a reasonably easy way to do this with row_to_json?
推荐答案
您不能使用常规递归CTE来做到这一点，因为几乎不可能在其层次结构的深处设置json值.但是，您可以将其反转:从树的叶子开始直到其根部建立树:
You cannot do that with a usual recursive CTE, because it is almost impossible to set a json value deep in its hierarchy. But you can do it reversed: build up the tree starting from its leaves, until its root:
-- 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+上也可以使用
And this will work even with PostgreSQL 9.2+
更新:一种变体，也应该处理流氓叶子节点(位于1到 max-level 之间的级别):
Update: A variant, which should handle rogue leaf nodes too (which are located with a level between 1 and max-level):
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+上进行测试).
This should work too on PostgreSQL 9.2+, however, I cannot test that. (I can only test on 9.5+ right now).
这些解决方案可以处理任何层次结构表中的任何列，但始终将int类型的lvl JSON属性附加到其输出中.
These solutions can handle any column in any hierarchical table, but will always append an int typed lvl JSON property to their output.
这篇关于使用row_to_json的Postgres递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
