Qubole Presto datatype "Map" using the Like Operator

Question

So I am trying to apply a simple like function for a Qubole query on Presto. For a string datatype I can simply do like '%United States of America%'.

However for the column I am trying to apply this has the underlying datatype as "map" and thus the query is failing. How do I write the like operator for the map datatype so that it fetches only those columns that match the pattern.

Answer 1

Please refer to the documentation about map related functions in Presto at https://trino.io/docs/current/functions/map.html

You should be able to make use of map_filter to filter out the required entries. E.g.:

SELECT map_filter(MAP(ARRAY['India', 'Poland', 'United States of America'], ARRAY[20, 3, 15]), (k, v) -> k like '%United States of America%');

             _col0             
-------------------------------
 {United States of America=15} 
(1 row)