Skip to main content

Categories: Semi-Structured Data

ARRAY_FREQUENCY

Returns a map of key-value pairs: keys are the unique elements in the input array and values specify how many times the keys appear in the input array.

Syntax​

ARRAY_FREQUENCY(array LIST) → MAP​

  • array: The array of values for which to calculate frequency. Accepts primitive types.

Examples

ARRAY_FREQUENCY example 
SELECT ARRAY_FREQUENCY(ARRAY[2,1,2,1,1,5]);
-- {"1":3, "2":2, "5":1}
ARRAY_FREQUENCY example 
SELECT ARRAY_FREQUENCY(ARRAY['a','b','ab','b','a']);
-- {"a":2, "ab":1, "b":2}
ARRAY_FREQUENCY example 
SELECT ARRAY_FREQUENCY(ARRAY['foo', 'bar', 'FOO', 'foo']);
-- {"FOO":1, "bar":1, "foo":2}
array_col contains ARRAY[1, 2, NULL, 2] 
SELECT ARRAY_FREQUENCY(array_col);
-- {"1":1, "2":2}

Usage Notes​

• Supports only primitive types. Complex types like STRUCT, MAP, and LIST are not supported.

• String keys are compared case-sensitively.

• Any null elements in the column value are ignored.

• Returns an error if the input array is empty.