PostgreSQL jsonb_agg() Function
Summary: in this tutorial, you will learn how to use the PostgreSQLjsonb_agg() function to aggregate values into a JSON array.
Introduction to the PostgreSQL jsonb_agg() function
Thejsonb_agg() function is anaggregate function that allows you to aggregate values into a JSON array.
Thejsonb_agg() function can be particularly useful when you want to create a JSON array from data of multiple rows.
Here’s the syntax of thejsonb_agg() function:
jsonb_agg(expression)In this syntax:
expression: is any valid expression that evaluates to a JSON value.
Thejsonb_agg() function returns a JSON array that consists of data from multiple rows.
PostgreSQL jsonb_agg() function example
Let’s explore some examples of using thejsonb_agg() function.
1) Basic jsonb_agg() function example
First,create a new table calledproducts:
CREATE TABLE products ( idSERIAL PRIMARY KEY, name VARCHAR(100)NOT NULL, priceDECIMAL(10,2)NOT NULL);Second,insert some rows into theproducts table:
INSERT INTO products (name, price)VALUES('Laptop',1200.00),('Smartphone',800.00),('Headphones',100.00);Third, use thejsonb_agg() function to aggregate product information into a JSON array:
SELECT jsonb_agg( jsonb_build_object('name',name,'price', price) )AS productsFROM products;Output:
products-------------------------------------------------------------------------------------------------------------------------- [{"name": "Laptop", "price": 1200.00}, {"name": "Smartphone", "price": 800.00}, {"name": "Headphones", "price": 100.00}](1 row)2) Using jsonb_agg() function with GROUP BY clause
First,create new tables calleddepartments andemployees:
CREATE TABLE departments( idSERIAL PRIMARY KEY, department_nameVARCHAR(255)NOT NULL);CREATE TABLE employees( idSERIAL PRIMARY KEY, employee_nameVARCHAR(255)NOT NULL, department_idINT NOT NULL, FOREIGN KEY (department_id) REFERENCES departments(id)ON DELETE CASCADE);Second, insert rows intodepartments andemployees tables:
INSERT INTO departments (department_name)VALUES ('Engineering'), ('Sales')RETURNING*;INSERT INTO employees (employee_name, department_id)VALUES ('John Doe',1), ('Jane Smith',1), ('Alice Johnson',1), ('Bob Brown',2)RETURNING*;Thedepartments table:
id | department_name----+----------------- 1 | Engineering 2 | Sales(2 rows)Theemployees table:
id | employee_name | department_id----+---------------+--------------- 1 | John Doe | 1 2 | Jane Smith | 1 3 | Alice Johnson | 1 4 | Bob Brown | 2(4 rows)Third, use thejsonb_agg() function to retrieve departments and a list of employees for each department in the form of a JSON array:
SELECT department_name, jsonb_agg(employee_name)AS employeesFROM employees e INNER JOIN departments dON d.id= e.department_idGROUP BY department_name;Output:
department_name | employees-----------------+--------------------------------------------- Engineering | ["John Doe", "Jane Smith", "Alice Johnson"] Sales | ["Bob Brown"](2 rows)3) Using jsonb_agg() function with NULLs
First, drop the departments and employees tables:
DROP TABLE employees;DROP TABLE departments;Second, recreate the departments and employees tables:
CREATE TABLE departments( idSERIAL PRIMARY KEY, department_nameVARCHAR(255)NOT NULL);CREATE TABLE employees( idSERIAL PRIMARY KEY, employee_nameVARCHAR(255)NOT NULL, department_idINT NOT NULL, FOREIGN KEY (department_id) REFERENCES departments(id)ON DELETE CASCADE);Third, insert rows into the departments and employees tables:
INSERT INTO departments (department_name)VALUES ('Engineering'), ('Sales'), ('IT')RETURNING*;INSERT INTO employees (employee_name, department_id)VALUES ('John Doe',1), ('Jane Smith',1), ('Alice Johnson',1), ('Bob Brown',2)RETURNING*;Output:
Thedepartments table:
id | department_name----+----------------- 1 | Engineering 2 | Sales 3 | IT(3 rows)Theemployees table:
id | employee_name | department_id----+---------------+--------------- 1 | John Doe | 1 2 | Jane Smith | 1 3 | Alice Johnson | 1 4 | Bob Brown | 2(4 rows)Third, use thejsonb_agg() function to retrieve departments and a list of employees for each department in the form of a JSON array:
SELECT department_name, jsonb_agg (employee_name) AS employeesFROM departments d LEFT JOIN employees e ON d.id = e.department_idGROUP BY department_name;Output:
department_name | employees-----------------+--------------------------------------------- Engineering | ["John Doe", "Jane Smith", "Alice Johnson"] Sales | ["Bob Brown"] IT | [null](3 rows)In this example, the IT department has no employees thereforejsonb_agg() function returns an array that contains a null value.
To skip the null and make the JSON array an empty array, you can use thejsonb_agg_strict() function:
SELECT department_name, jsonb_agg_strict (employee_name)AS employeesFROM departments d LEFT JOIN employees eON d.id= e.department_idGROUP BY department_name;Output:
department_name | employees-----------------+--------------------------------------------- Engineering | ["John Doe","Jane Smith","Alice Johnson"] Sales | ["Bob Brown"] IT | [](3 rows)Thejsonb_agg_strict() function works like thejsonb_agg() except that it skips the null values.
Summary
- Use the
jsonb_agg()function to aggregate values into a JSON array.
Last updated on