How to Query Arrays

An interesting way to query an array in Postgres is by using the function unnest which allows to expand the elements of an array as rows in a result set. Let’s an example:

Let’s remove the element ‘banana’ from the array

select unnest(fruits) AS fruit_name from array_example;
fruit_name
grape
apple
orange
peach

Once we have the array expanded in a set of rows, we can use all the clauses availables in a SQL query to query the array. For example if you want to obtain all the elements in the array ending in “e” we can do:

with fruits as ( select unnest(fruits) AS fruit_name from array_example )
select fruit_name from fruits where fruit_name like '%e';
fruit_name
grape
apple
Orange

IN THIS PAGE