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