PostgreSQL - ARRAY_AGG() Function

The PostgreSQL ARRAY_AGG function is a aggregate function that allows users to combine values from multiple rows into an array. This function is particularly useful when managing grouped data and returning multiple values in a single row.

In this article, We will learn about the What is PostgreSQL ARRAY_AGG by understanding various examples and so on.

What is PostgreSQL ARRAY_AGG?

• The ARRAY_AGG function in PostgreSQL is an aggregate function that combines values from multiple rows into an array.
  
• It is especially useful when we need to return multiple values in a single row or group values from related records.
  
• This function can be applied to a wide range of data types such as integers, strings, dates, and more.

Syntax of ARRAY_AGG:

The basic syntax of the ARRAY_AGG functions is as follows:

ARRAY_AGG(expression [ORDER BY sorting_expression])

Explanation:

• expression: This is the column or value that you want to aggregate into an array.
  
• ORDER BY (optional): Specifies the order in which values will be aggregated into the array.

Examples of PostgreSQL ARRAY_AGG() Function

Example 1: Basic Use of ARRAY_AGG

Suppose we have a table called employees with the following structure:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

INSERT INTO employees (name, department) VALUES
('Alice', 'HR'),
('Bob', 'IT'),
('Charlie', 'HR'),
('David', 'Finance'),
('Eve', 'IT');

Now, we want to group the employees by their department and return an array of employee names for each department.

SELECT department, ARRAY_AGG(name) AS employees
FROM employees
GROUP BY department;

Output:

Explanation:

• In this query, the ARRAY_AGG function aggregates the employee names for each department into an array.
• The GROUP BY clause ensures that names are grouped by department.

Example 2: Using ORDER BY with ARRAY_AGG

We can also specify the order in which values should appear in the array by using the ORDER BY clause.

SELECT department, ARRAY_AGG(name ORDER BY name DESC) AS employees
FROM employees
GROUP BY department;

Output:

Explanation:

• The ORDER BY name DESC orders the employee names in descending order within the array.

Example 3: Aggregating Integer Values

Let’s consider a sales table where we want to aggregate sales amounts into an array for each product.

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    amount INTEGER
);

INSERT INTO sales (product_name, amount) VALUES
('Product A', 200),
('Product A', 150),
('Product B', 300),
('Product B', 250),
('Product C', 100);

Now, aggregate the sales amounts for each product using ARRAY_AGG.

SELECT product_name, ARRAY_AGG(amount) AS sales_amounts
FROM sales
GROUP BY product_name;

Output:

Explanation:

• This query collects all sales amounts into an array for each product.

Example 4: Using DISTINCT with ARRAY_AGG

SELECT product_name, ARRAY_AGG(DISTINCT amount) AS unique_sales
FROM sales
GROUP BY product_name;

Output:

Explanation:

• The DISTINCT keyword ensures that duplicate sales amounts are excluded from the array.

Advantages of Using ARRAY_AGG

1. Data Aggregation: ARRAY_AGG simplifies the task of aggregating multiple values from rows into a single array, which is ideal for reporting and analytics.
   
2. Flexible Data Type Support: It works with various data types, including text, numbers, and dates.
   
3. Efficient Querying: You can use ARRAY_AGG to efficiently manage and retrieve grouped data with minimal effort.
   
4. Maintains Data Order: The optional ORDER BY clause allows for controlling the order of elements in the resulting array.

Important Points About ARRAY_AGG() Function in PostgreSQL

• ARRAY_AGG() in PostgreSQL is an aggregate function that collects a set of values into an array and returns that array as a result.
  
• ARRAY_AGG() effectively handles NULL values in aggregated columns, ensuring comprehensive data aggregation across rows.
  
• While primarily used in PostgreSQL, similar array aggregation functions are available in other SQL databases, each with its own syntax and capabilities.
  
• The ARRAY_AGG() function aggregates values specified by the expression into an array. Optional ORDER BY clause allows sorting of elements within the array based on specified criteria.

Conclusion

Overall, the PostgreSQL ARRAY_AGG function is an essential tool for aggregating data into arrays, especially when dealing with grouped datasets. Its ability to aggregate values efficiently, work with different data types, and allow for ordered data using the ORDER BY clause makes it a powerful asset for anyone working with PostgreSQL.