Presto: How to get the length of an array

Published: 2020-08-24 17:13:11 +0000 UTC

Reading time: 1 mins (150 words)

Tags: presto prestodb prestosql sql

problem

I’ve been doing a lot of sql queries on PrestoDB recently and just ran into Arrays. For one query I needed to see if an array was empty or not. To do that I needed to find the length of the array and see if it was greater than 0.

So my question - How do I get the length of an array in PrestoDB / PrestoSQL?

solution

In order to get the length or size of an array in Presto, you can use the cardinality function. cardinality takes in an array and will output the size or length of an array.

To solve my issue where I was looking for rows where the array field was non-empty, I could do something like this:

SELECT my_array
FROM my_table
WHERE CARDINALITY(my_array) > 0

Running this should give me all the rows in my table where my_array is a non-empty array.

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