Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have an aggregate function that, among other things, returns an array of elements, like:

SELECT lognum,array_agg(flightnum) as flightnums FROM logs GROUP BY lognum; 

Where the flightnum field is a varchar containing either a text string or a three-or-four digit number. Now say I want to select all logs that have a flight number starting with an '8' (so '800' or '8000' series flights). My first thought was to do something like this:

SELECT * FROM (SELECT lognum,array_agg(flightnum) as flightnums FROM logs GROUP BY lognum) s1 WHERE '8%' like ANY(flightnums); 

But while this doesn't give an error, it also doesn't return any results. I'm guessing that this is because the wildcard is on the left of the operator. Of course, turning it around to be:

WHERE ANY(flightnum) like '8%' 

gives me a syntax error. So is there any way I can run this query such that I get any rows containing a flight number that starts with an 8 (or whatever)? Please note that this is a simplified example demonstrating just the section I am having difficulty with.

Is there any way to do a pattern match against the elements of an array in postgresql (9.4 if the version makes a difference)? I have an aggregate function that, among other things, returns an array of elements, like:

SELECT  lognum ,array_agg(flightnum) as flightnums   FROM logs   GROUP BY lognum; 

Where the flightnum field is a varchar containing either a text string or a three-or-four digit number. Now say I want to select all logs that have a flight number starting with an '8' (so '800' or '8000' series flights). My first thought was to do something like this:

SELECT  *   FROM ( SELECT  lognum ,array_agg(flightnum) as flightnums  FROM logs  GROUP BY  lognum ) s1   WHERE  '8%' like ANY(flightnums); 

But while this doesn't give an error, it also doesn't return any results. I'm guessing that this is because the wild card is on the left of the operator. Of course, turning it around to be:

WHERE ANY(flightnum) like '8%' 

gives me a syntax error. So is there any way I can run this query such that I get any rows containing a flight number that starts with an 8 (or whatever)? 

Please note that this is a simplified example demonstrating just the section I am having difficulty with.