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.
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'));
(array_length(string_to_array(combined_elements_as_one_text, CAST(element AS varchar)), 1) - 1). Do you have any ideas how to increase performance?combined_elements_as_one_text is a list of elements (elements text[]) that were casted to varchar and joined as one text string. There is a string_to_array function that splits the string (combined_elements_as_one_text) with the specified delimiter CAST(element AS varchar)) into array. This array always contain counts of element that equal to occurrence count plus one.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)
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;
You can use the FILTER clause to count the occurrences.
Let's say we have a questions table with tags (array type) and you want to count the questions with postgresql tag:
SELECT COUNT(*) FILTER (WHERE '{postgresq}' <@ (tags)) as tagCount
FROM posts;
More generic function is here;
CREATE FUNCTION count_array_elements (
i_elements pg_catalog.anyarray,
i_element_to_find pg_catalog.anyelement,
out count bigint
)
RETURNS bigint AS
$body$
BEGIN
SELECT count(*) INTO count
FROM unnest(i_elements) u
WHERE u = i_element_to_find;
END;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
RETURNS NULL ON NULL INPUT;
With this way, we can query like this one below;
SELECT * FROM count_array_elements(array [ TRUE, TRUE, FALSE, FALSE, FALSE ], TRUE);
Thanks to all contributors here, I learnt a few things. I am building on work of others in this thread and others in stackoverflow.
I tried to create a function that will count for all the unique elements in the array.
I was targeting returning a json but it seems you can only return as SETOF.
CREATE OR REPLACE FUNCTION count_element_3(str_array text[])
RETURNS setof text
AS
$$
DECLARE
unique_element_array text[];
cardinality_array int[];
retArray text[];
BEGIN
-- Find unique items first
unique_element_array := array(select distinct unnest(str_array));
FOR I IN array_lower(unique_element_array, 1)..array_upper(unique_element_array, 1)
LOOP
cardinality_array[I] := (select cardinality(array_positions(str_array, unique_element_array[I])));
retArray[I] := concat(unique_element_array[I],':',cardinality_array[I]);
END LOOP;
RETURN QUERY SELECT(retArray::text);
END;
$$
LANGUAGE plpgsql
VOLATILE
RETURNS NULL ON NULL INPUT;
with t1 as (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)
)
select sarray, count_element_3(sarray) from t1
sarray count_element_3
text[] text
-------------------------------------
"{a,b,c,a,a}" "{c:1,a:3,b:1}"
"{a,b,c}" "{c:1,a:1,b:1}"
