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

Did you forget `users.age` in the result or do you omit it on purpose? Do you need `json`, `jsonb` or the exact text representation displayed as result? and please *always* declare the Postgres version in use. — Erwin Brandstetter, Sep 28 '16 at 00:52
Yeah. I went ahead and made edits. Also realized that I just need to sequel query to return an array of user objects with comment data nested in each user. — William Gottschalk, Sep 28 '16 at 01:34
Are you going to disclose your version of Postgres (`SELECT version();`) and the desired data type for the result? — Erwin Brandstetter, Sep 28 '16 at 01:39
It's Postgres 9.5. My server is written in Node so when I run the sql query, it should return an array of user objects. Each user object will have an array of comment objects associated with it. — William Gottschalk, Oct 02 '16 at 17:08

score 7 · Accepted Answer · answered Sep 27 '16 at 21:28

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;

Erwin Brandstetter · Answer 2 · 2023-02-22T22:52:45.990

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.

I really appreciate the answer. It doesn't need to return json. just an array of user objects. Each user contains their associated tweets — William Gottschalk, Sep 28 '16 at 01:39

SQL array agg and joins

2 Answers2

Answer to updated question

Answer to original question