POSTGRESQL Recursive LEFT JOIN on Array

Question

I have the following table with the parent_path array:

Id   | Account Name | parent_path
1      A              {1}
2      B              {2,1}
3      C              {3,2,1}
4      D              {4,3,2,1}

What I'm looking to do is to have a recursive left join in order to create 1 column per item in the parent_path array

Id   | Account Name | parent_path | parent_name1 | parent_name2 | parent_name3
1      A              NULL          NULL           NULL           NULL
2      B              {1}           A              NULL           NULL  
3      C              {2,1}         B              A              NULL
4      D              {3,2,1}       C              B              A

Thanks!

The resulting table cannot depend on data (like in your case). Postgresql does not support this AFAIK. Unless you know a priori the max number of columns. Still, what a waste of space. — freakish
– freakish, Commented Aug 10, 2020 at 13:17
As said, you can't have a dynamic number of columns in postgresql. You could create a single column containing the parent names in an array (basically a map(id -> account_name) on the parent_path array), or limit the number of parent_name_x columns. — Marth
– Marth, Commented Aug 10, 2020 at 13:31
@Marth if I know there's a maximum of 8 items in the parent_path, how would I do it? — GabPiche
– GabPiche, Commented Aug 10, 2020 at 13:33

Mike Organek · Accepted Answer · 2020-08-10 14:12:08Z

This is an abuse of SQL, but here goes:

 with get_names as (
  select h.id, h.account_name, h.parent_path, array_agg(h2.account_name order by p.rn) as name_path
    from hier h
         cross join lateral unnest(h.parent_path) with ordinality as p(path_id, rn)
         join hier h2 on h2.id = p.path_id
   group by h.id, h.account_name, h.parent_path
)
select id, account_name, parent_path, 
       name_path[2] as parent_name1,
       name_path[3] as parent_name2,
       name_path[4] as parent_name3,
       name_path[5] as parent_name4,
       name_path[6] as parent_name5,
       name_path[7] as parent_name6,
       name_path[8] as parent_name7,
       name_path[9] as parent_name8
  from get_names;

 id | account_name | parent_path | parent_name1 | parent_name2 | parent_name3 | parent_name4 | parent_name5 | parent_name6 | parent_name7 | parent_name8 
----+--------------+-------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------+--------------
  1 | A            | {1}         |              |              |              |              |              |              |              | 
  2 | B            | {2,1}       | A            |              |              |              |              |              |              | 
  3 | C            | {3,2,1}     | B            | A            |              |              |              |              |              | 
  4 | D            | {4,3,2,1}   | C            | B            | A            |              |              |              |              | 
(4 rows)

Ruben Helsloot · Accepted Answer · 2020-08-10 14:23:42Z

There is a cleaner solution, using PostgreSQL intarray, instead. It works best for small(ish) tables, since it's not optimised for performance:

CREATE EXTENSION intarray;

SELECT id,
       name,
       path - id as parents,
       (SELECT name FROM hierarchy h2 WHERE h2.id = (h.path - h.id)[1]) as parent_1,
       (SELECT name FROM hierarchy h2 WHERE h2.id = (h.path - h.id)[2]) as parent_2,
       (SELECT name FROM hierarchy h2 WHERE h2.id = (h.path - h.id)[3]) as parent_3,
       (SELECT name FROM hierarchy h2 WHERE h2.id = (h.path - h.id)[4]) as parent_4,
       (SELECT name FROM hierarchy h2 WHERE h2.id = (h.path - h.id)[5]) as parent_5,
       (SELECT name FROM hierarchy h2 WHERE h2.id = (h.path - h.id)[6]) as parent_6,
       (SELECT name FROM hierarchy h2 WHERE h2.id = (h.path - h.id)[7]) as parent_7
FROM hierarchy h

In my code, it produced the following (truncated) output:

+--+-----------+-------+--------+--------+--------+
|id|name       |parents|parent_1|parent_2|parent_3|
+--+-----------+-------+--------+--------+--------+
|1 |Europe     |       |NULL    |NULL    |NULL    |
|2 |Germany    |{1}    |Europe  |NULL    |NULL    |
|4 |Netherlands|{1}    |Europe  |NULL    |NULL    |
|7 |Africa     |       |NULL    |NULL    |NULL    |
|10|France     |{1}    |Europe  |NULL    |NULL    |
|12|America    |       |NULL    |NULL    |NULL    |
|17|Finland    |{1}    |Europe  |NULL    |NULL    |
|3 |Berlin     |{1,2}  |Europe  |Germany |NULL    |
+--+-----------+-------+--------+--------+--------+

Collectives™ on Stack Overflow

POSTGRESQL Recursive LEFT JOIN on Array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related