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.