Check If Map Or Presto Array Is Empty Or Contains
When working with array (indexable lists) or map (key-value tuple) complex types in Presto, it is useful to be able to check if map or array is empty. The examples below should enable you to check if a map or array is empty.
Maps
First, let’s create a prestomap from an array of key values:
presto> select map(array['taco','donut'], array[1,2]) as myMap;
myMap
-------------------
{donut=2, taco=1}
We can use the CARDINALITY() function to check if the map contains anything:
presto> select cardinality(map(array['taco','donut'], array[1,2])) = 0 as is_empty;
is_empty
----------
false
presto> select cardinality(map(array[], array[])) = 0 as is_empty;
is_empty
----------
true
We can also compare a map with an empty map() to test if it is empty:
presto> select (map(array['taco'], array['donut']) = map()) as is_empty;
is_empty
----------
false
Arrays
Again, we can use the CARDINALITY() function to check if an array contains anything. Here’s an example using an array with 3 elements:
presto> SELECT cardinality(ARRAY['Ahana', 'Cloud', 'Presto']) = 0 as is_empty;
is_empty
--------
false
presto> SELECT cardinality(ARRAY[]) = 0 as is_empty;
is_empty
--------
true
Another method to check if an array is empty is to compare it with array[] like this:
presto> SELECT (map_keys(map(ARRAY['Ahana'],ARRAY['Presto']))= array[]) as is_empty;
is_empty
----------
false
Tip: Be aware of null values. If an array contains nulls it is not considered to be empty:
presto> SELECT cardinality(ARRAY['', '', '']) = 0 as is_empty;
is_empty
--------
false
presto> SELECT cardinality(ARRAY[null,null,null]) = 0 as is_empty;
is_empty
--------
false
The above query examples should enable you to check if a map or array is empty.
If you want to get up and running with Presto, Ahana Cloud’s cloud managed service built for AWS is the easiest way to do that. See our docs for more details.