Summary: in this tutorial, you will learn how to use the PostgreSQL ARRAY_AGG() aggregate function to return an array from a set of input values.
Introduction to PostgreSQL ARRAY_AGG() function
The PostgreSQL ARRAY_AGG() function is an aggregate function that accepts a set of values and returns an array in which each value in the input set is assigned to an element of the array.
The following shows the syntax of the ARRAY_AGG() function:
1 | ARRAY_AGG(expression [ORDER BY [sort_expression {ASC | DESC}], [...]) |
The ARRAY_AGG() accepts an expression that returns a value of any type which is valid for an array element.
The ORDER BY clause is an optional clause. It specifies the order of rows that are processed in the aggregation, which determines the order of the elements in the result array.
Similar to other aggregate functions such as AVG(), COUNT(), MAX(), MIN(), and SUM(), the ARRAY_AGG() is often used with the GROUP BY clause.
PostgreSQL ARRAY_AGG() function examples
We will use the film, film_actor, and actor tables from the sample database for the demonstration.
Using PostgreSQL ARRAY_AGG() function without the ORDER BY clause example
The following example uses the ARRAY_AGG() function to return the list of film title and a list of actors for each film:
1 2 3 4 5 6 7 8 9 10 11 | SELECT title, ARRAY_AGG (first_name || ' ' || last_name) actors FROM film INNER JOIN film_actor USING (film_id) INNER JOIN actor USING (actor_id) GROUP BY title ORDER BY title; |
Here is the partial output: 
As you can see, the actors in each film are arbitrarily ordered. To sort the actors by last name or first name, you can use the ORDER BYclause in the ARRAY_AGG() function.
Using PostgreSLQ ARRAY_AGG() function with the ORDER BY clause example
This example uses the ARRAY_AGG() function to return a list of films and a list of actors for each film sorted by the actor’s first name:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT title, ARRAY_AGG ( first_name || ' ' || last_name ORDER BY first_name ) actors FROM film INNER JOIN film_actor USING (film_id) INNER JOIN actor USING (actor_id) GROUP BY title ORDER BY title; |
The following shows the partial output:
You can sort the actor list for each film by actor’s first name and last name as shown in the following query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT title, ARRAY_AGG ( first_name || ' ' || last_name ORDER BY first_name ASC, last_name DESC ) actors FROM film INNER JOIN film_actor USING (film_id) INNER JOIN actor USING (actor_id) GROUP BY title ORDER BY title; |
This picture shows the partial output of the query:
In this tutorial, you have learned how to use the PostgreSQL ARRAY_AGG() function to return an array from a set of input values.