Materialized Path im relationalen Modell

[castorp]

Du kannst innerhalb der CTE die besuchten Werte "aufsammeln" und dann am Ende nur die Datensätze ausgeben die das "Ende" des Baums markieren:

create table artikel(id integer, name text, parent integer);

insert into artikel(id, name, parent)
values
(1, 'Schränke', null),
(2, 'Sonderangebote', 1),
(6, 'Top-Angebote', 1),
(9, 'Holzschrank', 2),
(10, 'Holzschrank', 6);

with recursive cte as (
  select *, name as start_name, array[]::int[] as parent_ids, array[]::text[] as parent_names
  from artikel
  where name = 'Holzschrank'
  union all
  select c.*, p.start_name, p.parent_ids||c.id, p.parent_names||c.name
  from artikel c
    join cte p on c.id = p.parent
)
select start_name, parent_ids, parent_names
from cte
where parent is null
;

Das liefert:

start_name  | parent_ids | parent_names            
------------+------------+--------------------------
Holzschrank | {6,1}      | {Top-Angebote,Schränke} 
Holzschrank | {2,1}      | {Sonderangebote,Schränke}

Ich habe "native" Arrays verwendet, aber das geht natürlich auch mit JSONB Arrays.