Create string from array

问题:

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.

回答1:

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

回答2:

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;

回答3:

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