Postgres SQL array aggregate values from a single table based on conditions

Question

I'm a SQL newbie and I'm trying to write the following query:

I have the following table:

 user_id | chat_id 
---------+---------

Which represents a many to many mapping of users to chat rooms;

I'm looking to create a query that finds all the chat_ids that are associated with the input user_id, and then array aggregates all the user_ids associated with those chats excluding the input user_id.

So the result should look like this for example:

 chat_id | user_id 
---------+---------
    1     {1,3,5,6}

I've kind of jumbled together to following query; but I'm pretty sure I got something wrong:

WITH chatIDs AS (SELECT user_chats.chat_id FROM user_chats WHERE user_chats.user_id=$1)
WITH userIDs AS (SELECT user_chats.user_id FROM user_chats WHERE user_chats.chat_id=chatIDs AND user_chats.user_id != $1)
SELECT chatIDs, array_agg(userIDs) FROM user_chats;

EDIT:

Edited for clarity

likle · Accepted Answer · 2020-12-31 12:41:37Z

1

I believe you could just use a where clause to exclude the user:

SELECT chat_id, array_agg(user_id) FROM user_chats
WHERE user_id != $1 AND chat_id IN (SELECT chat_id FROM user_chats WHERE user_id = $1)
GROUP BY chat_id

edited Dec 31, 2020 at 12:41

answered Dec 31, 2020 at 12:30

likle

1,80711 silver badges11 bronze badges

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

3 Comments

Adrian Coutsoftides Over a year ago

This makes sense! But I first have to find all the chat_ids that are associated with the input user_id, and then array aggregate all the user_ids associated with those chats, but I think your query definetly comes close

likle Over a year ago

@AdrianCoutsoftides I see, if I understand you correctly you can achieve that by adding another condition (see the edited answer)

Adrian Coutsoftides Over a year ago

you beautiful person!

Collectives™ on Stack Overflow

Postgres SQL array aggregate values from a single table based on conditions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related