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

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
Mihael
  • 3
  • 2

1 Answers1

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:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • I just tried this and it works great, I'm guessing it's speed is optimized? – Mihael Jan 27 '22 at 18:18
  • @Mihael: Depends on whether you select most or few rows. But it won't get much faster. – Erwin Brandstetter Jan 27 '22 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 '22 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 '22 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: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=ad9a3a7c0f49e399d4efc0f933e83a45 – Erwin Brandstetter Jan 30 '22 at 17:31