Construct Nested Object Graph From Sql Hasmany Relationship

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: [ { autho

Solution 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
groupby 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
  groupby article_id) sub;

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

Solution 2:

Here's a MySQL solution:

SELECT CONCAT( '[ { '
              ,GROUP_CONCAT( CONCAT( 'title: "', REPLACE( a.title, '"', '\"' ), '"'
                                    ,', contents: "', REPLACE( a.content, '"', '\"' ), '"'
                                    ,', comments: ', a.comments
                                   )
                             SEPARATOR ' }, { '
                           )
              ,' } ]'
             )
  FROM (SELECT a1.title
              ,a1.content
              ,CONCAT( '[ { '
                      ,GROUP_CONCAT( CONCAT( 'author: "', REPLACE( c.author, '"', '\"' ), '"'
                                            ,', message: "', REPLACE( c.message, '"', '\"' ), '"'
                                           )
                                     SEPARATOR ' }, { '
                                   )
                      ,' } ]'
                     ) as comments
          FROM articles a1
          LEFTOUTERJOIN comments c
            ON c.articleId = a1.articleId
         GROUPBY a1.title, a1.content
       ) a
;

This will need some tweaking as strings get large. Would probably be best to return one row per article:

SELECT a1.title
      ,a1.content
      ,CONCAT( '[ { '
              ,GROUP_CONCAT( CONCAT( 'author: "', REPLACE( c.author, '"', '\"' ), '"'
                                    ,', message: "', REPLACE( c.message, '"', '\"' ), '"'
                                   )
                             SEPARATOR ' }, { '
                           )
              ,' } ]'
             ) as comments
  FROM articles a1
  LEFTOUTERJOIN comments c
    ON c.articleId = a1.articleId
 GROUPBY a1.title, a1.content

SQLFiddle: http://sqlfiddle.com/#!2/5edcd/13

Solution 3:

The first approach is correct if both queries are done within the same transaction. It basically trades CPU-time on the server-side (for the costly second query) for some CPU-time on the client-side and network traffic in between.

While the second approach at first seems costly on the network-traffic side, some on-the-wire protocols support referring to same-value columns in an efficient way. Column values repeated due to joins are not transmitted for every row in that case. You should check how much traffic is really generated, the overhead is probably not as large as it seems. On the client side, you can store all articles in a hashmap-like-object, using the article's primary key. While receiving rows, you add comments to a list, each listed being indexed by that key. This can be made more efficient by sorting the rows on the server-side according to article's primary key, enabling you to use a naturally sorted list of lists on the client. Building this structure is the cpu-time-tradeoff compared to the first approach. My verdict: Use a JOIN.

Solution 4:

An equivalent function of the PostgreSQL's json_agg functiun is on the agenda of the next release of MySQL.

That will be very helpful.

You can check that at (there is a tip to realize the same kind of result with the current versions) :

MySQL 8.0 Labs: JSON aggregation functions(to follow)