How to count array elements occurrences in Presto?

Question

I have an array in Presto and I'd like to count how many times each element occurs in it. For example, I have

[a, a, a, b, b]

and I'd like to get something like

{a: 3, b: 2}

Answer 1

We do not have a direct function for this, but you can combine UNNEST with histogram:

presto> SELECT histogram(x)
     -> FROM UNNEST(ARRAY[1111, 1111, 22, 22, 1111]) t(x);
     _col0
----------------
 {22=2, 1111=3}

You may want to file a new issue for a direct function for this.