Postgres HAVING ilike any wildcard in array_agg
1
vote
1
answer
5700
views
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.
Asked by koi
(23 rep)
Jun 10, 2020, 02:46 PM
Last activity: May 3, 2025, 07:07 AM
Last activity: May 3, 2025, 07:07 AM