PostgreSQL LIKE query on ARRAY field
You can use unnest() like @dezso already mentioned, for instance with a LATERAL join (Postgres 9.3+)

SELECT l.*
FROM   list l, unnest(array_field) a  -- implicit lateral
WHERE  lower(a) LIKE '1234%';


You don't need any of this for the presented case. No materialized view at all. This query on the underlying tables is faster, because it can use an index:

SELECT *  -- or selected columns
FROM   article_list a
JOIN   LATERAL  (                      -- only matching IDs
   SELECT array_agg(id) AS offer_list
   FROM   task_offer
   WHERE  article = a.oa_nr            -- LATERAL reference  
   AND    id::text ILIKE '1234%'       -- or just LIKE
   ) o ON offer_list IS NOT NULL;


offer_list is an array of the original data type in my result, and contains only matching IDs.

Returns a single row from article_list with an array of matching IDs in task_offer. Add a text_pattern_ops index (for left-anchored matches) to get best read performance:

CREATE INDEX task_offer_foo_idx ON task_offer (article, (id::text) text_pattern_ops);



Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


Or a trigram index for infix matches (not left-anchored):


How is LIKE implemented?


Or maybe a plain join for selective patterns:

SELECT a.*, o.offer_list -- or selected columns
FROM   article_list a
JOIN   (                          -- only matching IDs
   SELECT article, array_agg(id) AS offer_list
   FROM   task_offer
   WHERE  id::text ILIKE '1234%'  -- or just LIKE
   GROUP  BY 1
   ) o ON o.article = a.oa_nr;


Needs an index on article_list, too. (Which you probably have - no information in the question.) Like:

CREATE INDEX article_list_oa_nr_idx ON article_list (oa_nr);