SQL array agg and joins

Question

In my Postgres database, I have 3 tables. One for users one for comments and one to map the two user_comment_map. Here's how the tables look:

users
| id | name | age |
|----|------|-----|
| 1  | user | 20  |


comments
| id | mood  | subject | content         | created_at               |
|----|-------|---------|-----------------|--------------------------|
| 1  | happy | funny   | here is content | Tue Sep 27 2016 13:44:19 |
| 2  | silly | cool    | more content    | Tue Sep 27 2016 14:44:19 |

user_comment_map
| id | user_id | comment_id |
|----|---------|------------|
| 1  |        1|           1|
| 2  |        1|           2|

I'm trying to write a SQL query that results in the following object:

[{
  id: 1,
  name: "user",
  age: 20,
  comments: [
    {
      id: 1,
      mood: "happy",
      subject: "silly",
      content: "here is content",
      created_at: "Tue Sep 27 2016 13:44:19"
    }, 
    {
      id: 2,
      mood: "silly",
      subject: "cool",
      content: "more content",
      created_at: "Tue Sep 27 2016 14:44:19"
    },
  },
  {...}
]

I tried using joins and array_agg but I can't get the data in that format. Any help would be much appreciated. Note: I'm also using knex to build queries but I don't think that knex can handle stuff like this without resorting to knex.raw

Answer 1

This should do it:

SELECT
    json_build_object(
        'id',id,
        'name',name,
        'comments',(
            SELECT json_agg(json_build_object(
                'id', comments.id,
                'mood', comments.mood,
                'subject', comments.subject,
                'content', comments.content,
                'created_at', comments.created_at
            ))
            FROM user_comment_map JOIN comments ON comment_id=comments.id
            WHERE user_id=users.id
        )
    )
FROM users
WHERE id=1;

Answer 2

Answer to updated question

Without involving json. Just plain Postgres row and array types. And return all rows with all columns from users and comments:

SELECT ARRAY(
   SELECT t
   FROM  (
      SELECT u.*
          , (SELECT array_agg(c)
             FROM   user_comment_map uc
             JOIN   comments c ON uc.comment_id = c.id
             WHERE  uc.user_id = u.id
            ) AS comments
      FROM   users u
      ) t
   );

Using an ARRAY constructor on a subquery should be simplest and fastest.

But the result is an array of anonymous records. You probably do not want that. I suggest to rethink your approach.

---

Answer to original question

To return the result displayed as data type json (omitting users.age), to_json() on the alias of a subquery should be simplest and fastest. Works in Postgres 9.2 or later:

SELECT to_json(t)
FROM (
   SELECT u.id, u.name        -- manual selection from from table users
       , (SELECT json_agg(c)  -- use table alias of comments to wrap whole row
          FROM   user_comment_map uc
          JOIN   comments c ON uc.comment_id = c.id
          WHERE  uc.user_id = u.id
         ) AS comments
   FROM   users u
   WHERE  u.id = 1
   ) t;

This query returns whole rows from comments and picks up on added / removed columns if you should change the table layout. But you have to maintain your selection of columns from users manually.

To get the actual text representation displayed (data type text), you can use jsonb_pretty() in Postgres 9.5 or later:

SELECT jsonb_pretty(to_jsonb(t))
FROM (
   SELECT u.id, u.name
       , (SELECT jsonb_agg(c)
          FROM   user_comment_map uc
          JOIN   comments c ON uc.comment_id = c.id
          WHERE  uc.user_id = u.id) AS comments
   FROM   users u
   WHERE  u.id = 1
   ) t;

I am using jsonb functions here since json_pretty() only takes jsonb.