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
– Erwin Brandstetter, Commented Sep 28, 2016 at 0: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
– William Gottschalk, Commented Sep 28, 2016 at 1:34
Are you going to disclose your version of Postgres (SELECT version();) and the desired data type for the result? — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 28, 2016 at 1: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
– William Gottschalk, Commented Oct 2, 2016 at 17:08

redneb · Accepted Answer · 2016-09-27 21:28:59Z

9

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;

answered Sep 27, 2016 at 21:28

redneb

24.3k7 gold badges46 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2023-02-22 22:52:45Z

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.

edited Feb 22, 2023 at 22:52

answered Sep 28, 2016 at 1:20

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

2 Comments

William Gottschalk Over a year ago

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 Over a year ago

This is awesome! Thank you so much!

Collectives™ on Stack Overflow

SQL array agg and joins

2 Answers 2

Comments

Answer to updated question

Answer to original question

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Answer to updated question

Answer to original question

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related