prestotrino

Checking if key exists in Presto value map

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?

Solution

  • 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