intro
Discover all the possible ways to find elements in a PostgreSQL array or check if it contains one or more elements!
As you might already know, PostgreSQL supports arrays. This special data type allows you to store multiple values in a single column as an array. When working with arrays, one of the most common operations is searching for elements within them. Let's explore PostgreSQL find in array approaches!
What Is a PostgreSQL Array?
In PostgreSQL, an array is a data type to store multiple values in a single column. An array can store data of various data types, including integers, text, and more. For more information, refer to our guide on the PostgreSQL data types.
The syntax to add an array column in Postgres is as follows (note the [] addition):
1
ALTER TABLE your_table_name
2
ADD COLUMN tags text[];
This adds a tags column that can store an array of strings.
How to Find One or More Elements in a PostgreSQL Array
Time to discover the most effective PostgreSQL find in array approaches!
The following queries will be executed using DbVisualizer, a top-rated database client with full PostgreSQL support. Some of its features will also come in handy to explore the table to run the queries on.
The following examples will refer to the books table below:
As you can see from the DbVisualizer’s Data view, the table contains several book records. In particular, the column of interest is the string array named genres. Here is the SQL DDL definition of the bookstable, as retrieved via DbVisualizer in the DDL view:
Notice how the genres column is of type TEXT[].
Now we will explore techniques that you can use to get elements from an array in PostgreSQL.
Note: The techniques shown below work with PostgreSQL arrays of any data type.
By Index
The easiest way to get elements from an array in PostgreSQL is by accessing them using their index. For example:
1
SELECT id, title, genres[1] FROM books;
In this query, the genres[1] expression extracts the first element of the genres array from each row:
Contains Operator
@>—also known as the PostgreSQL array “contains” operator—allows you to verify if an array contains one or more elements.
For example, suppose you want to retrieve all books where the genres column contains the string “Adventure.” In other words, you want to get all adventure-related books. You can achieve that with the @> operator as follows:
1
SELECT * FROM books
2
WHERE genres @> ARRAY['Adventure'];
Or, equivalently:
1
SELECT * FROM books
2
WHERE genres @> '{"Adventure"}';
Execute one of these queries, and the result will be all books that belong to the "Adventure" genre:
All selected books are adventure books
In detail, @> returns true if the array contains the specified value(s) and false otherwise.
You can also use @> to check if an array contains multiple values. For example, to find books that belong to both "Adventure" and "Suspense" genres, use the following query:
1
SELECT * FROM books
2
WHERE genres @> ARRAY['Adventure', 'Suspense'];
Or, equivalently:
1
SELECT * FROM books
2
WHERE genres @> '{"Adventure", "Suspense"}';
The above queries will return all books that have both "Adventure" and "Suspense" as genres:
“Is Contained By” Operator
To check if an array contains one or more values from another array, you can utilize the <@ operator, known as the "is contained by" PostgreSQL array operator.
For example, suppose you want to get all books with genres that include both "Adventure" and "Suspense." You can achieve that using the <@ operator as follows:
1
SELECT * FROM books
2
WHERE ARRAY['Adventure', 'Suspense'] <@ genres;
Or, equivalently:
1
SELECT * FROM books
2
WHERE '{"Adventure", "Suspense"}' <@ genres;
This query produces the same result as the @> query seen earlier. The main difference is the order of the operands in the WHERE condition.
Overlap Operator
The PostgreSQL && array operator, also known as the "overlap" operator, helps you make sure if two arrays share any common elements. This operator is particularly useful when you want to find rows where an array column contains one or more elements from a given set.
Now, suppose you want to find all books that have either "Horror" or "Crime" as one of the genres. To reach that goal, you can use the && operator as below:
1
SELECT * FROM books
2
WHERE genres && ARRAY['Horror', 'Crime'];
Or, equivalently:
1
SELECT * FROM books
2
WHERE genres && '{"Horror", "Crime"}';
Execute one of the above queries, and the result will be as follows:
ANY/SOME Function
ANY—or equivalently SOME—is a PostgreSQL function to verify if any element in an array meets a specific condition.
For example, you can use ANY to retrieve all books where the genres array contains the genre “Adventure” like so:
1
SELECT * FROM books
2
WHERE 'Thriller' = ANY(genres);
The result will be:
If at least one element in the array matches the specified condition, the row is returned.
array_position Function
The array_position function returns the index of a specific element within an array, if present. Otherwise, it returns NULL.
If you want to find rows that contain the genre "Romance," execute the following query:
1
SELECT *
2
FROM books
3
WHERE array_position(genres, 'Romance') IS NOT NULL;
array_position function searches for "Romance" within the genres column of the books table. If the returned index is not NULL (i.e., genres contains the specified value), the row is selected.
The result will be:
unnest Function
The unnest function expands an array into a set of rows, enabling you to apply standard SQL search conditions on the resulting set. You can also use this result to filter records in the main query, as in this example:
1
SELECT *
2
FROM books
3
WHERE 'Thriller' IN (SELECT unnest(genres));
The above query searches for all books that contain the "Thriller" genre in the genres array.
Other Approaches
PostgreSQL provides native array operators and functions for searching within arrays, but you can also follow other approaches.
For example, the array_to_string function converts an array into a string. Then, you can leverage string functions like SQL LIKE, ILIKE, or even regular expressions to find elements within that array.
Now, suppose you want to get all books that have "Suspense" as a genre. Do that with the array_to_string function combined with LIKE:
1
SELECT *
2
FROM books
3
WHERE array_to_string(genres, ',') LIKE '%Suspense%';
array_to_string(genres, ',') converts the genres array into a single string, with each element separated by a comma. Then, LIKE '%Suspense%' searches the resulting string for the specified string. If "Suspense" is found anywhere in the string, the row is returned, as you can see below:
The problem with this approach is that it would also select books with genres like "Suspense-Thriller." That is because the genre string contains the word "Suspense," as specified in the LIKE condition. Thus, use array_to_string cautiously in PostgreSQL find in array scenarios. Also, keep in mind that converting all arrays to strings takes time and may make your queries slow.
Conclusion
In this guide, you learned how to access elements, search for elements, and check if specific elements are present in an array in PostgreSQL. You are now a master of PostgreSQL find in array approaches!
As demonstrated, working with arrays becomes easier with a powerful and visual database client, and this is where DbVisualizer comes in!
DbVisualizer is a robust database client that supports multiple DBMS technologies and is equipped with advanced features such as query optimization, SQL formatting, and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
Is it possible to search for data in an array in Postgres?
Yes, you can use built-in operators and functions for PostgreSQL find in array implementations. For instance, you can take advantage of array operators like @> (contains) and <@ (is contained by), along with functions such as ANY(), unnest(), and array_position().
How to find an element in a JSON array in PostgreSQL?
There are a few approaches you can follow to find an element in a JSON array in PostgreSQL. First, you can use jsonb_array_elements` to expand a JSONB array into a set of rows and search in them with a subquery:
1
SELECT *
2
FROM books
3
WHERE EXISTS (
4
SELECT 1
5
FROM jsonb_array_elements(genres) AS genre
6
WHERE genre = '"Adventure"'
7
);
Find out more about SQL subqueries.
Second, you can use the @> with a JSON array on the right:
1
SELECT *
2
FROM books
3
WHERE genres @> '["Adventure"]';
Third, you can utilize a JSONPATH expression as below:
1
SELECT *
2
FROM books
3
WHERE genres @? '$[*] == "Adventure"';
For more information, read our guide on PostgreSQL JSONPATH.
Why should I use an SQL client?
Using an SQL client, such as DbVisualizer, is beneficial because it enables you to visualize your data, making your development tasks easier. A good SQL client offers a variety of tools that simplify data management and analysis, regardless of the database management system you're working with. These features enhance productivity, streamline workflows, and help you gain insights from your data more effectively.
