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

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

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

CodePudding user response：

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;

CodePudding user response：

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

CodePudding user response：

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.