PostgreSQL – How to Use LIKE Query on ARRAY Field

database-designpattern matchingpostgresql

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.

Best Answer

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);

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

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);
Related Question