pjwal
pjwal

Reputation: 1

Query with condition on array items in PostgreSQL

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?

Upvotes: 0

Views: 659

Answers (1)

klin
klin

Reputation: 121514

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)

Upvotes: 1

Related Questions