I reviewed many solutions and did not find a suitable example.

I have a certain selection

CREATE TABLE category
(
    uuid uuid not null primary key,
    name character varying(32),
    parent_uuid uuid references category(uuid),
);

INSERT INTO category
(uuid, name, parent_uuid) 
VALUES 
('8a70180b-3644-4b17-af5f-93cbe0090cce', 'Electronics', null),
('d9093660-241a-48f6-bf09-b6a8c6c7f12a', 'Microcontrollers', '8a70180b-3644-4b17-af5f-93cbe0090cce'),
('376ae1cb-425d-44d2-b19a-19b6f1e86314', 'Arduino, 'd9093660-241a-48f6-bf09-b6a8c6c7f12a'),
('5d5f174a-5c8e-4d12-912f-8173e255e35a', 'ESP8266', 'd9093660-241a-48f6-bf09-b6a8c6c7f12a'),
('f79f5fa0-6eaf-465b-9b14-e3b49c5ac9ef', 'Food', null),
('8aa95eda-7963-40ef-be44-076cdf06c5c1', 'Meat', 'f79f5fa0-6eaf-465b-9b14-e3b49c5ac9ef');

Ultimately I want to build a tree

1. Electronics
   • Microcontrollers
     • Arduino
     • ESP8266
2. Food
   • Meat

I managed to make a selection of parents

WITH RECURSIVE nodes(uuid, name, parents) AS (
    SELECT
        uuid,
        name,
        ARRAY[]::uuid[]
    FROM category
    WHERE parent_uuid IS null

    UNION ALL

    SELECT
        category.uuid,
        category.name,
        parents || nodes.uuid
    FROM category
    JOIN nodes ON nodes.uuid = category.parent_uuid
)

SELECT * FROM nodes;

result:

However, this is not ideal for me. I would prefer this

I don't quite understand how to do it. Could you help me please?

CodePudding user response：

Keeping the base/root uuid & name in the recursive loop can help for the aggregation.

The solution below uses an EXISTS to know when the recursion ended.

WITH RECURSIVE RCTE_NODES AS (
  SELECT
    uuid
  , name
  , uuid as root_uuid
  , name as root_name
  , 1 as lvl
  , ARRAY[]::uuid[] as children
  , true as has_next
  FROM category
  WHERE parent_uuid IS null

  UNION ALL

  SELECT
    cat.uuid
  , cat.name
  , cte.root_uuid
  , cte.root_name
  , cte.lvl 1
  , cte.children || cat.uuid
  , (exists(select 1 from category cat2 where cat2.parent_uuid = cat.uuid))
  FROM RCTE_NODES cte
  JOIN category cat
    ON cat.parent_uuid = cte.uuid
)
SELECT root_uuid as uuid, root_name as name
, array_agg(children) as children
FROM RCTE_NODES
WHERE has_next = false
GROUP BY root_uuid, root_name
ORDER BY root_uuid;

Test on db<>fiddle here