The ARRAY_AGG() in PostgreSQL is a built-in aggregate function used for grouping and aggregating data. The ARRAY_AGG() function is an efficient and convenient way to aggregate data. It accepts a column as input and retrieves an array of values from all the rows in a group. It is useful for combining multiple values into a single data structure, such as when aggregating data from multiple rows.

This blog will explain what exactly the ARRAY_AGG() is and how to use it in Postgres. In this regard, the below-provided concepts will be covered in this post:

How to Use ARRAY_AGG() Function in Postgres?
- Example 1: How to Use ARRAY_AGG() Function on a Single Column?
- Example 2: How to Use ARRAY_AGG() Function With ORDER BY Clause in Postgres?
- Example 3: How to Use ARRAY_AGG() Function on Multiple Columns?
- Example 4: How to Use ARRAY_AGG() Function With WHERE Clause?
- Example 5: How to Use ARRAY_AGG() Function on Multiple Tables?

How to Use ARRAY_AGG() Function in Postgres?

The ARRAY_AGG() function in Postgres is an aggregate function that combines several values into a single array. It takes a column as input and returns an array of values from all the rows in the specified group:

ARRAY_AGG(expression [ORDER BY [sort_expression|col_name {ASC | DESC}], [....]);

Example 1: How to Use ARRAY_AGG() Function On a Single Column?

We have created a sample table named “employee_info” that contains the following data:

Let’s exercise the ARRAY_AGG() function to get the list of employee names:

SELECT ARRAY_AGG(e_name)
FROM employee_info;

The output shows that the ARRAY_AGG() function retrieves an array of employee names.

Example 2: How to Use ARRAY_AGG() Function With ORDER BY Clause?

In the previous example, we witnessed that the ARRAY_AGG() function retrieves an array of employee names. However, the array elements were not sorted in any order. To sort the array elements ascending or descending, we need to use the ORDER BY clause with the ARRAY_AGG() function:

SELECT ARRAY_AGG(e_name
ORDER BY e_name DESC)
FROM employee_info;

This time the ARRAY_AGG() function retrieves an array of employee names from the employee_info table in descending order.

Example 3: How to Use the ARRAY_AGG() Function on Multiple Columns?

We will implement the ARRAY_AGG() function on “e_id” and “e_name” columns using the concatenation operator:

SELECT ARRAY_AGG(e_id || ' - ' || e_name)
FROM employee_info;

This is how the ARRAY_AGG() function works on multiple columns.

Example 4: How to Use ARRAY_AGG() Function With WHERE Clause?

To get a filtered array using ARRAY_AGG() function, you must use the WHERE clause as follows:

SELECT ARRAY_AGG(e_id ||' - '|| e_name ||' - ' || e_email)
FROM employee_info
WHERE e_id > 3;

This time the ARRAY_AGG() function retrieves an array of only those employees whose id is greater than 3.

Example 5: How to Use ARRAY_AGG() Function on Multiple Tables?

We have created two sample tables: “employee_info” and “department_info”. Both these tables are linked with each other via a foreign key. The below snippets illustrate the table’s content:

The department_info table contains the following data:

Suppose we want to get an array of employee ids and names based on their respective departments. For this purpose, we will use the ARRAY_AGG() function with the help of INNER JOIN, as shown in the following snippet:

SELECT dpt_name, ARRAY_AGG(e_id ||'-' || e_name)
FROM employee_info
INNER JOIN department_info USING (e_id)
GROUP BY dpt_name;

In the above code snippet:

- Two columns are passed as the first argument to the ARRAY_AGG() function.
- The concatenation symbol is used to aggregate/combine the columns.
- The INNER JOIN joins the employee_info and department_info tables.
- The GROUP BY clause groups the table’s data with respect to the department name.

This way, you can use the ARRAY_AGG() function in Postgres.

Conclusion

Data aggregation in PostgreSQL is made easy and efficient with ARRAY_AGG(). The ARRAY_AGG() function in Postgres is an aggregate function that combines several values into a single array. It takes a column as input and returns an array of values from all the rows in the specified group. This blog explained various use cases of the ARRAY_AGG() function using examples.