Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

PostgreSQL – ARRAY_AGG() Function

  • Last Updated : 01 Jun, 2020

PostgreSQL ARRAY_AGG() function is an aggregate function that accepts a set of values and returns an array where each value in the input set is assigned to an element of the array.

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

The ORDER BY clause is an voluntary clause. It specifies the order of rows that are processed in the collection, which establishes the order of the elements in the result array. It is often used with the GROUP BY clause.
Now let’s look into some examples.
Example 1:
We will be using the film, film_actor and actor tables in the dvdrental sample database for demonstration. In this example we will query for the list of film title and a list of actors for each film using the ARRAY_AVG() function as follows:

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;

Output:

Example 2:
here we will use 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 using the commands below:

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;

Output:

My Personal Notes arrow_drop_up
Recommended Articles
Page :