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?

Did you try array_join(array_agg(value), ',')? I found a relevant post. Please check out. https://stackoverflow.com/questions/44142356/presto-equivalent-of-mysql-group-concat — Shantanu Kher, Jun 29 '20 at 17:34
Many thanks for the quickest reply! `map_agg` is the solution! :) — Cherry, Jun 29 '20 at 17:44

score 3 · Accepted Answer · edited Jun 29 '20 at 20:00

3

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!

edited Jun 29 '20 at 20:00

Piotr Findeisen

19,480
2
52
82

answered Jun 29 '20 at 17:43

Cherry

31,309
66
224
364

How convert rows to map in presto?

1 Answers1