How to map array of ints to a single concat string?

Question

I have these tables:

names
id | name
7  | 'a'
8  | 'b'
9  | 'c'

group_names
id | group_of_names
1  | '7'
2  | '9,8'
3  | '7,8,9'

how to build a select that returns their names instead, separated by semicolon, preserving the original order as in:

id | single_text
1  | 'a'
2  | 'c;b'
3  | 'a;b;c'

I managed to do

select g.id, string_to_array(g.group_of_names,',')::int[] from group_names g;
id | string_to_array
1  | {7}
2  | {9,8}
3  | {7,8,9}

but i don't know how to, returning several arrays, for each of them, concatenate texts based on their ids

klin · Accepted Answer · 2021-10-28 18:54:11Z

1

If the order of resulting strings is irrelevant:

select g.id, string_agg(n.name, ';')
from group_names g
join names n
    on n.id = any(string_to_array(g.group_of_names, ',')::int[])
group by g.id

otherwise:

select g.id, string_agg(n.name, ';' order by ord)
from names n
join (
    select id, elem, ord
    from group_names
    cross join regexp_split_to_table(group_of_names, ',') 
        with ordinality as arr(elem, ord)
    ) g
    on n.id = g.elem::int
group by g.id

Test it in db<>fiddle.

In Postgres 14+ you can use string_to_table() instead of regexp_split_to_table().

edited Oct 28, 2021 at 18:54

answered Oct 28, 2021 at 17:29

klin

123k15 gold badges241 silver badges263 bronze badges

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

Comments

nachospiu · Accepted Answer · 2021-10-28 17:13:43Z

0

You can try this way: Using the ANY operator to check if n.id value is in the array of group_of_names.

SELECT gn.id, string_agg(n.name, ';') AS single_text
FROM names n
INNER JOIN group_names gn ON n.id::text = ANY(string_to_array(gn.group_of_names, ','))
GROUP BY gn.id
ORDER BY gn.id;

Or this way: using your query and unnest() function to expand an array to a set of rows.

SELECT gn.id, string_agg(n.name, ';')
FROM names n
INNER JOIN (SELECT g.id, unnest(string_to_array(g.group_of_names, ',')::int[]) AS name_id
            FROM group_names g) AS gn ON n.id = gn.name_id
GROUP BY gn.id
ORDER BY gn.id;

answered Oct 28, 2021 at 17:13

nachospiu

2,0792 gold badges10 silver badges13 bronze badges

Comments

Edouard · Accepted Answer · 2021-10-28 17:25:50Z

0

SELECT g.id, string_agg(n.name,';') AS single_text
  FROM group_names AS g
 CROSS JOIN LATERAL regexp_split_to_table (g.group_of_names, ',') AS gn(element)
 INNER JOIN names AS n
    ON n.id :: text = gn.element
 GROUP BY g.id

answered Oct 28, 2021 at 17:25

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to map array of ints to a single concat string?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related