PostgreSQL Aggregate data into single row

Question

With the following SQL query which I want to add an array of JSON objects which has data from another table, based on the posts.liked_by array:

SELECT 
  p.id, 
  p.author, 
  p.content, 
  u2.id, 
  u2.username, 
  p.liked_by AS likedby
FROM posts p
INNER JOIN users u1 
  ON p.author = u1.id
LEFT JOIN users u2 
  ON u2.id = ANY(p.liked_by)

I'm getting the expected output of

╔════╤════════╤═════════════╤══════════╤═════════╗
║ id │ author │ content     │ username │ likedby ║
╠════╪════════╪═════════════╪══════════╪═════════╣
║ 1  │ 1      │ Lorem Ipsum │ John Doe │ {1, 2}  ║
╚════╧════════╧═════════════╧══════════╧═════════╝

Now, I'd like to modify the likedby column to be an array of objects with user data, accoring to something along the lines of this:

+----+--------+-------------+----------+-----------------------------------------------------------------+
| id | author | content     | username | liked_by                                                        |
+----+--------+-------------+----------+-----------------------------------------------------------------+
| 1  | 1      | Lorem Ipsum | John Doe | [{id: 1, username: "John Doe"}, {id: 2, username: "Sam Smith"}] |
+----+--------+-------------+----------+-----------------------------------------------------------------+

with data of the posts table being structured like

+----+--------+-------------+-----------+-----------+
| id | author | content     | author_id | liked_by  |
+----+--------+-------------+-----------+-----------+
| 1  | 1      | lorem ipsum | 1         | {1, 2, 3} |
+----+--------+-------------+-----------+-----------+

and the user table being structured as

+----+----------+
| id | username |
+----+----------+
| 1  | John Doe |
+----+----------+

How would I go about doing this?

from here: FROM public.posts AS posts, public.users remove: , public.users — Oto Shavadze
– Oto Shavadze, Commented Sep 11, 2018 at 14:29
@OtoShavadze That just switches the error to the users table. For reference, I changed it to FROM public.posts AS posts LEFT JOIN users u ON users.id = ANY (posts.likedby) — Mackan90096
– Mackan90096, Commented Sep 11, 2018 at 14:31
@a_horse_with_no_name I based the query on a different query for getting the author data from the user table with the author column value along with the post data — Mackan90096
– Mackan90096, Commented Sep 11, 2018 at 14:33
Please edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. (edit your question - do not post code or additional information in comments) — user330315
– user330315, Commented Sep 11, 2018 at 14:43
Question has been edited with sample data and the ideal output. — Mackan90096
– Mackan90096, Commented Sep 11, 2018 at 14:51

Oto Shavadze · Accepted Answer · 2018-09-11 15:37:44Z

2

for getting aggregated user names for liked_by column, you can use subquery with jsonb_agg() and jsonb_build_object() functions:

SELECT posts.*, "user".username as author_name,  
    (SELECT jsonb_agg(jsonb_build_object("user".id, "user".username)) FROM "user" where "user".id = any(posts.liked_by)  )
FROM posts
INNER JOIN "user"
ON posts.author_id = "user".id

http://rextester.com/KMEY13984

edited Sep 11, 2018 at 15:37

answered Sep 11, 2018 at 15:31

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

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

1 Comment

Mackan90096 Over a year ago

This did exactly what I wanted! Thank you!

404 · Accepted Answer · 2018-09-11 15:19:12Z

2

It works with an explicit join (btw it's not good form to mix and match):

SELECT
    ...
FROM posts p
INNER JOIN users u1 
    ON p.author = u1.id
LEFT JOIN users u2 
    ON u2.id = ANY(p.liked_by)

And as to why, from the postgres documentation:

... JOIN binds more tightly than comma. For example FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as FROM T1, T2 INNER JOIN T3 ON condition because the condition can reference T1 in the first case but not the second.

edited Sep 11, 2018 at 15:19

answered Sep 11, 2018 at 15:09

404

8,7522 gold badges33 silver badges53 bronze badges

2 Comments

Mackan90096 Over a year ago

This solved the issue with the error, thank you, I have since edited the question to closer reflect what I'd like to do.

404 Over a year ago

OK but you shouldn't change your question like that, as now the answer is in response to an issue that no longer appears in the question. Either create a new question for how to get results in a certain format, or edit your question without deleting the original issue.

Collectives™ on Stack Overflow

PostgreSQL Aggregate data into single row

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related