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;

Zegarek · Accepted Answer · 2023-02-13 15:49:51Z

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

edited Feb 13, 2023 at 15:49

answered Feb 13, 2023 at 13:38

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

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

3 Comments

user881703 Over a year ago

thank you for the feedback but I have 50+ columns in the class table, SO that all need to go group by the statement?

Zegarek Over a year ago

It can, but it doesn't have to. You can run this in a CTE or a subquery to assign the list of student names to each class identified by its unique identifier, and join that to the rest of the columns.

Zegarek Over a year ago

@user881703 I've updated the answer and the demo.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related