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

Question

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!

GMB · Accepted Answer · 2023-03-15 13:37:44Z

2

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

answered Mar 15, 2023 at 13:37

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SelVazi · Accepted Answer · 2023-03-15 14:29:47Z

0

Assuming the traversal ids are always with ascendant order, You can do it using a simple inner join with ANY operator as follows :

with cte as (
  select t2.id, t2.name, t1.name as sub_name, t1.id as sub_id
  from mytable t1
  inner join mytable t2 on t1.id = ANY(t2.traversal_ids)
)
select id, name, '/' || string_agg(sub_name, '/' order by sub_id) as path
from cte
group by id, name

Demo here

edited Mar 15, 2023 at 14:29

answered Mar 15, 2023 at 14:05

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

2 Comments

GMB Over a year ago

Yes, but this orders the array elements by id, while one would expect them to respect their order in the original array.

SelVazi Over a year ago

Correct, this is a working solution if the traversal_ids is always ascendant, thanks for the remark

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related