Fetch matching results from the integer array satisfying the condition which is given as text

Question

I've an array of integer data stored in a particular field in the user table. This array represents the groups in which the user belongs. A user can have any number of groups.

ie,

Table: user

user_id  |  user_name  |  user_groups
---------+-------------+-------------
1        |   harry     | {1,2,3}
2        |   John      | {4,5,6}

Table: Groups

group_id    |    group_name
------------+--------------
    1       |    Arts
    2       |    Science
    3       |    Security
    4       |    Sports

(Pardon, It should have been an 1-N relationship). I need to execute a query as follows,

SELECT * from user where user_groups = ANY(x);

where x will be text values Arts,Science,Security,Sports. So when x= Arts, the result of harry is returned. The database that I'm using is Postgresql8.4

Answer 1

You can use @> contains operator:

SELECT *
FROM Users
WHERE user_groups @> (SELECT ARRAY[group_id]
                      FROM Groups
                      WHERE group_name = 'Arts')

SqlFiddleDemo

EDIT:

Is there any way by which I could display user_groups like {Arts,Science,Security}, instead of {1,2,3}

You could use correlated subquery:

SELECT user_id, user_name, (SELECT array_agg(g.group_name)
                            FROM Groups g
                            WHERE ARRAY[g.group_id] <@ u.user_groups) AS user_groups
FROM Users u
WHERE user_groups @> (SELECT ARRAY[group_id]
                      FROM Groups
                      WHERE group_name = 'Arts')

SqlFiddleDemo2