PostgreSQL Json_Agg Function

In PostgreSQL, the json_agg() is an aggregate function that allows us to aggregate the rows into a JSON array. The function takes multiple input values and returns a JSON array of the provided values.

The function is handy when you need to quickly and efficiently aggregate the table rows into a JSON array without needing the custom logic and JSON parsing.

Let us explore what this function entails, the syntax, the accepted parameters, and some basic examples on how to use it in a real database.

PostgreSQL JSON_AGG() Function

The following code snippet describes the syntax of the json_agg() function in PostgreSQL:

json_agg(expr) -> JSON

The function accepts an expression or a set of rows or literal values and returns a JSON array of the values.

The function converts all the values in the provided expression or column into the JSON function according to the to_json() function.

Example 1: Simulate a Temporary Table

We can simulate a temporary table to demonstrate how we can use the PostgreSQL json_agg() function. The table is as follows:

CREATE TABLE employees (

id SERIAL PRIMARY KEY,

name VARCHAR(100),

department VARCHAR(100)

);

INSERT INTO employees (name, department) VALUES

(Alice Doe', 'IT'),

('Jane Smith', 'HR'),

('Michael Johnson', 'Finance'),

('Sarah Williams', 'IT');

Suppose we want to aggregate all the employee names into a JSON array. We can use json_agg for that as shown in the following query:

SELECT json_agg(name) AS employee_names FROM employees;

The given query returns a row called employee_names which contains a JSON array of the employee names.

employee_names

-----------------------------------------------------------------

["Alice Doe", "Jane Smith", "Michael Johnson", "Sarah Williams"]

(1 row)

Example 2: Aggregate Multiple Columns

We can also use the json_agg function to aggregate multiple columns into a single JSON array. For example, suppose we want to aggregate both the employee name and the department into a single JSON array. We can run the query as follows:

SELECT json_agg(json_build_object('name', name, 'department', department)) AS employees_info FROM employees;

Resulting Output:

employees_info
--------------------------------------------------------------------
[{"name" : "Alice Doe", "department" : "IT"}, {"name" : "Jane Smith", "department" : "HR"}, {"name" : "Michael Johnson", "department" : "Finance"}, {"name" : "Sarah Williams", "department" : "IT"}]

As you can see, the query returns a row called employee_info which contains a JSON array of employee information.

Example 3: Filtering the Rows

We can also filter the rows before applying the json_agg function. For example, suppose we want to aggregate only the employees in the IT department. We can use a WHERE clause:

SELECT json_agg(name) AS employee_names FROM employees WHERE department = 'IT';

This should return the matched rows as a JSON array.

employee_names

---------------------------------

["Alice Doe", "Sarah Williams"]

(1 row)

Conclusion

This tutorial taught us about the json_agg function in PostgreSQL which allows us to aggregate the rows into a JSON array.

PostgreSQL Json_Agg Function