How convert rows to map in presto?

Consider SQL statement:

SELECT key, value FROM ( SELECT key, value, row_number() OVER (PARTITION BY key ORDER BY length(value) DESC) AS rn FROM my_table ) WHERE rn <= 5; 

This produces:

key value A 1 A 2 B 10 

How to make this like:

key values A [1;2;3;4;5] B [10;20;30;40;50] 

Sql engine is presto. Any Ideas?

1 Answer

map_agg is the solution:

SELECT key, map_agg(key, value) FROM ( SELECT key, value, row_number() OVER (PARTITION BY key ORDER BY length(value) DESC) AS rn FROM my_table ) WHERE rn <= 5 group by key; 

Many thanks, @Shantanu Kher for the quickest reply!