How to check if Presto array or map contains values

In Presto, it is important to be able to determine if complex types like array (indexable lists) or map (key-value tuple) are empty. The following examples will demonstrate various methods to verify if a map or array is empty in Presto.

Check if a Presto map is empty

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 

In the code above, the first query creates a presto map by pairing keys and their corresponding values from two arrays.

The second query uses the “cardinality” function to check if the map created in the first query has any key-value pairs. Since the map is not empty, the result of the query is “false”.

The third query creates an empty map using two empty arrays as arguments to the “map” function. Then, the “cardinality” function is used to check if this empty map contains any key-value pairs. Since there are no key-value pairs in the empty map, the result is “true”.

The final query compares a non-empty map (created with one key-value pair) to an empty map using the “=” operator. Since the two maps are not equal, the result is “false”.

Check if a Presto array contains values

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  

In the code above, the first query creates a presto map by pairing keys and their corresponding values from two arrays.

The second query uses the “cardinality” function to check if the map created in the first query has any key-value pairs. Since the map is not empty, the result of the query is “false”.

The third query creates an empty map using two empty arrays as arguments to the “map” function. Then, the “cardinality” function is used to check if this empty map contains any key-value pairs. Since there are no key-value pairs in the empty map, the result is “true”.

The final query compares a non-empty map (created with one key-value pair) to an empty map using the “=” operator. Since the two maps are not equal, the result is “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.