select linked rows in the same table

Question

I'm creating a branching dialog game, and used a dialog tool that outputs JSON with a link and a link_path to connect dialogs together. I've parsed and inserted this structure in PostgreSQL.

I want to query a subset of rows, let's say starting with row 1, and follow the link_path until the link_path is null. Successive rows may be out of order.

For example, in the table below,

• starting with row 1, I find row with link_path = b,
• this gives me row 3, I find row with link_path = c,
• this gives me row 4, row 4's link_path is null, so we return this set: [row 1, row 3, row 4]

--

link     link_path    info
--------------------------
a          b          asdjh
w          y          akhaq
b          c          uiqwd
c                     isado
y          z          qwiuu
z                     nzabo

In PostgreSQL, how can I select rows like this without creating a loop of queries? My goal is performance.

Answer 1

You can use a recursive query:

with recursive cte as (
    select t.* from mytable t where link = 'a'
    union all
    select t.*
    from cte c
    inner join mytable t on t.link = c.link_path
)
select * from cte

Demo on DB Fiddle:

link | link_path | info 
:--- | :-------- | :----
a    | b         | asdjh
b    | c         | uiqwd
c    | null      | isado