Construct nested object graph from SQL hasmany relationship

Question

Example: I have some articles and comments and I want to get something like this:

[{
   title: "Article 1",
   content: "Super long article goes here",
   comments: [
      { author: "Troll", message: "You suck, Sir!" }, 
      { author: "SpamBot", message: "http://superawesomething.com/"}
   ]
},{
   title: "Article 2",
   content: "Another long article goes here",
   comments: [ ... ]
}]

Right now I see two solutions:

1. Get the articles first, then the comments in a second query with some IN condition and finally add the comments to the respective articles.
2. Good old joins. For one I will still have to fiddle around with the data a lot to get into the structure I want. But beyond that I'm a little concerned since payload like articles.content will be transmitted for every comment - unless there is a way to do the join I am not aware of.

I'm hoping that my SQL-illiteracy makes me miss the simple solution.

Answer 1

You can do this, using aggregates and/or subqueries. Something like:

select title, content, json_agg(comments.author, comments.message) as comments
from articles 
join comments on articles.article_id = comments.article_id
group by article_id;

If you need this aggregated into one string/json/something - just wrap it into another aggregate query like this:

select json_agg(sub)
from (
  select title, content, json_agg(comments.author, comments.message) as comments
  from articles 
  join comments on articles.article_id = comments.article_id
  group by article_id) sub;

This is a Postgres query. Have no expirience with Mysql.