Introduction to PostgreSQL ARRAY_AGG()
The PostgreSQL provides various aggregate functions; the PostgreSQL ARRAY_AGG() aggregate function is used to get an array that will have each value of the input set added an element of the array. This aggregate function accepts a set of values as input, and the function includes NULL values into the array while concatenating the input values. We can use the ORDER BY clause with the PostgreSQL ARRAY_AGG() aggregate function in order to sort the result.
Syntax
Consider the following syntax of the PostgreSQL ARRAY_AGG() aggregate function:
ARRAY_AGG(input_expression [ORDER BY [sort_expression {ASC | DESC}], [...])
- ARRAY_AGG(): The PostgreSQL ARRAY_AGG() aggregate function takes the number of values as an input and then returns an array.
- ORDER BY: This is an optional clause. This clause is used when we want the results sorted, which are processed in the aggregation, which results to sort the elements in the result array.
Examples to Implement PostgreSQL ARRAY_AGG()
We will create two tables of name ‘student’ and ‘department’ by using the CREATE TABLE statement as follows in order to understand the examples:
create table student
(
stud_id serial PRIMARY KEY,
stud_fname VARCHAR(80) NOT NULL,
stud_lname VARCHAR(80) NOT NULL,
department_id int NOT NULL
);
create table department
(
department_id serial PRIMARY KEY,
department_name VARCHAR(80) NOT NULL
);
Now, we will insert some data into the department table by using the INSERT INTO statement as follows.
INSERT INTO department(department_name)
VALUES
('Computer'),
('Electrical'),
('IT'),
('Civil'),
('Chemical'),
('Mechanical');
Illustrate the result of the above INSERT INTO statement by using the following SQL statement and snapshot.
Code:
select * from department;
Output:
Now, we will insert some data into the student table by using the INSERT INTO statement as follows.
INSERT INTO student(stud_fname, stud_lname, department_id)
VALUES
('Smith','Johnson',1),
('Williams','Jones',1),
('Harper','James',2),
('Jack','Liam',2),
('Harry','Mason',3),
('Jacob','Oscar',3),
('Michael','Charlie',4),
('William','Joe',4),
('Oliver','John',5),
('Jack','Richard',5),
('Harry','Joseph',5),
('George','Thomas',6),
('Brown','Charles',6);
Illustrate the result of the above INSERT INTO statement by using the following SQL statement and snapshot.
Code:
select * from student;
Output:
Example #1
Without ORDER BY clause in PostgreSQL ARRAY_AGG() aggregate function Consider the following SQL statement, which will use the function to return the list of names of the department and the list of names of the students studying in each department:
Code:
SELECT
department_name,
ARRAY_AGG (stud_fname || ' ' || stud_lname) students
FROM
department
INNER JOIN student USING (department_id)
GROUP BY
department_name
ORDER BY
Department_name;
Illustrate the result of the above SQL statement by using the following snapshot.
From the above example, we can see that each department’s students are randomly ordered; to sort the students by their last name or first name, we have to define the ORDER BY clause in the PostgreSQL ARRAY_AGG() aggregate function.
Example #2
ORDER BY clause with PostgreSQL ARRAY_AGG() aggregate function
Consider the following example to get the list of students for each department, which are sorted by the student’s first name as shown in the following SQL statement:
Code:
SELECT
department_name,
ARRAY_AGG (
stud_fname || ' ' || stud_lname
ORDER BY
stud_fname
) students
FROM
department
INNER JOIN student USING (department_id)
GROUP BY
department_name
ORDER BY
department_name;
Illustrate the result of the above SQL statement by using the following snapshot.
Example #3
ORDER BY clause with PostgreSQL ARRAY_AGG() aggregate Function
Consider the following example to sort the list of students for each department by student’s first name and last name as shown in the following SQL statement:
Code:
SELECT
department_name,
ARRAY_AGG (
stud_fname || ' ' || stud_lname
ORDER BY
stud_fname ASC,
stud_lname DESC
) student
FROM
department
INNER JOIN student USING (department_id)
GROUP BY
department_name
ORDER BY
department_name;
Illustrate the result of the above SQL statement by using the following snapshot.
Conclusion
We hope from the above article you have understood how to use the PostgreSQL ARRAY_AGG() aggregate function and how the aggregate function works. Also, we have added several examples of the aggregate functions to understand them in detail.
Recommended Articles
This is a guide to PostgreSQL ARRAY_AGG(). Here we discuss an introduction to PostgreSQL ARRAY_AGG() with syntax and examples. You can also go through our other related articles to learn more –
