> This page location: PostgreSQL Aggregate Functions > ARRAY_AGG
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL ARRAY_AGG Function

**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](../postgresql-tutorial/postgresql-array) in which each value in the set is assigned to an element of the array.

The following shows the syntax of the `ARRAY_AGG()` function:

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

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

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

Similar to other aggregate functions such as [`AVG()`](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-avg-function), [`COUNT()`](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-count-function), [`MAX()`](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-max-function), [`MIN()`](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-min-function), and [`SUM()`](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-sum-function), the `ARRAY_AGG()` is often used with the [`GROUP BY`](../postgresql-tutorial/postgresql-group-by) clause.

## PostgreSQL ARRAY_AGG() function examples

We will use the `film`, `film_actor`, and `actor` tables from the [sample database](../postgresql-getting-started/postgresql-sample-database) for the demonstration.

### 1) 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 titles and a list of actors for each film:

```sql
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:

| title            | actors                                                                 |
| ---------------- | ---------------------------------------------------------------------- |
| Academy Dinosaur | \{"Rock Dukakis","Mary Keitel","Johnny Cage","Penelope Guiness",...}   |
| Ace Goldfinger   | \{"Minnie Zellweger","Chris Depp","Bob Fawcett","Sean Guiness",...}    |
| Adaptation Holes | \{"Cameron Streep","Bob Fawcett","Nick Wahlberg","Ray Johansson",...}  |
| Affair Prejudice | \{"Jodie Degeneres","Kenneth Pesci","Fay Winslet","Oprah Kilmer",...}  |
| African Egg      | \{"Dustin Tautou","Matthew Leigh","Gary Phoenix","Matthew Carrey",...} |

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 BY` clause in the `ARRAY_AGG()` function.

### 2) Using PostgreSQL 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:

```sql
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:

![](https://neon.com/postgresqltutorial/PostgreSQL-ARRAY_AGG-with-ORDER-BY-clause.png)
You can sort the actor list for each film by the actor's first name and last name as shown in the following query:

```sql
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:

![](https://neon.com/postgresqltutorial/PostgreSQL-ARRAY_AGG-with-ORDER-BY-clause-example-2.png)

## Summary

- Use the PostgreSQL `ARRAY_AGG()` function to return an array from a set of input values.

---

## Related docs (PostgreSQL Aggregate Functions)

- [AVG](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-avg-function)
- [BOOL_AND](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-bool_and)
- [BOOL_OR](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-bool_or)
- [COUNT](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-count-function)
- [MAX](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-max-function)
- [MIN](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-min-function)
- [STRING_AGG](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-string_agg-function)
- [SUM](https://neon.com/postgresql/postgresql-aggregate-functions/postgresql-sum-function)