Checking if key exists in Presto value map

presto trino
the_darkside picture the_darkside · Mar 29, 2019 · Viewed 11.1k times · Source

I am new to Presto, and can't quite figure out how to check if a key is present in a map. When I run a SELECT query, this error message is returned:

Key not present in map: element

SELECT value_map['element'] FROM
mytable
WHERE name = 'foobar'

Adding AND contains(value_map, 'element') does not work

The data type is a string array

SELECT typeof('value_map') FROM mytable

returns varchar(9)

How would I only select records where 'element' is present in the value_map?

Answer

Piotr Findeisen picture Piotr Findeisen · Mar 29, 2019

You can lookup a value in a map if the key is present with element_at, like this:

SELECT element_at(value_map, 'element')
FROM ...
WHERE element_at(value_map, 'element') IS NOT NULL

Related questions

How to convert timestamp to date in Presto?

I like to convert my timestamp columns to date and time format. How should I write the query from presto? my timestamp is UTC time. Thank you very much Timestamp format"1506929478589" After query convert it looks like "2016-10-25 21:04:08.436"

Read More
Presto equivalent of MySQL group_concat

I'm new to Presto and looking to get the same functionality as the group_concat function in MySQL. Are the following two equivalent? If not, any suggestions for how I can recreate the group_concat functionality in Presto? MySQL: select …

Read More
LATERAL VIEW EXPLODE in presto

New to presto, any pointer how can I use LATERAL VIEW EXPLODE in presto for below table. I need to filter on names in my presto query CREATE EXTERNAL TABLE `id`( `id` string, `names` map<string,map<string,…

Read More