CODEWITHSUNDEEP
sqlpostgresqljoin
Mackan90096
Mackan90096

Reputation: 354

PostgreSQL Aggregate data into single row

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?

Upvotes: 1

Views: 140

Answers (2)

Oto Shavadze
Oto Shavadze

Reputation: 42753

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

Upvotes: 2

404
404

Reputation: 8542

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.

Upvotes: 2

Related Questions