id_start | id_end |
---|---|
1 | 2 |
2 | 3 |
3 | 4 |
I want to collect elements who are "connected" like [1, 2, 3, 4]
. (in an array for example)
I tried a recursive query like:
WITH RECURSIVE q_rec AS (
SELECT id_start,
id_end
FROM my_table
UNION
SELECT t.id_start
t.id_end
FROM my_table t
INNER JOIN q_rec r ON r.id_start = t.id_end
)
SELECT *
FROM q_rec;
But how can I aggregate them in an array despite they are not in the same column ?
Another approach gathers all ids in the recursive query, as a table. Then applies aggregation.
WITH RECURSIVE cte AS (
SELECT id_start AS first_id,
id_start AS id
FROM tab t1
WHERE NOT EXISTS(SELECT 1 FROM tab t2 WHERE t1.id_start = t2.id_end)
UNION ALL
SELECT cte.first_id,
tab.id_end
FROM cte
INNER JOIN tab
ON cte.id = tab.id_start
)
SELECT first_id,
ARRAY_AGG(id) AS ids
FROM cte
GROUP BY first_id
Output:
first_id | ids |
---|---|
1 | [1,2,3,4] |
Check the demo here.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments