PostgreSQL LIKE query on ARRAY field

Question

Is there any way to have a Postgres LIKE query on a ARRAY field?

Currently I want something like that:

SELECT * FROM list WHERE lower(array_field) LIKE '1234%'

Currently lower is not needed that much. However it should find ONE matching field inside the ARRAY. Is that even possible?

Currently I use a materialized view to generate the "list" table with a JOIN and a ARRAY_AGG(), since I JOIN a table where more values could be on the right table. Which would duplicate fields on the left table, which is not what I want.

Edit this is how I create the view (really sluggish and ugly):

CREATE MATERIALIZED VIEW article_list_new AS
SELECT a.id, 
       a.oa_nr, 
       a.date_deleted, 
       a.lock, 
       a.sds_nr, 
       a.kd_art_nr, 
       a.kd_art_index, 
       a.kd_art_extend, 
       a.surface, 
       a.execution, 
       a.surface_area, 
       a.cu_thickness, 
       a.endintensity, 
       a.drilling, 
       array_agg(o.id::text) AS offer_list 
FROM article_list a LEFT JOIN task_offer o ON o.article = a.oa_nr 
GROUP BY .....;  

I also need to return the IDs of the task_offer table.

Answer 1

You can use unnest() like dezso commented, for instance with a LATERAL join:

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 o
   WHERE  o.article = a.oa_nr            -- LATERAL reference  
   AND    id::text ILIKE '1234%' COLLATE "C"  -- or just LIKE
   ) o ON offer_list IS NOT NULL;

Returns a single row from article_list with an array offer_list of matching IDs in task_offer (if any) - an array of the original data type.

Add an index using COLLATE "C", so it can be used for left-anchored LIKE patterns:

CREATE INDEX task_offer_foo_idx ON task_offer (article, (id::text) COLLATE "C");

In older versions of Postgres, the operator class text_pattern_ops served the same purpose. See:

• Is there a difference between text_pattern_ops and COLLATE "C"?

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

• How is LIKE implemented?
• Pattern matching with LIKE, SIMILAR TO or regular expressions

Or maybe use 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%' COLLATE "C"  -- or just LIKE
   GROUP  BY 1
   ) o ON o.article = a.oa_nr;

Needs an index on article_list.oa_nr, too. (Which you probably have.) Like:

CREATE INDEX article_list_oa_nr_idx ON article_list (oa_nr);

Answer 2

Have you considered the parray_gin extension, also located here?

I haven't used it in production yet, but from what I have seen of it it does what you want against the array. You can create the relevant index on the materialized view.

However, I would only do that if you are going to have the materialized view anyway. Otherwise I think @ErwinBrandstetter's answer is better than creating a materialized view only to hold the index.