I have a table pr with two columns:

stg source id, parent id

125, 124
126, 125
127, 125
128, 127 

what I want a final result as:

parent id, stg_source_ids
124, [125, 126, 127, 128] 

as my final row in another view.

I am trying to solve this using recursive cte

    SELECT pr.parent_id, pr.stg_source_id, ARRAY_CONSTRUCT(pr.stg_source_id) AS stg_source_ids
    FROM pr
    WHERE NOT EXISTS (
        SELECT 1 FROM pr as pr2 WHERE pr2.stg_source_id = pr.parent_id
    )
    
    UNION ALL
    
    SELECT pr.parent_id, pr.stg_source_id, array_append(p.stg_source_ids, pr.stg_source_id)
    FROM pr
    JOIN ParentChildHierarchy p ON pr.parent_id = p.stg_source_id

this produce
results such as:

124 -- [125]
125 -- [125, 126]
125 -- [125, 127]
127  -- [127, 128]