How to Use PostgreSQL ARRAY_AGG Function?

The ARRAY_AGG() aggregate method is one of the methods used in PostgreSQL, which takes several input values and concatenates them into an array, including NULL values. It returns an array with every value from the input group as a part. To sort the result set via the PostgreSQL ARRAY_AGG function, you will be using the ORDER BY phrase. You can also use the WHERE clause when required.

To understand the aggregate ARRAY_Agg() method, you have to perform several examples. For this purpose, open the PostgreSQL command-line shell. If you want to switch on the other Server, do so by providing its name. Otherwise, leave the space empty and hit the Enter button to jump on Database. If you want to use the default database, e.g., Postgres, then leave it as is and press Enter; otherwise, write the name of a database, e.g., “test”, as shown in the image below. If you want to use another port, write it out, otherwise just leave it as it is and tap Enter to continue. It will ask you to add the username if you want to switch to another username. Add the username if you want , otherwise, just press “Enter”. In the end, you have to provide your current user password, to start using the command-line using that particular user as below. After successful entries of all the required information, you are good to go.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/How-to-Use-PostgreSQL-ARRAY_AGG-Function_1.jpg" data-lazy- height="194" src="data:image/svg xml,” width=”629″>

Use Of ARRAY_AGG On Single Column:

Consider the table “person” in the database “test” having three columns; “id”, “name”, and “age”. The column “id” has the ids of all the persons. While the field ‘name’ contains the names of the persons and the column ‘age’ the ages of all the persons.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/How-to-Use-PostgreSQL-ARRAY_AGG-Function_2.jpg" data-lazy- height="146" src="data:image/svg xml,” width=”323″>

Depending on the overhead table, we have to apply the aggregate ARRAY_AGG method to return the list of array of all the names of the table via column “name”. With this, you have to use the ARRAY_AGG() function in the SELECT query to fetch the result in the form of an array. Try the stated query in your command shell and get the result. As you can see, we have the below output column “array_agg” having names listed in an array for the very same query.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/How-to-Use-PostgreSQL-ARRAY_AGG-Function_3.jpg" data-lazy- height="86" src="data:image/svg xml,” width=”376″>

Use Of ARRAY_AGG On Multiple Columns With ORDER BY Clause:

Example 01:

Applying the ARRAY_AGG function to multiple columns while using the ORDER BY clause, consider the same table “person” within the database “test” having three columns; “id”, “name”, and “age”. In this example, we will be using the GROUP BY clause.

<img alt="" data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/How-to-Use-PostgreSQL-ARRAY_AGG-Function_43.jpg" height="148" src="data:image/svg xml,” width=”269″>

We have been concatenating the SELECT query result in an array list while using the two columns “name” and “age”. In this example, we have been using space as a special character which has been used to concatenate both of these columns so far. On the other hand, we have been fetching the column “id” separately. The concatenated array result will be shown in a column “persondata” at run time. The result set will be first grouped by the “id” of the person and sorted in ascending order of field “id”. Let’s try the below command in the shell and see the results yourself. You can see we have got a separate array for every name-age concatenated value in the image below.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/How-to-Use-PostgreSQL-ARRAY_AGG-Function_5.jpg" data-lazy- height="162" src="data:image/svg xml,” width=”524″>

Example 02:

Consider a newly created table “Employee” within the database “test” having five columns; “id”, “name”, “salary”, “age”, and “email”. The table stores all the data about the 5 Employees working in a company. In this example, we will be using the special character ‘-‘ to concatenate two fields instead of using space while utilizing the GROUP BY and ORDER BY clause.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/How-to-Use-PostgreSQL-ARRAY_AGG-Function_6.jpg" data-lazy- height="148" src="data:image/svg xml,” width=”452″>

We concatenate the data of two columns, “name” and “email” in an array while using ‘-‘ between them. Same as before, we extract the column “id” distinctly. The concatenated column results will be shown as “emp” at run time. The outcome set will be first assembled by the “id” of the person, and afterward, it will be organized in ascending order of column “id”. Let’s attempt a very similar command in the shell with minor changes and see the consequences. From the result below, you have acquired a distinct array for every name-email concatenated value presented in the picture while the ‘-‘ sign is used in every value.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/How-to-Use-PostgreSQL-ARRAY_AGG-Function_7.jpg" data-lazy- height="166" src="data:image/svg xml,” width=”484″>

Use Of ARRAY_AGG On Multiple Columns Without ORDER BY Clause:

You can also try the ARRAY_AGG method on any table without using the ORDER BY and GROUP BY clause. Assume a newly created table “actor” in your old database “test” having three columns; “id”, “fname”, and “lname”. The table contains data about the actor’s first names and last names along with their ids.

<img alt="" data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/How-to-Use-PostgreSQL-ARRAY_AGG-Function_8.jpg" height="178" src="data:image/svg xml,” width=”297″>

So, concatenate the two columns “fname” and “lname” in an array list while using space between them, same as you did in the last two examples. We haven’t taken out the column ‘id’ distinctly andee have been using the ARRAY_AGG function within the SELECT query. The resulted array concatenated column will be presented as “actors”. Try the below-stated query in the command shell and have a glimpse of the resulted array. We have fetched a single array with name-email concatenated value presented, separated by a comma from the outcome.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/How-to-Use-PostgreSQL-ARRAY_AGG-Function_9.jpg" data-lazy- height="84" src="data:image/svg xml,” width=”759″>

Conclusion:

Finally, you are almost done executing most of the examples required for understanding the ARRAY_AGG aggregate method. Try more of them at your end for better understanding and knowledge.