I am trying to select a row from a postgresql table based on the data in the array of that row. Here are the two tables:
Table 1: users
| users | --- |
|---|---|
| username | text |
| password | text |
Table 2: Chats
| chats | --- |
|---|---|
| room_id | serial |
| users | text[] |
| messages | json[] |
Let's say I have a user who's username is "Hello_world1" and I wanted to get all of the chats for that user. I would say "SELECT * FROM chats WHERE users contains "Hello_world2" I've tried "SELECT * FROM chats WHERE users[1] = "Hello_world2", but this will not work in the long because it means I would have to create another chat which is the same as this one but with that other user's name in users[1].
CodePudding user response：
I found a solution!
Here is the query:
SELECT * FROM chats WHERE "Hello_world1" = ANY (users)
Translation:
Grab every row from chats if any of the users from the users array = "Hello_world1"