PostgreSQL ARRAY_AGG(), STRING_ADD

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');

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);

Code:

select * from student;

Output:

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;

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.

with 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;

STRING_AGG() function

The PostgreSQL STRING_AGG() function is an aggregate function that concatenates a list of strings and places a separator between them. The function does not add the separator at the end of the string.

The following shows the syntax of the STRING_AGG() function:

STRING_AGG ( expression, separator [order_by_clause] )

The STRING_AGG() function accepts two arguments and an optional ORDER BY clause.

• expression is any valid expression that can resolve to a character string. If you use other types than character string type, you need to explicitly cast these values of that type to the character string type.
• separator is the separator for concatenated strings.

The order_by_clause is an optional clause that specifies the order of concatenated results. It has the following form:

ORDER BY expression1 {ASC | DESC}, [...]Code language: SQL (Structured Query Language) (sql)

The STRING_AGG() is similar to the ARRAY_AGG() function except for the return type. The return type of the STRING_AGG() function is the string while the return type of the ARRAY_AGG() function is the array.

Like other aggregate functions such as AVG(), COUNT(), MAX(), MIN(), and SUM(), the STRING_AGG() function is often used with the GROUP BY clause.

PostgreSQL STRING_AGG() function examples

We will use the film, film_actor, and actor tables from the sample database for the demonstration.

A) Using STRING_AGG() function to generate a list of comma-separated values

This example uses the STRING_AGG() function to return a list of actor’s names for each film from the film table:

SELECT
    f.title,
    STRING_AGG (
	a.first_name || ' ' || a.last_name,
        ' ~ '
       ORDER BY
        a.first_name,
        a.last_name
    ) actors
FROM
    film f
INNER JOIN film_actor fa USING (film_id)
INNER JOIN actor a USING (actor_id)
GROUP BY
    f.title;

Here is the partial output:

B) Using STRING_AGG() function to generate a list of emails

The following example uses the STRING_AGG() function to build an email list for each country. The email in each list separated by a semi-colon.

SELECT
    country,
    STRING_AGG (email, ';') email_list
FROM
    customer
INNER JOIN address USING (address_id)
INNER JOIN city USING (city_id)
INNER JOIN country USING (country_id)
GROUP BY
    country
ORDER BY
    country;

The following picture shows the partial output:

reference: https://www.educba.com/postgresql-array_agg/