Из несколько строк иерархии в одну строку

Question

У меня есть иерархическая таблица с данными

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');

Таблица 1 | object_id | parent_id | | --------- | --------- | | 1 | 0 | | 2 | 0 | | 3 | 1 | | 4 | 1 | | 5 | 1 | | 6 | 3 | | 7 | 2 | | 8 | 2 |

Эти данные мне нужно трансформировать в такой результат: Таблица2

object_id	parent_id
{1,3,4,5,6},{2,7,8}	0

Начал с текущего запроса, но пока не знаю куда дальше копать:

SELECT parent_id, array_to_string(array_agg(distinct(object_id)), ' , ', '') AS object_id
FROM my_table 
GROUP BY parent_id;

Предполагаю, что тут необходима промежуточная таблица

Всё это нужно, что бы посмотреть какие продукты купили пользователи.

В иерархической таблице есть список строк, который связан между собой через object_id и parent_id образуя иерархию, но и есть ИДи, у которых нету связей по parent_id в текущей таблицы - это есть парент (ид 0 таблица 1) с которой начинается связь. Далее после трансформации мне нужно получить результат Таблицы 2 для того, что бы его сджойнить с другой таблицей по ид 0.

Грубо говоря у меня есть таблица пользователей и есть таблица продуктов, где таблица продуктов может хранить в себе пакет продуктов(иерархию), допустим пакет "студенческий" это продукт, в который входит сыр, масло и т.д., этот пакет имеет object_id 1, а parent_id 0 (это ид пользователя), object_id 3 это сыр и т.д.

Answer 1

Попробуйте брутфорсом с примером отсюда https://stackoverflow.com/questions/39212832/how-to-aggregate-two-postgresql-columns-to-an-array-separated-by-brackets. Ситуация очень похожа, на мой взгляд. Вдруг поможет...

Answer 2

WITH RECURSIVE
cte AS (
  SELECT object_id, parent_id, object_id root_id
  FROM test
  WHERE parent_id = '0'
  UNION ALL
  SELECT test.object_id, test.parent_id, cte.root_id
  FROM cte
  JOIN test ON cte.object_id = test.parent_id
),
cte2 AS (
  SELECT '{' || ARRAY_TO_STRING(ARRAY_AGG(object_id), ',') || '}' object_id
  FROM cte
  GROUP BY root_id
)
SELECT ARRAY_TO_STRING(ARRAY_AGG(object_id), ',') object_id, 0 parent_id 
FROM cte2

https://dbfiddle.uk/MkaZiUzZ

БЛОГ НА HUSL

Из несколько строк иерархии в одну строку

Ответы (2 шт):