Array aggregation with ARRAY_AGG in PostgreSQL

Introduction

ARRAY_AGG helps you collect multiple values from rows into a single list. It makes it easy to see related data together.

You want to list all the products bought by each customer in one row.

You need to gather all comments made on a blog post into one array.

You want to group all tags related to an article into a single list.

You want to combine all phone numbers of a person into one array.

Syntax

PostgreSQL

ARRAY_AGG(expression [ORDER BY expression])

The expression is the column or value you want to collect into an array.

You can use ORDER BY inside ARRAY_AGG to sort the values in the array.

Examples

This groups employee names by their department into arrays.

PostgreSQL

SELECT department, ARRAY_AGG(employee_name) FROM employees GROUP BY department;

This groups employee names by department and sorts the names alphabetically inside the array.

PostgreSQL

SELECT department, ARRAY_AGG(employee_name ORDER BY employee_name) FROM employees GROUP BY department;

This collects all product names in the 'Books' category into one array.

PostgreSQL

SELECT ARRAY_AGG(product_name) FROM products WHERE category = 'Books';

Sample Program

This example creates an orders table, adds some orders, then groups products bought by each customer into arrays sorted alphabetically.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_name TEXT,
  product_name TEXT
);

INSERT INTO orders (customer_name, product_name) VALUES
('Alice', 'Book'),
('Alice', 'Pen'),
('Bob', 'Notebook'),
('Bob', 'Pen'),
('Bob', 'Eraser');

SELECT customer_name, ARRAY_AGG(product_name ORDER BY product_name) AS products_bought
FROM orders
GROUP BY customer_name
ORDER BY customer_name;

Important Notes

ARRAY_AGG runs in O(n) time where n is the number of rows in the group.

It uses extra space to store the array for each group.

Common mistake: forgetting to use GROUP BY when aggregating, which causes errors or unexpected results.

Use ARRAY_AGG when you want to collect multiple values into one list. Use string_agg if you want a single string instead.

Summary

ARRAY_AGG collects multiple row values into a single array.

You can sort the array elements using ORDER BY inside ARRAY_AGG.

It is useful for grouping related data together in one row.