How to perform this recursive query to fetch child ids for each id?

user_8898525 0
Viewed 28

Consider the following table:

ID ParentID
1 null
2 1
3 2
4 3

I want the result as such:

1: [2,3,4]
2: [3,4]
3: [4]
4: null

(i.e) Since 1 is the parent of 2 which is the parent of 3 and so on...

I tried this query but it requires a value:


    WITH RECURSIVE a AS (
    SELECT 1 AS id # I need to get all values instead of a specific value
    UNION ALL
    SELECT b."ID"
    FROM "tblName" "b" JOIN "c" ON "c"."id" = "b"."ParentID"
    )
    SELECT * FROM c
recursive-query postgresql
1 Answers

Get all rows, recurse downward while keeping track of the level, then use array_agg() to consolidate and array_remove to remove the row's "self" id.

with recursive walk as (
  select id, parentid, id as start_id, 0 as level
    from tblname
  union all
  select c.id, c.parentid, p.start_id, p.level + 1 as level
    from walk p
         join tblname c on c.parentid = p.id
)
select start_id, array_remove(array_agg(id order by level), start_id) as children 
  from walk
 group by start_id;

The nullif() is there only to match your spec.

db<>fiddle here

user_138083190
Related