Athena presto query of map type columns with dynamic key names

Question

I'm using Athena presto query to find value from a map type column. I want to find a key/value pair with dynamic key names. Specifically, my keys would be like this abc_1, abc_2, ...., abc_100 and it always starts with a perfix, then a dynamic part and finished with some letters. There are many possible combinations and I cannot enumerate all of them. So I'm wondering whether I could use regular expression or wildcard in the keys like something below:

selelct map_var1['abc_**'] from tab1.

I tagged hive and spark as well and please suggest solutions in other language too. I could use other languages.

Thanks!

Answer 1

You can use map_filter:

-- sample data
with dataset(id, map_col) as
    (values (1, map(array['abc_1', 'abc_2', 'skip'], array[1, 2, 3])))

-- query
select map_filter(map_col, (k, v) -> k like 'abc_%')
from dataset;

Output:

       _col0
--------------------
 {abc_1=1, abc_2=2}