Arrays are essential data structures in any programming paradigm. Arrays allow us to store the data in contiguous memory locations. PostgreSQL offers an ARRAY data type that creates an array of any built-in or user-defined data types. However, arrays store data of the same data type. For instance, a string array contains text data only, an integer array contains integers only, etc.

To query the ARRAY data in Postgres, the SELECT statement is used in Postgres. You can use different operators to query the filtered data from an array based on specific conditions.

This post explains how to query an array’s data in Postgres using various practical examples.

How to Query an Array’s Data in Postgres?

We have created a sample table named “staff_data” whose details are shown in the following snippet:

SELECT * FROM staff_info;

Postgres allows us to query the data of an entire array or a specific index of an array.

Example 1: Querying Entire Array

To query an entire array, the SELECT statement can be used. For instance, in the following snippet, the SELECT statement is used to query the “st_email” array:

SELECT st_name, st_email
FROM staff_info;

Example 2: Querying Specific Index of an Array

Specify the index number of the array in the square bracket to query only a specific index of an array:

SELECT st_name, st_email[1]
FROM staff_info;

The above-given query will query/fetch only the first element of the selected array:

Example 3: Querying Specific Records

The WHERE clause can be used to fetch the array’s data based on a specific condition:

SELECT st_name
FROM staff_info
WHERE st_email[1] = 'mike@gmail.com';

The above statement will fetch the employee name whose email id at index 1 is 'mike@gmail.com':

The output signifies that the “SELECT” statement queries the filtered data only.

Example 4: Querying Array’s Data Using Built-in Operators

You can also use built-in operators like ANY, SOME, etc. to compare the array elements with some specific value. For instance, in the following snippet, we utilize the ANY operator to check if an employee has an email “john123@gmail.com”:

SELECT st_name
FROM staff_info
WHERE 'john123@gmail.com' = ANY(st_email);

The output shows that an employee named “John” has an email address “john123@gmail.com”.

Example 5: Expanding the Array’s Values in Postgres

The built-in UNNEST() function is used in Postgres to expand the selected array to multiple records. For instance, the following query will expand the “st_email” array to multiple rows:

SELECT st_id, st_name,
UNNEST(st_email)
FROM staff_info;

The UNNEST() function expanded the selected array into multiple rows.

Conclusion

To query the ARRAY data in Postgres, the SELECT statement is used. Postgres allows us to query the data of an entire array or a specific index of an array. Moreover, different built-in operators can be used to query the data from an array based on specific conditions. The built-in UNNEST() function is used in Postgres to expand the selected array to multiple records. This blog explained how to query array data using numerous examples.