How can we sort a MAP based on Keys in Aws Athena SQL
select map_from_entries(array_agg(row(a,b)))
from
(
SELECT a, b
FROM unnest(map(ARRAY['z', 'a', 'b', 'c'], ARRAY[1, 2, 3, 4])) AS t(a, b)
order by a desc
)
Would like to get {a=2, b=3, c=4, z=1} But I am getting {a=2, b=3, z=1, c=4}
Trino doesnt have function map_top_n_keys as in Presto.. Thanks
2
Maps/dictionaries are unordered structures (though there are special cases like SortedMap in Java or SortedDictionary in C# but Trino/Presto do not expose such datastructure). The closest you can do is to sort an array of rows created from a map:
-- sample data
with dataset(m) as (
values ( map(ARRAY['z', 'a', 'b', 'c'], ARRAY[1, 2, 3, 4]) )
)
-- query
select array_sort(map_entries(m), (l, r) -> if(l[1] > r[1], 1, -1)) sorted_arr,
map_from_entries(array_sort(map_entries(m), (l, r) -> if(l[1] > r[1], 1, -1))) sorted_arr_to_map
from dataset;
Output:
| sorted_arr | sorted_arr_to_map |
|---|---|
| [{a, 2}, {b, 3}, {c, 4}, {z, 1}] | {a=2, b=3, z=1, c=4} |
Trino doesn’t have function
map_top_n_keysas in Presto
And this one is easily simulated with map and array functions:
-- query
select slice(array_sort(map_keys(m)), 1, 3)
from dataset;
Output:
_col0
-----------
[a, b, c]