PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SELECTing an array from an array?

I’m getting an array of values as part of a query:

SELECT class_id, count(*) as num_in_class, ARRAY_AGG(DISTINCT major_id) major_ids
    FROM students GROUP BY class_id;

This gives me the number of distinct majors in each class. The id of the major isn’t super helpful though, I would like to know the name of the major:

SELECT major.name, ...
    ...
JOIN major on major.id = major_id

This naive solution doesn’t work as is because my major_ids are in an array. How can I complete this join and get the names of the majors into an array major_names of the same length as major_ids?

Tags:

2

Answers


  1. 0

    Do you just want something like this query?

    SELECT
    s.class_id,
    COUNT(*) AS students_in_class,
    ARRAY_AGG(DISTINCT m.id) AS distinct_major_ids,
    ARRAY_AGG(DISTINCT m.name) AS distinct_major_names
FROM students s
INNER JOIN major m ON m.id = s.major_id
GROUP BY s.class_id;
    Login or Signup to reply.
  2. 0

    Its basically the same that you are proposing with the join

    select class_id, count(*) as num_in_class, 
array_agg(distinct array[m.id::varchar, m.name]) majors
from students s 
inner join major m on m.id =s.major_id  
group by class_id;

    Fiddle to test

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search