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?
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
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.
Hi I'm Hamilton and I wrote / built this! I like doing lots of things, but mostly I like to build (and then share those buildings). Some things I'm currently building: art, software, and prose. To stay up-to-date with my latest buildings, subscribe to my email list or follow one of my many other accounts. #buildit #shipit #justhamit