Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

CREATE TABLE


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

INSERT 0 6


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;


uuid
name
children


8a70180b-3644-4b17-af5f-93cbe0090cce
Electronics
{{d9093660-241a-48f6-bf09-b6a8c6c7f12a,376ae1cb-425d-44d2-b19a-19b6f1e86314},{d9093660-241a-48f6-bf09-b6a8c6c7f12a,5d5f174a-5c8e-4d12-912f-8173e255e35a}}

f79f5fa0-6eaf-465b-9b14-e3b49c5ac9ef
Food
{{8aa95eda-7963-40ef-be44-076cdf06c5c1}}



SELECT 2

uuid	name	children
8a70180b-3644-4b17-af5f-93cbe0090cce	Electronics	{{d9093660-241a-48f6-bf09-b6a8c6c7f12a,376ae1cb-425d-44d2-b19a-19b6f1e86314},{d9093660-241a-48f6-bf09-b6a8c6c7f12a,5d5f174a-5c8e-4d12-912f-8173e255e35a}}
f79f5fa0-6eaf-465b-9b14-e3b49c5ac9ef	Food	{{8aa95eda-7963-40ef-be44-076cdf06c5c1}}