Introduction to PostgreSQL ARRAY_AGG() function

The PostgreSQLARRAY_AGG() function is an aggregate function that accepts a set of values and returns anarray in which each value in the set is assigned to an element of the array.

The following shows the syntax of theARRAY_AGG() function:

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

TheARRAY_AGG() accepts an expression that returns a value of any type that is valid for an array element.

TheORDER BY clause specifies the order of rows processed in the aggregation, which determines the order of the elements in the result array. TheORDER BY clause is optional.

Similar to other aggregate functions such asAVG(),COUNT(),MAX(),MIN(), andSUM(), theARRAY_AGG() is often used with theGROUP BY clause.

PostgreSQL ARRAY_AGG() function examples

We will use thefilm,film_actor, andactor tables from thesample database for the demonstration.

1) Using PostgreSQL ARRAY_AGG() function without the ORDER BY clause example

The following example uses theARRAY_AGG() function to return the list of film titles and a list of actors for each film:

SELECT    title,    ARRAY_AGG (first_name|| ' ' || last_name) actorsFROM    filmINNER JOIN film_actorUSING (film_id)INNER JOIN actorUSING (actor_id)GROUP BY    titleORDER 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 theORDER BY clause in theARRAY_AGG() function.

2) Using PostgreSQL ARRAY_AGG() function with the ORDER BY clause example

This example uses theARRAY_AGG() function to return a list of films and a list of actors for each film sorted by the actor’s first name:

SELECT    title,    ARRAY_AGG (        first_name|| ' ' || last_name        ORDER BY            first_name    ) actorsFROM    filmINNER JOIN film_actorUSING (film_id)INNER JOIN actorUSING (actor_id)GROUP BY    titleORDER BY    title;

The following shows the partial output:

You can sort the actor list for each film by the actor’s first name and last name as shown in the following query:

SELECT    title,    ARRAY_AGG (        first_name|| ' ' || last_name        ORDER BY            first_nameASC,            last_nameDESC    ) actorsFROM    filmINNER JOIN film_actorUSING (film_id)INNER JOIN actorUSING (actor_id)GROUP BY    titleORDER BY    title;

This picture shows the partial output of the query:

Summary

• Use the PostgreSQLARRAY_AGG() function to return an array from a set of input values.

Last updated onJanuary 26, 2024

On this page

• Introduction to PostgreSQL ARRAY_AGG() function
• PostgreSQL ARRAY_AGG() function examples
• Summary