PostgreSQL jsonb_object_agg() Function
Summary: in this tutorial, you will learn how to use the PostgreSQLjsonb_object_agg() function to aggregate key/value pairs into a JSON object.
Introduction to the PostgreSQL jsonb_object_agg() function
The PostgreSQLjsonb_object_agg() function is anaggregate function that allows you to collect key/value pairs into aJSON object.
Thejsonb_object_agg() can be useful when you want to aggregate data from multiple rows into a single JSON object or construct complex JSON output.
Here’s the syntax of thejsonb_object_agg() function:
jsonb_object_agg(key,value)In this syntax:
keyrepresents the key for the JSON object. The key must not be null.valuerepresents the value for the corresponding key.
Thejsonb_object_agg() returns a JSON object that consists of key/value pairs.
PostgreSQL jsonb_object_agg() function examples
Let’s explore some examples of using the PostgreSQLjsonb_object_agg() function.
1) Basic PostgreSQL jsonb_object_agg() function example
First,create a table calleddepartments:
CREATE TABLE departments( idSERIAL PRIMARY KEY, department_nameVARCHAR(255)NOT NULL);Second,insert some rows into thedepartments table:
INSERT INTO departments(department_name)VALUES ('Sales'), ('Marketing')RETURNING*;Third, use thejsonb_object_agg() function to create an object whose key is the department name and value is the id:
SELECT jsonb_object_agg(department_name, id) departmentsFROM departments;Output:
departments------------------------------ {"Sales": 1, "Marketing": 2}(1 row)2) Using the jsonb_object_agg() function with GROUP BY clause
First,create a new table calledemployees:
CREATE TABLE employees ( idSERIAL PRIMARY KEY, name VARCHAR(100)NOT NULL, salaryDECIMAL(10,2), department_idINT NOT NULL, FOREIGN KEY(department_id) REFERENCES departments(id)ON DELETE CASCADE);Second,insert some rows into theemployees table:
INSERT INTO employees (name, salary, department_id)VALUES ('John Doe',60000,1), ('Jane Smith',65000,1), ('Alice Johnson',55000,2), ('Bob Williams',70000,2), ('Alex Miller',NULL ,2)RETURNING*;Output:
id | name | salary | department_id----+---------------+----------+--------------- 1 | John Doe | 60000.00 | 1 2 | Jane Smith | 65000.00 | 1 3 | Alice Johnson | 55000.00 | 2 4 | Bob Williams | 70000.00 | 2 5 | Alex Miller | null | 2(5 rows)Third, use thejsonb_object_agg() function to get the department name and a JSON object that contains employee details of the department including employee name and salary:
SELECT department_name, jsonb_pretty( jsonb_object_agg(e.name, e.salary) )AS employee_detailsFROM departments d INNER JOIN employees eON e.department_id= d.idGROUP BY department_name;Output:
department_name | employee_details-----------------+------------------------------- Marketing | { + | "Alex Miller": null, + | "Bob Williams": 70000.00,+ | "Alice Johnson": 55000.00+ | } Sales | { + | "John Doe": 60000.00, + | "Jane Smith": 65000.00 + | }(2 rows)Note that we use thejsonb_pretty() function to format JSON.
Alex Miller has not had a salary yet so his salary is null. Thejsonb_object_agg() also collects the null into the JSON object.
To skip nulls, you can use thejsonb_object_agg_strict() function as follows:
SELECT department_name, jsonb_pretty( jsonb_object_agg_strict(e.name, e.salary) )AS employee_detailsFROM departments d INNER JOIN employees eON e.department_id= d.idGROUP BY department_name;Output:
department_name | employee_details-----------------+------------------------------- Marketing | { + | "Bob Williams": 70000.00,+ | "Alice Johnson": 55000.00+ | } Sales | { + | "John Doe": 60000.00, + | "Jane Smith": 65000.00 + | }(2 rows)Thejsonb_object_agg_strict() function works like thejsonb_object_agg() function except that it skips null values.
Summary
- Use the
jsonb_object_agg()function to aggregate key/value pairs into a JSON object. - Use the
jsonb_object_agg()function to aggregate key/value pairs into a JSON object and skip null values.
Last updated on