



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 ?

PostgreSQL - Aggregate attributes from different columns recursively
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.
