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?
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