Create string from array

Question

I have a table in PostgreSQL that contains:

id   name   arrayofparents
1     First
2     Second      {1}
3     Second_Sec  {1,2}
4     Third       {1,2,3}
5     Second_A    {1}
6     Other
7     Trash       {6}

arrayofparents is of type integer[] it contains a list of the parents records for that row with the correct order.

id=4 parents are: First then Second then Second_sec

How do I write a query that for any given id it will generate a string of it's parents name?

for example:

id=3: First->Second.

id=4: First->Second->Second_sec.

id=7: Other.

Edit: if possible I prefer the requested id name will always appear. id=3: First->Second->Second_sec.

id=4: First->Second->Second_sec->Third.

id=7: Other->Trash.

id=6: Other.

This is a bad DB design as (to my knowledge) you cannot enforce referential integrity with an array type (see dba.stackexchange.com/questions/60132/… ). I recommend changing your design to use a "join table", then you can use a recursive CTE to solve your problem. — Dai
– Dai, Commented Dec 6, 2015 at 7:50
The field arrayofparents is used for other porpuses.. mostly to with Any query.... I just wanted to take advantage of it. I can not change the table. — avi
– avi, Commented Dec 6, 2015 at 8:28

Mauri · Accepted Answer · 2015-12-06 16:02:04Z

2

you can combine muliple operations like generate_subscripts and array to get the result:

with mtab as (
      SELECT id, name, array_append(arrayofparents,id) as arrayofparents,
      generate_subscripts(array_append(arrayofparents, id), 1) AS p_id FROM tab where id=2
)
select distinct array_to_string(
  array(
    select tab.name from tab join mtab t on tab.id=t.arrayofparents[t.p_id]
  ), '->'
) ;

live example Sqlfiddle

or use outer join combined with any:

SELECT coalesce(string_agg(p.name, '->') || '->' || t.name, t.name) AS parentnames
FROM tab AS t
  LEFT JOIN tab AS p ON p.id = ANY(t.arrayofparents)
 where t.id =7 
GROUP BY t.id, t.name

live example Sqlfiddle

edited Dec 6, 2015 at 16:02

answered Dec 6, 2015 at 9:04

Mauri

1,2851 gold badge14 silver badges21 bronze badges

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

8 Comments

avi Over a year ago

it doesn't work... check it on id=7 it gives First.... it should give Other

avi Over a year ago

Question.. if I also want to add the current id name ? for example id=7 : Other -> Trash. instad of Other. meaning add the current id name at the end every time. is it doable?

avi Over a year ago

it's working but it doesn't make sure that Trash is at the end. the union cause the order to be massed up. it can generate Trash-> Other.

Mauri Over a year ago

can you try the second option?

Mauri Over a year ago

i modified the second version (the new first version) and it can now return all items

|

Community · Accepted Answer · 2017-05-23 11:52:20Z

1

Each of these queries work for a single id as well as for the whole table.
And you can return just the path / the full path or all other columns as well.

SELECT t.*, concat_ws('->', t1.path, t.name) AS full_path
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT string_agg(t1.name, '->' ORDER  BY i) AS path
   FROM   generate_subscripts(t.arrayofparents, 1) i
   JOIN   tbl t1 ON t1.id = t.arrayofparents[i]   
   ) t1 ON true
WHERE  t.id = 4;  -- optional

Alternatively, you could move the ORDER BY to a subquery - may be a bit faster:

SELECT concat_ws('->', t1.path, t.name) AS full_path
FROM   tbl t, LATERAL (
   SELECT string_agg(t1.name, '->') AS path
   FROM  (
      SELECT t1.name
      FROM   generate_subscripts(t.arrayofparents, 1) i
      JOIN   tbl t1 ON t1.id = t.arrayofparents[i]
      ORDER  BY i
      ) t1
   ) t1
WHERE  t.id = 4;  -- optional

Since the aggregation happens in the LATERAL subquery we don't need a GROUP BY step in the outer query.

We also don't need LEFT JOIN LATERAL ... ON true to retain all rows where arrayofparents is NULL or empty, because the LATERAL subquery always returns a row due to the aggregate function.
LATERAL requires Postgres 9.3.

What is the difference between LATERAL and a subquery in PostgreSQL?

Use concat_ws() to ignore possible NULL values in the concatenation.

SQL Fiddle.

WITH OTDINALITY makes it a bit simpler and faster in Postgres 9.4:

SELECT t.*, concat_ws('->', t1.path, t.name) AS full_path
FROM   tbl t, LATERAL (
   SELECT string_agg(t1.name, '->' ORDER BY ord) AS path
   FROM   unnest(t.arrayofparents) WITH ORDINALITY a(id,ord)
   JOIN   tbl t1 USING (id)  
   ) t1
WHERE  t.id = 4;

Detailed explanation:

PostgreSQL unnest() with element number

Variant with UNION ALL for pg 9.3

SELECT t1.full_path
FROM   tbl t, LATERAL (
   SELECT string_agg(name, '->') AS full_path
   FROM  (
      (
      SELECT name
      FROM   generate_subscripts(t.arrayofparents, 1) i
      JOIN   tbl ON id = t.arrayofparents[i]
      ORDER  BY i
      )
      UNION ALL SELECT t.name
      ) t1
   ) t1
WHERE  t.id = 4;

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Dec 6, 2015 at 13:12

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

3 Comments

avi Over a year ago

Can you show how to make sure that the id will always appear? see my question edit.

avi Over a year ago

unnest doesn't keep the order... it messes up the records.. it can show Second->First->Second_sec instad of First->Second->Second_sec

Erwin Brandstetter Over a year ago

@avi: Right, well mostly. Just like I explained in detail in the linked answer. unnest() itself reains the original order, but the join messes with it. I fixed that. Plus, the join condition was also missing. And I added the name of the current row according to your Q update. Each of these queries should now be faster than the currently accepted answer (besides being correct). Test with EXPLAIN ANALYZE.

Eelke · Accepted Answer · 2015-12-06 08:41:01Z

0

If you only want direct parents (not grandparents) then something like this should work:

SELECT c.id, c.name, string_agg(p.name, '->') AS parentnames
FROM yourtable AS c
  LEFT JOIN yourtable AS p ON p.id = ANY c.parents
GROUP BY c.id, c.name

answered Dec 6, 2015 at 8:41

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

1 Comment

avi Over a year ago

it says: ERROR: syntax error at or near "c". I need all parents from what is written in arrayofparents

Collectives™ on Stack Overflow

Create string from array

3 Answers 3

8 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related