How to filter out certain keys in a map in Trino/Presto?

Question

I have a column which is a map with key value pairs per row and i would like to filter out some keys that I do not care about such that the resultant map is a filtered version per row.

The keys that I care about will be in an array though which should be sorted. How can I use that array of keys along with an IN operator so I can use map_filter function or is there any other way to fetch the map per row using perhaps element_at function. I did not find anything in the documentation.

So far I have this SQL query:

    WITH temp AS (
    SELECT array_sort(filter(map_keys(mapColumn), x -> x not in ('id', 'image')))) as myFiltered 
FROM myTable WHERE myFilter = 'a'
    )     
    SELECT map_filter(mapColumn,(k, v) -> k in temp.myFiltered))) from myTable WHERE myFilter = 'a'

Answer 1

If I understood your requirement correctly - you need to use contains array function:

select map_filter(m, (k, v) -> contains(array[1], k)) filtered
from (values (map(array[2,1], array['b', 'a']))) as t(m);

Output:

 filtered
----------
 {1=a}

Or if you need to filter out some keys - then not contains:

select map_filter(m, (k, v) -> not contains(array[1], k)) filtered
from (values (map(array[2,1], array['b', 'a']))) as t(m);

Output:

 filtered
----------
 {2=b}