Take average of map values in Trino

Question

I have a column of Map(String(), Double()).

I want to take the average of the (non-null) values for each key that appears in the column.

SELECT 
    AVG(ELEMENT_AT(action_to_score, 'x')) as x,
    AVG(ELEMENT_AT(action_to_score, 'y')) as y
FROM (
    VALUES
        (map_from_entries(ARRAY[('x', 1), ('y', 1)])),
        (map_from_entries(ARRAY[('y', 2)])),
        (map_from_entries(ARRAY[('x', 3)]))
) AS t(action_to_score)

This above query returns

  x    y
2.0  1.5

I want to make the query without hardcoding the keys (namely x and y) in the query. Thanks!

Answer 1

As far as I know it is not possible, because Trino does not support dynamic pivoting, the only "dynamic" option is to transform data into key-value columns by flattening map with unnest (I use succinct syntax, which allows to skip writing CROSS JOIN explicitly) and then performing the needed aggregation over group by:

SELECT k key,
    AVG(v) avg
FROM (
    VALUES
        (map_from_entries(ARRAY[('x', 1), ('y', 1)])),
        (map_from_entries(ARRAY[('y', 2)])),
        (map_from_entries(ARRAY[('x', 3)]))
) AS t(action_to_score),
unnest(action_to_score) as ut(k, v)
group by k;

Output:

key	avg
y	1.5
x	2.0