CODEWITHSUNDEEP
arrayspostgresqlcomparison-operators
vaartis
vaartis

Reputation: 424

Search with LIKE in PostgreSQL array

I have this table:

 id |   name   |          tags           
----+----------+-------------------------
  1 | test.jpg | {sometags,other_things}

I need to get rows that contain specific tags by searching in array with regular expression or LIKE, like this:

SELECT * FROM images WHERE 'some%' LIKE any(tags);

But this query returns nothing.

Upvotes: 1

Views: 59

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125404

with images (id, name, tags) as (values
    (1, 'test.jpg', '{sometags, other_things}'::text[]),
    (2, 'test2.jpg', '{othertags, other_things}'::text[])
)
select *
from images
where (
    select bool_or(tag like 'some%')
    from unnest(tags) t (tag)
);
 id |   name   |          tags           
----+----------+-------------------------
  1 | test.jpg | {sometags,other_things}

unnest returns a set which you aggregate with the convenient bool_or function

Upvotes: 4

Related Questions