# Using array\_agg in Postgres - powerful and flexible

DevFeed: [Using array\_agg in Postgres - powerful and flexible](<https://devfeed.tech/articles/using-array-agg-in-postgres-powerful-and-flexible-41134.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2013/04/17/Using-array_agg-in-Postgres-powerful-and-flexible/>)

Author: Map

Published: 2013-04-17T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>), [function](<https://devfeed.tech/topics/function.md>)

Tags: [aggregate](<https://devfeed.tech/tags/aggregate.md>), [array](<https://devfeed.tech/tags/array.md>), [array-agg](<https://devfeed.tech/tags/array-agg.md>), [email](<https://devfeed.tech/tags/email.md>), [functions](<https://devfeed.tech/tags/functions.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [query](<https://devfeed.tech/tags/query.md>)

## AI overview

This tutorial explains how to use PostgreSQL arrays and the array_agg function to aggregate project and task data into formatted results, reducing the need to group query results in application code. It demonstrates the approach with a project-management email example.

## Source excerpt

In almost any application it's common to want to aggregate some set of values together, commonly in a comma separated form. Most developers do this by running a query to get much of the raw data, looping over the data and pushing it into a set, appending each new value to the appropriate key. Hopefully, it's not a surprise that there's a much better way to do this with PostgreSQL. Postgres has a flexible and robust array datatype that comes with a variety of functions. Even without taking advantage of the array datatype in your application, you can still take advantage of some of the functions to get the functionality you need. Lets take a look at an example schema and use case. An example Given a project management application, you may have users who have projects that have tasks. An example piece of functionality might be to send an email with a list of all projects that have tasks that are past their due dates of completion. Your schema might look something like this: # \d users Table "public.users" Column | Type | Modifiers ------------+-----------------------------+----------- id | integer | not null email | character varying(255) | ... # \d projects Table "public.projects" Column | Type | Modifiers ------------+-----------------------------+----------- id | integer | not null user_id | integer | not null name | character varying(255) | not null ... # \d tasks Table "public.tasks" Column | Type | Modifiers --------------+-----------------------------+----------- id | integer | not null project_id | integer | not null completed_at | timestamp without time zone | due_at | timestamp without time zone | ... To get a list of all projects that have tasks that haven't been completed, you would start with something like: SELECT projects.name FROM projects, tasks WHERE projects.id = tasks.project_id AND tasks.due_at > tasks.completed_at AND tasks.due_at > now() This would give you a list of projects which you could then easily join this with users: SELECT users.email pr