I've been stuck on this for a while now. I've tried asking people on Discord groups but don't seem to get any help on there.

I'm trying to make a Twitter clone. I found node-Postgres package, but I'm a bit confused about how to structure my queries. Is there a way to combine multiple tables and the result would be in JSON object format? I'm trying to make something like this

Tweet: {
id,
message,
img,
comments : [{ id:, user:, message},  id:, user:, message},  id:, user:, message},  id:, user:, message}, ],
likes : [],
retweets: [].
saved: []
 }

I found out about json_object_build and json_agg and did something like this

The result is like this:

{"user_id": 1, "tweet_id": 1, "tweet_message": "Test Tweet", "tweet_comments": [{"likes": 1, "user_id": 1, "comment_id": 3, "comment_message": "testing 10"}, {"likes": 3, "user_id": 3, "comment_id": 2, "comment_message": "testing 2"}, {"likes": 1, "user_id": 2, "comment_id": 1, "comment_message": "testing 1"}]}

{"user_id": 2, "tweet_id": 2, "tweet_message": "Test Tweet 2", "tweet_comments": [{"likes": null, "user_id": 3, "comment_id": 5, "comment_message": "testing 2"}, {"likes": null, "user_id": 2, "comment_id": 6, "comment_message": "testing 10"}, {"likes": null, "user_id": 1, "comment_id": 4, "comment_message": "testing 1"}]}

But from what I saw in the documents for node-postgress it doesn't support this.