Convert right side of join of many to many into array of objects

Question

I have 2 tables:

Table subjects: id, class_id, title, created_at, updated_at
Table marks: id, student_id, class_id, mark, created_at, updated_at, subject_id

I want to query subjects table and have it return all of its columns and also marks column, that is of type array (and the values in it are supposed to be objects) that has essentially the result of the query

select * from marks where marks.subject_id = subjects.id;

So a result row looks something like this:

 (5,3,"Subject 1","2022-01-25T12:05:11.000Z","2022-01-25T12:05:11.000Z"
, [{id:15, student_id:12, class_id:3, mark:5, created_at:"2022-01-23T12:05:11.000Z", updated_at:"2022-01-23T12:05:11.000Z", subject_id:5}
 , {id:23, student_id:12, class_id:3, mark:5, created_at:"2022-01-21T12:05:11.000Z", updated_at:"2022-01-21T12:05:11.000Z", subject_id:5}
  ])

The question is similar to Convert right side of join of many to many into array

Answer 1

The best solution depends on missing details. A plain correlated subquery is one of various options (especially good for small selections).
Using json_agg() to aggregate rows into an array of objects:

SELECT *, (SELECT json_agg(m.*) FROM marks m WHERE m.subject_id = s.id) AS marks
FROM   subjects s;

To use only a subset of columns, see:

• Select columns inside json_agg