I tried to find out the masterIds as Array. please find the below

create temp table location as
select 1 as id,'World' as name,null as main_id
union all
select 2,'Asia',1
union all
select 3,'Africa',1
union all
select 4,'India',2
union all
select 5,'Sri Lanka',2
union all
select 6,'Mumbai',4
union all
select 7,'South Africa',3

Table like

And My expected result mentioned below:

And My code is handling only one row but i need this for entire table. please find my code below:

 with recursive
    cte as (
    select * from location  where id=4
    union all
    select g.*  from cte join location g on g.id  = cte.main_id
    )
    select array_agg(id)  from cte l

CodePudding user response：

You seem to want an array of all ancestors (including id) for each id. Here is one attempt:

with recursive cte as (
    select id, name, main_id from location
    union all
    select cte.id, g.name, g.main_id  
    from cte join location g on g.id  = cte.main_id
)
select id, array[id] || array_agg(main_id)  
from cte l
where main_id is not null
group by id

See Fiddle

For each iteration in the recursive cte, we keep the id that we started from and traverse the tree upwards (i.e. add next ancestor to the set of ancestors). At the end we create one group for each id and aggregate all ancestors into the array.

Worth noting is that the set of ancestors is unordered, so you may end up with a "path" that is out of order. You can fix that by providing an order, here I use the level relative to the id:

 with recursive
    cte as (
    select id, name, main_id, 0 as lvl from location
    union all
    select cte.id, g.name, g.main_id, lvl 1  from cte join location g on g.id  = cte.main_id
    )
    select id, array[id] || array_agg(main_id order by lvl)
    from cte l
    where main_id is not null
    group by id