PostgreSQL json_object_agg() Function

The PostgreSQL json_object_agg() function is an aggregate function that returns a JSON object consisting of a set of key-value pairs.

json_object_agg() Syntax

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

json_object_agg(key_expr, value_expr) -> JSON

Typically, we use the json_object_agg() function like:

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

Parameters

key_expr

Required. A column name or expression used to generate the key of the key-value pair in the JSON object.

value_expr

Required. A column name or expression used to generate the value of a key-value pair in a JSON object.

Return value

The PostgreSQL json_object_agg() function returns an object that contains key-value pairs consisting of the specified column or expression in a group, where the key_expr is used as the key in the key-value pair, and the value_expr converted to a JSON value using the to_json() function is used as the value in the key-value pair.

json_object_agg() Examples

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

SELECT 'Tim' name, 'Math' subject, 'A' grade
UNION
SELECT 'Tim' name, 'English' subject, 'B' grade
UNION
SELECT 'Tom' name, 'Math' subject, 'B' grade
UNION
SELECT 'Tom' name, 'English' subject, 'A' grade;

 name | subject | grade
------+---------+-------
 Tom  | English | A
 Tom  | Math    | B
 Tim  | English | B
 Tim  | Math    | A
(4 rows)

Here, we have some rows for user grades. The name column is the name of the user, the subject column is the name of the subject, and the grade column is the grade of a subject.

Suppose, if you want to put all grades for each user (users with the same name are treated as one user) in a single JSON object, you can use the GROUP BY clause to group all rows by name and use the json_object_agg() function to construct a JSON object with the subject as key and the grade as value in each group. The following statement fulfills this requirement:

SELECT
    t.name,
    json_object_agg(t.subject, t.grade) grades
FROM (
    SELECT 'Tim' name, 'Math' subject, 'A' grade
    UNION
    SELECT 'Tim' name, 'English' subject, 'B' grade
    UNION
    SELECT 'Tom' name, 'Math' subject, 'B' grade
    UNION
    SELECT 'Tom' name, 'English' subject, 'A' grade
) t
GROUP BY t.name;

 name |              grades
------+-----------------------------------
 Tim  | { "English" : "B", "Math" : "A" }
 Tom  | { "English" : "A", "Math" : "B" }
(2 rows)

here,

• For Tim, his grades are aggregated into a JSON object { "English" : "B", "Math" : "A" }.
• For Tom, his grades are aggregated into a JSON object { "English" : "A", "Math" : "B" }.