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.