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
isnull
, 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.
Advertisement
Answer
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
link | link_path | info :--- | :-------- | :---- a | b | asdjh b | c | uiqwd c | null | isado