Select from array in postgres using wildcard/Like

Question

So I have a Postgres database where one of the columns is an array of strings

If I do the query

SELECT count(*) FROM table WHERE column @> ARRAY['string']::varchar[];

I get a certain set of data back, but if I want to query that array with a wildcard on the string I can't seem to figure that out, something like

SELECT count(*) FROM table WHERE column LIKE ARRAY['%string%']::varchar[];

Any help is appreciated!

Answer 1

use unnest():

WITH t(arr) AS ( VALUES
  (ARRAY['foo','bar']),
  (ARRAY['foo1','bar1'])
)
SELECT count(*) FROM t,unnest(t.arr) AS str
WHERE str ILIKE '%foo%';

Result:

 count 
-------
     2
(1 row)