MySql query two tables and add second as a property to the rows from the first table

Question

I am making a teacher interface, and i have four tables Professors, Students, All Assignments and Student Assignments (all assignments for individual students)

I want to send the professor all the students and all their assignments, but some have multiple assignments and some have none. I query all the student info from the first table and now I want every single row (student) to have a property assignments which is and array of all the assignments from the second table.

Students

Name Class Grade
s1   1     A
s2   2     B

Assignments

Name Student Status 
a1   s1      done
a2   s1      done

And I want to get:

[
{Name:s1, Class: 1, Grade: A, Assignments:[{Name:a1, Status: done}, {Name:a2, Status: done}]},
{Name:s2, Class: 2, Grade: B, Assignments:[]}
] 

I am using nodejs (mysql module) for querying the database.

---

UPDATE (from the comment)

version 10.4.11-MariaDB – pro_programmer

Answer 1

You could use JSON aggregation:

select s.*, 
    (
        select json_arrayagg(json_object('Name', a.name, 'Status', a.status)) 
        from assignments a 
        where a.student = s.name
    ) assignments
from students s

If MariaDB 10.4, where json_arrayagg() is not avaible (but json_object() is), one workaround uses group_concat():

select s.*, 
    (
        select concat('[', group_concat(json_object('Name', a.name, 'Status', a.status), ']') 
        from assignments a 
        where a.student = s.name
    ) assignments
from students s

Answer 2

-- MySQL 8+ needed
WITH cte AS ( SELECT Student Name, JSON_ARRAYAGG(JSON_OBJECT('Name', Name, 
                                                             'Status', Status)) Assignments
              FROM Assignments
              GROUP BY Student )
SELECT JSON_OBJECT( 'Name', Students.Name,
                    'Class', Students.Class,
                    'Grade', Students.Grade,
                    'Assignments', COALESCE(cte.Assignments, JSON_ARRAY()) ) full_data
FROM Students
LEFT JOIN cte USING (Name);

or

-- MySQL 5.7.22 needed
SELECT JSON_OBJECT( 'Name', Students.Name,
                    'Class', Students.Class,
                    'Grade', Students.Grade,
                    'Assignments', COALESCE(cte.Assignments, JSON_ARRAY()) ) full_data
FROM Students
LEFT JOIN ( SELECT Student Name, JSON_ARRAYAGG(JSON_OBJECT('Name', Name, 
                                                           'Status', Status)) Assignments
            FROM Assignments
            GROUP BY Student ) cte USING (Name);

fiddle