Get top 5 students for each class in many-to-many relationship So I'm doing this in MySQL.
I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their columns.

students
student_id | name | email | GPA

classes
class_id | name | room

student_class_rel
student_id | class_id

I want to get a student_id of the 5 students for each class with the highest GPAs (along with the class info). How would I do this?
For example, this query gets 100 classes and then gets every student_id for all students in that class
SELECT *, 
    JSON_ARRAYAGG(student_id) 
FROM classes 
    INNER JOIN student_class_rel 
    USING(class_id) 
GROUP BY class_id; 
LIMIT 100

I want that query, expect the JSON_ARRAYAGG(student_id) only holds the ID to the top 5 students in terms of GPA for each class rather than every student.
Also, assume there could be upwards of millions of students and hundreds of thousands of classes and that there are around 50 to 1000 students per class. So performance is key for this query.
And I'm aware of hacky type solutions (like make multiple queries, once for each class) but so far those have been WAY too slow. I've spent too much time trying to figure this out, any help is greatly appreciated! (Also let me know if there's any additional info needed).
Student with GPAs is an aggregation
Class information is not an aggregation

Solution:

Use two queries
Query the GPAs (CTE and Window Function)
Query the class information

You will probably need two indices on student_id and class_id to boost the performance.
Example queries in MySQL 8.0.20:
WITH ordered_gpa AS(
    SELECT students.id,
        students.gpa, 
        student_class_rel.class_id,
        rank() over (partition by student_class_rel.class_id order by students.gpa DESC) gpa_rank
    FROM student_class_rel
    INNER JOIN students 
    ON student_class_rel.stu_id = students.id
)
SELECT * 
FROM ordered_gpa
WHERE gpa_rank &lt;= 5;

Here, we use Windows rather than GROUP BY, which is the PARTITION BY to split the rows.
SELECT class_id, name, room
FROM classes
WHERE class_id IN (
  SELECT DISTINCT class_id
  FROM student_class_rel
) tmp;

You could combine this query with the first one.