filtering on keys produced in map_agg function - Trino

Question

looking for a good solution to filter on keys being used in the mapp_agg function in the query below.

I am trying to filter the key column to reduce the size of the map to only the columns I am looking to create, however when running this I get a key not present error. If I dont use an IN but rather an OR then it work but that impact performance as I add additional columns

SELECT 
id,
metric_id,
kv['uni Amount'] as uni_amt,
kv['mrt Amount'] as mrt_amt
FROM 
id,
metric_id,
map_agg(
        key,
        value
      ) kv
from table
where key in ('uni Amount', 'mrt Amount')
group by 
id,
metric_id)

I tried to use map_filter as well but couldn't get it working

SELECT 
id,
metric_id,
kv['uni Amount'] as uni_amt,
kv['mrt Amount'] as mrt_amt
FROM 
id,
metric_id,
map_filter(map_agg(
        key,
        value
      ), (k,v) k -> k in ('uni Amount', 'mrt Amount')
from table
group by 
id,
metric_id)

Answer 1

It seems that you have a minor syntax mistake/type, map_filter requires a second parameter in form of function with two arguments (key and value) and resulting in bool which can be written in anonymous/lambda/arrow form which is can be found in a lot of languages. Try changing (k,v) k -> k in ('uni Amount', 'mrt Amount') to (k,v) -> k in ('uni Amount', 'mrt Amount') (k and v are key and value parameters of the function) and add missing parenthesis:

-- ...
map_filter(
    map_agg(key, value)
    , (k,v) -> k in ('uni Amount', 'mrt Amount'))
-- ...

however when running this I get a key not present error

You can use element_at instead of indexer access which requires key to be present:

element_at(map(K, V), key) → V Returns value for given key, or NULL if the key is not contained in the map.