Postgres HAVING ilike any wildcard in array_agg
... but it is the other way around. The wild card would need to be in the inputted value.


Yes, it's backwards, but ILIKE (internally operator ~~*) nor the alternative regexp operator ~* have commutators. I.e., you can't turn the expression around, can't switch left and right operand. See:

SELECT * FROM pg_operator WHERE oprname  IN ('~~*', '~*'); 


oprcom = 0 means no commutator. Details in the manual.

The right operand of (I)LIKE is a pattern, so switching is impossible. And the ANY construct only goes one way. Dead end. See:


Can PostgreSQL index array columns?


You can still turn it around using a different tool: the aggregate function bool_or():

SELECT p.id, p.name, array_agg(vo.name)
FROM   product           p
JOIN   product_variation pv ON p.id = pv.product_id
JOIN   variation_option  vo ON pv.variation_option_id = vo.id
GROUP  BY p.id
HAVING bool_or(vo.name ILIKE ('%' || $1 || '%'));


Also using plain JOIN instead of LEFT JOIN, which would be pointless in view of the later filter.


  ideally, I'd be able to input an array of strings


Now that's also possible for an input array:

...
HAVING bool_or(vo.name ~* ANY($1));


$1 being of type text[] or an array literal like '{foo, bar}'. I chose the regexp operator as we don't need leading and trailing % with it.

Since $1 is a pattern (or an array of patterns), as mentioned above, you may want to escape characters with special meaning (per array element). See:


Escape function for regular expression or LIKE patterns


If your input is selective, meaning only very few products actually qualify, then an alternative formulation with EXISTS might be faster - in combination with a trigram index on vo.name. See:


How is LIKE implemented?


Index:

CREATE INDEX variation_option_name_gin_trgm_idx ON variation_option USING gin (name gin_trgm_ops);


Query:

SELECT p.id, p.name, array_agg(vo.name)
FROM   product           p
JOIN   product_variation pv ON p.id = pv.product_id
JOIN   variation_option  vo ON pv.variation_option_id = vo.id
WHERE  EXISTS (
   SELECT -- list can be empty
   FROM   variation_option vo1
   WHERE  vo1.id = pv.variation_option_id
   AND    vo1.name ILIKE ('%' || $1 || '%')
-- AND    vo1.name ~* ANY($1)  -- for array input
   )
GROUP  BY p.id;


Related:


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