PostgreSQL jsonb_agg() Function

The PostgreSQL jsonb_agg() function is an aggregate function that returns a JSON array containing all the values ​​in a group.

jsonb_agg() Syntax

Here is the syntax of the PostgreSQL jsonb_agg() function:

jsonb_agg(expr) -> JSON

Typically, we use the jsonb_agg() function like:

SELECT jsonb_agg(expr), ...
FROM table_name
[WHERE ...]
[GROUP BY group_expr1, group_expr2, ...];

Parameters

expr

Required. A column name or expression.

Return value

The PostgreSQL jsonb_agg() function returns a JSON array containing all the values of the specified expression or column in the specified group, including null.

All values ​​in the expression or column will be converted to JSON values ​​according to the to_json() function.

jsonb_agg() Examples

To demonstrate usages of the PostgreSQL jsonb_agg() function, we simulate a temporary table using the following statement with UNION and SELECT:

SELECT 'Tim' name, 'Football' hobby
UNION
SELECT 'Tim' name, 'Baseball' hobby
UNION
SELECT 'Tom' name, 'Piano' hobby
UNION
SELECT 'Tom' name, 'violin' hobby;

 name |  hobby
------+----------
 Tom  | violin
 Tom  | Piano
 Tim  | Football
 Tim  | Baseball
(4 rows)

Here, we have some rows about the user’s hobbies. The name column is the user’s name, and the hobby column is a hobby of the user.

Suppose, if you want to put all the hobbies of each user in a JSON array by user (users with the same name count as one user), you can use the GROUP BY clause to group all rows by name and use the jsonb_agg() function to construct a JSON array from all values of hobby column in each group. The following statement fulfills this requirement:

SELECT
    t.name,
    jsonb_agg(t.hobby) hobbies
FROM (
    SELECT 'Tim' name, 'Football' hobby
    UNION
    SELECT 'Tim' name, 'Baseball' hobby
    UNION
    SELECT 'Tom' name, 'Piano' hobby
    UNION
    SELECT 'Tom' name, 'violin' hobby
) t
GROUP BY t.name;

 name |         hobbies
------+--------------------------
 Tim  | ["Football", "Baseball"]
 Tom  | ["violin", "Piano"]
(2 rows)

Here, the hobbies column is a JSON array containing all of a user’s hobbies.