Stanislav Belyakov Stanislav Belyakov - 6 months ago 33
SQL Question

PostgreSQL: get count of occurrences of specified element in array

I need to calculate the count of occurrences of specified element in array, something like:


elem_occurrences_count(ARRAY[a,b,c,a,a], a) = 3


elem_occurrences_count(ARRAY[a,b,c], d) = 0



Is there any function in PostgreSQL that can be used to solve the problem? Any help is appreciated.

Answer

You will need to unnest the array and then count the occurrences.

with elements (element) as (
   select unnest(ARRAY['a','b','c','a','a'])
)
select count(*)
from elements
where element = 'a';

This can easily be embedded into a function:

create or replace function count_elements(elements text[], to_find text)
  returns bigint
as
$body$
  select count(*) 
  from unnest(elements) element 
  where element =  to_find;
$body$
language sql;