How convert rows to map in presto?

Question

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?

Answer 1

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!