I have the following table and data
CREATE TABLE relationships (a TEXT, b TEXT);
CREATE TABLE nodes(n TEXT);
INSERT INTO relationships(a, b) VALUES
('1', '2'),
('1', '3'),
('1', '4'),
('1', '5'),
('2', '6'),
('2', '7'),
('2', '8'),
('3', '9');
INSERT INTO nodes(n) VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10');
I want to output
n | children
1 | ['2', '3', '4', '5', '6', '7', '8', '9']
2 | ['6', '7', '8', '9']
3 | ['9']
4 | []
5 | []
6 | []
7 | []
8 | []
9 | []
10 | []
I am trying to use WITH RECURSIVE but is stuck on how to pass parameter into CTE
WITH RECURSIVE traverse(n) AS (
SELECT *
FROM relationships
WHERE a = n --- not sure how to pass data to here
UNION ALL
...
)
WITH basic_cte AS (
SELECT a1.n as n,
(SELECT COALESCE(json_agg(temp), '[]')
FROM (
(SELECT * FROM traverse(a1.a))
) as temp
) as children
FROM nodes as a1
)
SELECT *
FROM basic_cte;
Note: This ignores any empty children. You can add a left join like in @a_horse_with_no_name's answer to get that functionality.
You can't really pass a parameter into the CTE unless you veer off into stored procedures and whatnot. The CTE is a single table that needs to contain all the rows you might want to use from it.
Assuming a fairly nice graph (no duplicate edges, no cycles), code like the following ought to do what you're looking for.
WITH RECURSIVE descendants(parent, child) AS (
SELECT * FROM relationships
UNION
SELECT d.parent, r.b
FROM descendants d JOIN relationships r ON d.child=r.a
)
SELECT parent AS n, array_agg(child) AS children
FROM descendants
GROUP BY parent
To get a list of the children of all nodes, you need a left join to the nodes table
with recursive rels as (
select a,b, a as root
from relationships
union all
select c.*, r.root
from relationships c
join rels r on r.b = c.a
)
select n.n, array_agg(r.b) filter (where r.b is not null)
from nodes n
left join rels r on r.root = n.n
group by n.n
order by n.n;