PostgreSQL – ARRAY_AGG() Function
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
Share your thoughts in the comments
Please Login to comment...