Recursive Categories

DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS product_categories;
DROP TABLE IF EXISTS products;
CREATE TEMP TABLE categories
  as
    select *
    from (
           VALUES
             (1 :: int, 'a' :: VARCHAR, null),
             (2 :: int, 'b' :: VARCHAR, null),
             (3 :: int, 'c' :: VARCHAR, null),
             (4 :: int, 'aa' :: VARCHAR, 1),
             (5 :: int, 'aa' :: VARCHAR, 1),
             (6 :: int, 'aaa' :: VARCHAR, 4),
             (7 :: int, 'bb' :: VARCHAR, 2),
             (8 :: int, 'bbb' :: VARCHAR, 7)
         ) as t (id, name, parent_id);
CREATE TEMP TABLE products
  as
    select *
    from (
           VALUES
             (1 :: int, 'a' :: VARCHAR),
             (2 :: int, 'b' :: VARCHAR),
             (3 :: int, 'c' :: VARCHAR)
         ) as t (id, name);
CREATE TEMP TABLE product_categories
  as
    select *
    from (
           VALUES
             (1 :: int, 3 :: int, 6 :: int),
             (2 :: int, 3 :: int, 6 :: int),
             (3 :: int, 1 :: int, 3 :: int)
         ) as t (id, product_id, category_id);
-- WITH RECURSIVE list_categories(id, name, childs) AS (
WITH RECURSIVE list_categories(id, name) AS (
  SELECT
    parent_category.id,
    parent_category.name
--     parent_category.name,
--     json_agg(child_categories) filter (where child_categories.id is not null) as childs
  from categories parent_category
    left outer join categories child_categories on child_categories.parent_id = parent_category.id
  where parent_category.parent_id isnull
  group by parent_category.id, parent_category.name
  UNION ALL
  SELECT
    child_categories.id,
    child_categories.name
--     child_categories.name,
--     array_agg(child_categories) as childs
  from categories child_categories
    inner join list_categories on child_categories.parent_id = list_categories.id
    left outer join categories child_child_categories on child_child_categories.parent_id = child_categories.id
  group by child_categories.id, child_categories.name
)
SELECT *
from list_categories;
WITH RECURSIVE list_categories(id, name, path) AS (
  SELECT
    parent_category.id,
    parent_category.name,
    parent_category.id || '/' as path
  from categories parent_category
    left outer join categories child_categories on child_categories.parent_id = parent_category.id
  where parent_category.parent_id isnull
  group by parent_category.id, parent_category.name
  UNION ALL
  SELECT
    child_categories.id,
    child_categories.name,
    list_categories.path || child_categories.id || '/'
  from categories child_categories
    inner join list_categories on child_categories.parent_id = list_categories.id
    left outer join categories child_child_categories on child_child_categories.parent_id = child_categories.id
)
SELECT *
from list_categories;
select ARRAY [] :: integer []
select '123' || '/'
SELECT
  parent_category.id,
  parent_category.name,
  json_agg(child_categories.*) as childs
from categories parent_category
  left outer join categories child_categories on child_categories.parent_id = parent_category.id
where parent_category.parent_id isnull
group by parent_category.id, parent_category.name