Postgresql Select rows where column::text = array::text[]

Question

This is similar to Postgresql Select rows where column = array question

create table students (id int, name text);
insert into students values 
(1,'AA'),
(2,'BB'),
(3,'CC'),
(4,'DD');

create table classes (name text,students text[]);
insert into classes values
('CL-1','{2,4}'),
('YL-2','{2,1,4}'),
('CL-3','{2,3}'),
('BL-33','{2}'),
('CL-5','{1,3,4}'),
('CL-6','{4}');

How can I get the names of the students in each class?

select cl.name, 
       (select st.names 
        from student st 
        where st.id in cl.student) as student_names -- exp: AA,BB,CC
from class cl;

Answer 1

You can join the tables and re-aggregate the names that correspond the the ID's in your array:

select c.name as class_name,
       string_agg(s.name,',') as student_names
from classes c
        inner join students s
        on s.id::text=any(students)
group by c.name;
-- class_name | student_names
--------------+---------------
-- CL-5       | AA,CC,DD
-- YL-2       | AA,BB,DD
-- CL-6       | DD
-- BL-33      | BB
-- CL-1       | BB,DD
-- CL-3       | BB,CC

If you don't want to group by a ton of columns in classes, you can initially retrieve these lists in a CTE, then join that to classes:

with student_name_lists as 
( select c.name as class_name,
         string_agg(s.name,',') as student_names
  from classes c join students s
                 on s.id::text = any(students)
  group by c.name )
select c.*,
       sn.student_names
from classes c join student_name_lists sn
               on c.name=sn.class_name;

online demo