Presto: How to get the number of keys in a Map

Published: 2020-09-05 17:20:45 +0000 UTC

Reading time: 1 mins (189 words)

Tags: presto prestodb prestosql sql

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.

About the Ham

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

comments powered by Disqus