I have a table with data hierarchy
CREATE TABLE my_table(
object_id varchar,
parent_id varchar
);
INSERT INTO my_table(object_id , parent_id)
VALUES
('1', '0'),
('2', '0'),
('3', '1'),
('4', '1'),
('5', '1'),
('6', '3'),
('7', '2'),
('8', '2');
| object_id | parent_id |
|---|---|
| 1 | 0 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 3 |
| 7 | 2 |
| 8 | 2 |
I need to tranformation to
| object_id | parent_id |
|---|---|
| {1,3,4,5,6},{2,7,8} | 0 |
I think there is no way to do without an intermediate table
So far, I have only found this request, but I don’t know which way to dig further
SELECT parent_id, array_to_string(array_agg(distinct(object_id)), ' , ', '') AS object_id
FROM my_table
GROUP BY parent_id;
Thx
