Postgres HAVING ilike any wildcard in array_agg

Question

I need a filter where the input value is similar to any value in an aggregate, using ilike and wildcards. My attempt at a query so far is:

SELECT p.id, p.name, array_agg(vo.name)
FROM product p
LEFT JOIN product_variation pv
ON p.id = pv.product_id
LEFT JOIN variation_option vo
ON pv.variation_option_id = vo.id
GROUP BY p.id
HAVING 1ドル ilike any(array_agg('%' || vo.name || '%'));

But it doesn't work. It gets even more complicated: ideally, I'd be able to input an array of strings, where any of them are similar to the aggregate. Any tips?

I realize this kind of works, just not as I need it to. The input value should be able to be a partial match for any of the array elements, but it is the other way around. The wild card would need to be in the input value.

Answer 1

... 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