PostgreSQL: get count of occurrences of specified element in array

Question

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 1

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;

Update

Since Postgres 9.5 this can also be done using array_positions() which returns an array of positions where an element was found. The length of that array is the number of occurrences:

select cardinality(array_positions(ARRAY['a','b','c','a','a'], 'a'));

Answer 2

9.5+

There is an easier method now

SELECT
  sArray,
  c,
  coalesce(array_length( array_positions(sArray, c), 1 ),0) AS count
FROM ( VALUES
  (ARRAY['a','b','c','a','a'], 'a'),
  (ARRAY['a','b','c'], 'd')
) AS t(sArray,c);

   sarray    | c | count 
-------------+---+-------
 {a,b,c,a,a} | a |     3
 {a,b,c}     | d |     0
(2 rows)

Answer 3

The occurrence of all elements in an array can be found with this query:

SELECT count(id), UNNEST(array) as element
FROM myTable 
GROUP BY element;

To count the occurrence of a specific element, for example 'c', add a WHERE clause:

SELECT count(id), UNNEST(array) as element
FROM myTable 
WHERE EXISTS (SELECT * FROM UNNEST(array) AS x WHERE x='c')
GROUP BY element;