0

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

1 Answer 1

2

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:

5
  • I just tried this and it works great, I'm guessing it's speed is optimized?
    – Mihael
    Jan 27, 2022 at 18:18
  • @Mihael: Depends on whether you select most or few rows. But it won't get much faster. Jan 27, 2022 at 18:24
  • Thanks so much @Erwin, just one more question, is it possible to do it nested way, so something like SELECT *, (SELECT json_agg(u.*, (SELECT json_agg(m.*) FROM marks m) AS marks) FROM students u) AS students FROM subjects s; I tried this, but it doesn't work
    – Mihael
    Jan 29, 2022 at 16:52
  • Using the link you provided I came up with this SELECT *, (SELECT json_agg(json_build_object('id', u.id, 'user_id', u.user_id, 'class_id', u.class_id, 'marks', (SELECT json_agg(m.*) FROM marks m))) FROM students u) AS students FROM subjects s; and it works, but I would like to not have to write every column name, because I do need all of them, but I'm fine with this but if there is a better option, that would be awesome. Thanks
    – Mihael
    Jan 29, 2022 at 17:36
  • @Mihael: I think your last query is incorrect (missing WHERE clause to link marks to students), and I think it can be simplified. But both is hard without an actual test case and the precise expected result. Crate a new question with the specifics, be sure to add your version of Postgres and a fiddle - random example: dbfiddle.uk/… Jan 30, 2022 at 17:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.