I would like to select rows in a table in which a certain number of items in an array column meet a comparison condition (>= n). Is this possible without using unnest?
1 Answer
unnest() is a natural way to count filtered elements in an array.
However, you can hide this in an sql function like this:
create or replace function number_of_elements(arr int[], val int)
returns bigint language sql
as $$
select count(*)
from unnest(arr) e
where e > val;
$$;
with test(id, arr) as (
values
(1, array[1,2,3,4]),
(2, array[3,4,5,6]))
select id, arr, number_of_elements(arr, 3)
from test;
id | arr | number_of_elements
----+-----------+--------------------
1 | {1,2,3,4} | 1
2 | {3,4,5,6} | 3
(2 rows)
