Open In App

PostgreSQL – ARRAY_AGG() Function

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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:


Last Updated : 01 Jun, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads