[SOLVED] Is recursive query an option for concatenating text for building a path

Is recursive query an option for concatenating text for building a path

Simplifying a bit. I have the following data structure:

| id       | name           | traversal_ids[] |
| -------- | -------------- |-----------------|
| 1        | container1     | {1}             |
| 2        | container2     | {2}             |
| 3        | subcontainer1  | {2, 3}          |
| 4        | container4     | {4}             |
| 5        | subsub         | {2 ,3 ,5}       |

and I'd like to transform this in the following:

| id       | name           | path                             |
|--------  |--------------  |----------------------------------|
| 1        | container1     | /container1                      |
| 2        | container2     | /container2                      |
| 3        | subcontainer1  | /container2/subcontainer1        |
| 4        | container4     | /container4                      |
| 5        | subsub         | /container2/subcontainer1/subsub |

I'd like as much as possible to give this to the database to execute, but unfortunately I lack the sql skills. If it's not possible I'll try to get this done inside the app.

Can this be achieved in SQL?

Thanks!

Solution

No need for recursion. We can unnest the array in a lateral join, bring the corresponding names with a join, then aggregate back:

select t.*, x.*
from mytable t
cross join lateral (
    select string_agg(t1.name, '/' order by x.ord) path
    from unnest(t. traversal_ids) with ordinality x(id, ord)
    inner join mytable t1 on t1.id = x.id
) x