problem

I've been writing a lot of sql queries against PrestoDB recently. One thing that came up was I needed to find rows where a Map field was non-empty, where non-empty meant that it contained at least one key. Here I'll show how I solved for this.

How do you get the number of keys in a Map in PrestoSQL / PrestoDB?

solution

To get the number of keys in a Map, we'll use the same cardinality function that we use for finding the length of an array in Presto and pass it the array of keys from our map.

To get the keys from our map, we can use the MAP_KEYS function. This function takes in a Map and returns an array of keys.

We can use this like MAP_KEYS(my_map).

The cardinality function takes in an array and outputs the length or size of the array. So putting cardinality together with MAP_KEYS, we could find rows where our map is non-empty by doing something like this

SELECT my_map
FROM my_table
WHERE CARDINALITY(MAP_KEYS(my_map)) > 0

This should return all rows where my_map has at least one key in it.