Checking if key exists in Presto value map

Question

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?

If the column type is a `varchar`, then it’s a string, not a map. Can you show some example values for the column? — David Phillips, Mar 29 '19 at 22:38
There is a typographical error -- `SELECT typeof(value_map)` would show the type of the `value_map`. `SELECT typeof('value_map')` shows the type of the `'value_map'` string literal. — Piotr Findeisen, Mar 29 '19 at 22:44

score 36 · Accepted Answer · answered Mar 29 '19 at 22:47

36

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

answered Mar 29 '19 at 22:47

Piotr Findeisen

19,480
2
52
82

score 5 · Answer 2 · answered Feb 07 '22 at 18:55

element_at is ambiguous in that case -- it'll return NULL when either there's no such key or the key does exist and has NULL associated with it. A guaranteed approach is contains(map_keys(my_map), 'mykey'), which admittedly should be a bit slower than the original variant.

Checking if key exists in Presto value map

2 Answers2