Onjsdev

Share


PostgreSQL array_agg Function


By onjsdev

Jan 21st, 2024

array_agg function in PostgreSQL combines multiple values from a column into a single array (list). It is useful for grouping and aggregating data when you need to collect multiple values within a single record.

Syntax

The array_agg function is as follows:

SELECT array_agg(column_name)
FROM table_name
[WHERE condition]
[GROUP BY group_column]

Example

In the example below, we retrieve information about customer purchases from an order table. This query will give us lists of each customer's purchases.

SELECT customer_id, array_agg(product_name) AS purchased_products
FROM orders
GROUP BY customer_id;

/*
customer_id | purchased_products
-------------+-----------------------
1           | {'Product A', 'Product C'}
2           | {'Product B', 'Product D'}
*/

Key points:

  • Order of values: The array elements are ordered according to their appearance in the original column.
  • Data types: Works with various data types like text, numbers, dates, etc.
  • Null values: Excludes null values from the resulting array.
  • Nesting: Can be nested within other aggregate functions or subqueries.

Conclusion

The array_agg function is a handy tool in PostgreSQL for aggregating values into arrays. It is especially useful when dealing with grouped data and you want to consolidate values into a more compact form. Keep in mind that it is just one of many powerful aggregation functions that PostgreSQL provides.

Thank you for reading.

SELECT AS AN ARRAY IN POSTGRESQL

Let's consider two tables - users and courses - that have a many-to-many relationship, where each user can be enrolled in multiple courses and each course can have multiple enrolled users.

To represent this relationship, we create a third table called user_courses that contains two foreign keys - user_id and course_id - that reference the id columns of the users and courses tables, respectively.

array_agg Function

To select rows as an array from these tables, we can use the array_agg function in PostgreSQL. The array_agg function aggregates values into an array and can be used in combination with the GROUP BY clause to group rows based on a specific column or columns.

Here's an example query that selects all users and their enrolled courses as an array:

SELECT users.name, ARRAY_AGG(courses.name) AS course_names
FROM users
JOIN user_courses ON users.id = user_courses.user_id
JOIN courses ON user_courses.course_id = courses.id
GROUP BY users.id;

The resulting output of the query will be a list of users and their enrolled courses, where the courses are represented as an array. We can also modify the query to select specific columns from the courses table, such as the id column, along with the name column, like this:

SELECT users.name, ARRAY_AGG(courses.id) AS course_ids, ARRAY_AGG(courses.name) AS course_names
FROM users
JOIN user_courses ON users.id = user_courses.user_id
JOIN courses ON user_courses.course_id = courses.id
GROUP BY users.id;

In this modified query, we select the id and name columns from the courses table, and aggregate them into separate arrays called course_ids and course_names, respectively. We can then use these arrays to access and manipulate the enrolled courses for each user.

Conclusion

In conclusion, selecting rows as an array from tables having many-to-many relationships in PostgreSQL can be requires. By using the array_agg function in with the GROUP BY clause, you can easily group and aggregate data into arrays and select them.

Thank you for reading.