postgresql : search records based on array field vaule with multiple values

Question

I have a table that has an array field.

CREATE TABLE notifications
(
    id integer NOT NULL DEFAULT nextval('notifications_id_seq'::regclass),
    title character(100) COLLATE pg_catalog."default" NOT NULL,
    tags text[] COLLATE pg_catalog."default",
    CONSTRAINT notifications_pkey PRIMARY KEY (id)
)

and tags field can have multiple values from

["a","b","c","d"]

now I want all the records for which tags have a or d ("a","d")array values.

I can use postgresl in but this can be used to search single value. How can I achieve this?

Answer 1

You could use ANY:

SELECT *
FROM notifications
WHERE 'a' = ANY(tags) OR 'b' = ANY(tags);

DBFiddle Demo

Answer 2

If the values 'a' and 'b' are static (you only need to check for those 2 values in every query), then you can go with the solution that Lukasz Szozda provided.

But if the values you want to check for are dynamic and are different in multiple queries(sometimes it is {'a','b'} but sometimes it is {'b', 'f','m'}) you can create an intersection of both of the arrays and check if the intersection is empty.

For example:

If we have the following table and data:

CREATE TABLE test_table_1(description TEXT, tags TEXT[]);
INSERT INTO test_table_1(description, tags) VALUES
  ('desc1', array['a','b','c']),
  ('desc2', array['c','d','e']);

If we want to get all of the rows from test_table_1 that have one of the following tags b, f, or m, we could do it with the following query:

SELECT * FROM test_table_1 tt1
 WHERE  array_length((SELECT array
    (
        SELECT UNNEST(tt1.tags)
        INTERSECT
        SELECT UNNEST(array['b','f','m'])
    )), 1) > 0;

In the query above we use array_length to check if the intersection is empty.

Writing the query this way can also be useful if you want to add additional constraint to the number of matched tags. For example if you want to get all of the rows that have at least 2 tags from the group {'a','b','c'} you just need to set array_length(...) > 1