PostgreSQL proposes various built-in functions to deal with JSON data. The JSON_AGG() is one such function that combines multiple values into a single JSON array. Using the JSON_AGG() function, a single column, multiple columns, or all columns of a table can be aggregated. The return type of the JSON_AGG() function is JSON.

This post demonstrates the usage of the JSON_AGG() function in PostgreSQL using numerous examples.

How to Use JSON_AGG() Function in Postgres?

To use the JSON_AGG() function in Postgres, the below-provided syntax is used:

JSON_AGG(expression);

In place of the “expression” parameter, you can specify any constant, table column, expression, or table reference. The stated function can aggregate the “NULL” values as well.

Let’s comprehend the usage of the JSON_AGG() function using the following examples.

Example 1: Basic Usage of the JSON_AGG() Function

A sample table named “emp_data” has already been created with the following content:

Suppose we want to aggregate the employees' names. For this purpose, the JSON_AGG() function can be utilized as follows:

SELECT JSON_AGG(emp_name) as employee_names
FROM emp_data;

The output shows that all values(including null) of the “emp_name” column have been aggregated into a single JSON array.

Example 2: Using the JSON_AGG() Function With ORDER BY Clause

Use the ORDER BY clause with the JSON_AGG() function to sort a JSON array into a specific order:

SELECT JSON_AGG(emp_name ORDER BY emp_id DESC) as employee_names
FROM emp_data;

The returned JSON array is sorted in descending order.

Example 3: Using the JSON_AGG() Function With WHERE Clause

Use the WHERE clause with the JSON_AGG() function to aggregate the filtered data only:

SELECT JSON_AGG(emp_name) as employee_names
FROM emp_data
WHERE emp_id <= 5;

The JSON_AGG() function aggregates only those employees’ names whose id is less than or equal to 5.

Example 4: Using the JSON_AGG() Function With GROUP BY Clause

Use the GROUP BY clause with the JSON_AGG() function to group the aggregated data:

SELECT joining_date, JSON_AGG(emp_name) as employee_name
FROM emp_data
GROUP BY joining_date;

The data have been aggregated based on the employees’ joining date.

Example 5: Using the JSON_AGG() Function to Aggregate All Columns

The JSON_AGG() function can be used to aggregate all columns of a specific table into a single array:

SELECT JSON_AGG(emp_data.*) as employee_info
FROM emp_data;

The JSON_AGG() function successfully aggregated an entire table’s data into a single array.

Example 6: Using the JSON_AGG() Function to Aggregate Multiple Columns

Passing multiple column names as arguments to the JSON_AGG() function will result in an error. Use the WITH clause to aggregate multiple columns without encountering any errors.

WITH emp_info AS
(SELECT emp_name, joining_date 
FROM emp_data
)
SELECT JSON_AGG(emp_info.*)
FROM emp_info;

- The WITH clause(an auxiliary statement) is used in the above snippet to define a temporary table named “emp_info”.
- Multiple columns of the “emp_data” table are fetched using the SELECT statement and stored in the temporary table: “emp_info”.
- Finally, the JSON_AGG() function is used to aggregate multiple columns based on the temporary table “emp_info”:

The output snippet signifies that multiple columns of the “emp_data” table have been aggregated into a single array.

Conclusion

In PostgreSQL, the JSON_AGG() function is used to combine multiple values into a single JSON array. The return type of the JSON_AGG() function is JSON. Passing multiple column names as arguments to the JSON_AGG() function results in an error. So, use the WITH clause to aggregate multiple columns without encountering any errors. This post demonstrated numerous examples to explain the working of the Postgres JSON_AGG() function.