Postgresql get the most common value in array

Question

I have a table with column with array values (after group by and array_agg function)

COLUMN_VALUE          | other_columns...
-----------:          | -------:
 {0.45,0.45,0.97,0.99}|        ..
 {0.45,0.45,0.85,0.99}|        ..
 {0.45,0.45,0.77,0.99}|        ..
 {0.45,0.45,0.10,0.99}|        ..

How do I get the most frequent value? (0.45 for each row for this case)

My guess goes to unnest and groupby again but I am trying to find something more robust and faster.

Query I am using to build the table

select column1, column2, column3, array_agg(column4) as prices
from tb
where some conditions
group by 1, 2, 3

Answer 1

You can get the most frequent value during aggregation using the mode() aggregate:

select column1, column2, column3, 
       array_agg(column4) as prices
       mode() within group (order by column4 desc) as most_frequent_price
from tb
where ...
group by 1, 2, 3

Online example