> This page location: JSON Functions > jsonb_agg > Full Neon documentation index: https://neon.com/docs/llms.txt # PostgreSQL jsonb_agg() Function **Summary**: in this tutorial, you will learn how to use the PostgreSQL `jsonb_agg()` function to aggregate values into a JSON array. ## Introduction to the PostgreSQL jsonb_agg() function The `jsonb_agg()` function is an [aggregate function](../postgresql-aggregate-functions) that allows you to aggregate values into a JSON array. The `jsonb_agg()` function can be particularly useful when you want to create a JSON array from data of multiple rows. Here's the syntax of the `jsonb_agg()` function: ```sql jsonb_agg(expression) ``` In this syntax: - `expression`: is any valid expression that evaluates to a JSON value. The `jsonb_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 the `jsonb_agg()` function. ### 1) Basic jsonb_agg() function example First, [create a new table](../postgresql-tutorial/postgresql-create-table) called `products`: ```sql CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL ); ``` Second, [insert some rows](../postgresql-tutorial/postgresql-insert-multiple-rows) into the `products` table: ```sql INSERT INTO products (name, price) VALUES ('Laptop', 1200.00), ('Smartphone', 800.00), ('Headphones', 100.00); ``` Third, use the `jsonb_agg()` function to aggregate product information into a JSON array: ```sql SELECT jsonb_agg( jsonb_build_object('name', name, 'price', price) ) AS products FROM products; ``` Output: ```text 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](../postgresql-tutorial/postgresql-create-table) called `departments` and `employees`: ```sql CREATE TABLE departments( id SERIAL PRIMARY KEY, department_name VARCHAR(255) NOT NULL ); CREATE TABLE employees( id SERIAL PRIMARY KEY, employee_name VARCHAR(255) NOT NULL, department_id INT NOT NULL, FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE ); ``` Second, insert rows into `departments` and `employees` tables: ```sql 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 *; ``` The `departments` table: ```text id | department_name ----+----------------- 1 | Engineering 2 | Sales (2 rows) ``` The `employees` table: ```text 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 the `jsonb_agg()` function to retrieve departments and a list of employees for each department in the form of a JSON array: ```sql SELECT department_name, jsonb_agg(employee_name) AS employees FROM employees e INNER JOIN departments d ON d.id = e.department_id GROUP BY department_name; ``` Output: ```text 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: ```sql DROP TABLE employees; DROP TABLE departments; ``` Second, recreate the departments and employees tables: ```sql CREATE TABLE departments( id SERIAL PRIMARY KEY, department_name VARCHAR(255) NOT NULL ); CREATE TABLE employees( id SERIAL PRIMARY KEY, employee_name VARCHAR(255) NOT NULL, department_id INT NOT NULL, FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE ); ``` Third, insert rows into the departments and employees tables: ```sql 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: The `departments` table: ```text id | department_name ----+----------------- 1 | Engineering 2 | Sales 3 | IT (3 rows) ``` The `employees` 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 the `jsonb_agg()` function to retrieve departments and a list of employees for each department in the form of a JSON array: ```sql SELECT department_name, jsonb_agg (employee_name) AS employees FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY department_name; ``` Output: ```text 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 therefore `jsonb_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 the `jsonb_agg_strict()` function: ```sql SELECT department_name, jsonb_agg_strict (employee_name) AS employees FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY department_name; ``` Output: ``` department_name | employees -----------------+--------------------------------------------- Engineering | ["John Doe", "Jane Smith", "Alice Johnson"] Sales | ["Bob Brown"] IT | [] (3 rows) ``` The `jsonb_agg_strict()` function works like the `jsonb_agg()` except that it skips the null values. ## Summary - Use the `jsonb_agg()` function to aggregate values into a JSON array. --- ## Related docs (JSON Functions) - [Extracting JSON Data](https://neon.com/postgresql/postgresql-json-functions/postgresql-json-extract) - [JSONB Operators](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb-operators) - [jsonb_array_elements](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_array_elements) - [jsonb_array_elements_text](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_array_elements_text) - [jsonb_array_length](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_array_length) - [jsonb_build_array](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_build_array) - [jsonb_build_object](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_build_object) - [jsonb_each](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_each) - [jsonb_each_text](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_each_text) - [jsonb_extract_path](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_extract_path) - [jsonb_extract_path_text](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_extract_path_text) - [jsonb_insert](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_insert) - [jsonb_object](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_object) - [jsonb_object_agg](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_object_agg) - [jsonb_object_keys](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_object_keys) - [JSON Path](https://neon.com/postgresql/postgresql-json-functions/postgresql-json-path) - [jsonb_path_exists](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_path_exists) - [jsonb_path_query](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_path_query) - [jsonb_path_query_array](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_path_query_array) - [jsonb_path_query_first](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_path_query_first) - [jsonb_pretty](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_pretty) - [jsonb_set](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_set) - [jsonb_strip_nulls](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_strip_nulls) - [jsonb_to_record](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_to_record) - [jsonb_populate_record](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_populate_record) - [jsonb_populate_recordset](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_populate_recordset) - [jsonb_typeof](https://neon.com/postgresql/postgresql-json-functions/postgresql-jsonb_typeof) - [row_to_json](https://neon.com/postgresql/postgresql-json-functions/postgresql-row_to_json) - [to_jsonb](https://neon.com/postgresql/postgresql-json-functions/postgresql-to_jsonb)