JSONB_OBJECT_AGG(key, value) is an aggregate function that takes two columns, a key and a value, from many rows and folds them into a single JSON object. It is the direct way to turn a settings table into a map like { "key": value } without a second query or building JSON in application code.
Syntax and a basic example
The function takes exactly two arguments: the first becomes the object's key, the second its value. The key is coerced to text; the value keeps its JSON type. The call usually sits next to a GROUP BY, but without grouping it folds the whole table into one object.
SELECT JSONB_OBJECT_AGG(key, value) AS settings
FROM settings;
If settings holds rows ('theme', 'dark') and ('lang', 'en'), the result is {"theme": "dark", "lang": "en"}. A few behaviors worth keeping in mind:
- The key cannot be
NULL; such a row raises an error. Filter them out first with WHERE key IS NOT NULL.
- A
NULL value is allowed and lands in the object as JSON null.
- Key order in
jsonb is neither preserved nor meaningful: it is a map, not a list.
- An empty group yields
NULL, not an empty object {}.
A lookup document per group
The most useful application is building one JSON object per entity. Say every user has a set of key/value pairs in a user_settings(user_id, key, value) table. Assemble each user's settings profile in one query:
SELECT
user_id,
JSONB_OBJECT_AGG(key, value) AS prefs
FROM user_settings
GROUP BY user_id;
The source of keys and values does not have to be a ready-made settings table. You can aggregate any two columns, for example to build a "order status -> total for that status" map per user:
SELECT
user_id,
JSONB_OBJECT_AGG(status, total) AS totals_by_status
FROM (
SELECT user_id, status, SUM(amount) AS total
FROM orders
GROUP BY user_id, status
) s
GROUP BY user_id;
Here the subquery guarantees exactly one row per (user_id, status) pair, which means exactly one key in the object.
Duplicate-key behavior
This is the main trap. If a group has two values under the same key, JSONB_OBJECT_AGG does not error and does not merge them; it simply keeps one, and which one depends on the row processing order, so it is effectively unpredictable.
SELECT JSONB_OBJECT_AGG(key, value)
FROM (VALUES ('color', 'red'), ('color', 'blue')) AS t(key, value);
Gotcha: do not rely on a silent "last one wins." If, say, the most recent record should win, make deduplication explicit with DISTINCT ON or a window function before aggregating:
SELECT user_id, JSONB_OBJECT_AGG(key, value) AS prefs
FROM (
SELECT DISTINCT ON (user_id, key)
user_id, key, value
FROM user_settings
ORDER BY user_id, key, updated_at DESC
) latest
GROUP BY user_id;
That way you decide which row wins for a duplicate key, instead of leaving it to the planner.
JSONB_OBJECT_AGG vs JSONB_AGG of objects
An easy confusion: JSONB_AGG also builds JSON, but an array, not an object. The difference is in the result shape and how you navigate it afterward.
SELECT JSONB_OBJECT_AGG(key, value)
FROM user_settings WHERE user_id = 1;
SELECT JSONB_AGG(JSONB_BUILD_OBJECT('key', key, 'value', value))
FROM user_settings WHERE user_id = 1;
How to choose:
- Need lookup by name (
prefs->>'theme') and unique keys? Reach for JSONB_OBJECT_AGG. It is a dictionary.
- Need an ordered list, repeated elements, or several fields per record? Reach for
JSONB_AGG. It is a list.
JSONB_AGG accepts an ORDER BY inside the aggregate; for an object the key order is meaningless anyway.
Notes on other databases
In MySQL the equivalent is JSON_OBJECTAGG(key, value), which works similarly, but duplicate-key behavior varies by version and mode, so deduplicate up front just as you would in PostgreSQL. ClickHouse has no direct aggregate: you build a map with map() or groupArray of pairs and convert, and the Map type replaces the JSON object. When you specifically need a compact lookup document per group, JSONB_OBJECT_AGG in PostgreSQL stays the most direct tool.
JSONB_OBJECT_AGG(key, value)is an aggregate function that takes two columns, a key and a value, from many rows and folds them into a single JSON object. It is the direct way to turn a settings table into a map like{ "key": value }without a second query or building JSON in application code.Syntax and a basic example
The function takes exactly two arguments: the first becomes the object's key, the second its value. The key is coerced to text; the value keeps its JSON type. The call usually sits next to a
GROUP BY, but without grouping it folds the whole table into one object.SELECT JSONB_OBJECT_AGG(key, value) AS settings FROM settings;If
settingsholds rows('theme', 'dark')and('lang', 'en'), the result is{"theme": "dark", "lang": "en"}. A few behaviors worth keeping in mind:NULL; such a row raises an error. Filter them out first withWHERE key IS NOT NULL.NULLvalue is allowed and lands in the object as JSONnull.jsonbis neither preserved nor meaningful: it is a map, not a list.NULL, not an empty object{}.A lookup document per group
The most useful application is building one JSON object per entity. Say every user has a set of key/value pairs in a
user_settings(user_id, key, value)table. Assemble each user's settings profile in one query:SELECT user_id, JSONB_OBJECT_AGG(key, value) AS prefs FROM user_settings GROUP BY user_id;The source of keys and values does not have to be a ready-made settings table. You can aggregate any two columns, for example to build a "order status -> total for that status" map per user:
SELECT user_id, JSONB_OBJECT_AGG(status, total) AS totals_by_status FROM ( SELECT user_id, status, SUM(amount) AS total FROM orders GROUP BY user_id, status ) s GROUP BY user_id;Here the subquery guarantees exactly one row per
(user_id, status)pair, which means exactly one key in the object.Duplicate-key behavior
This is the main trap. If a group has two values under the same key,
JSONB_OBJECT_AGGdoes not error and does not merge them; it simply keeps one, and which one depends on the row processing order, so it is effectively unpredictable.-- two rows share the key 'color' -> only one survives, order is undefined SELECT JSONB_OBJECT_AGG(key, value) FROM (VALUES ('color', 'red'), ('color', 'blue')) AS t(key, value);SELECT user_id, JSONB_OBJECT_AGG(key, value) AS prefs FROM ( SELECT DISTINCT ON (user_id, key) user_id, key, value FROM user_settings ORDER BY user_id, key, updated_at DESC ) latest GROUP BY user_id;That way you decide which row wins for a duplicate key, instead of leaving it to the planner.
JSONB_OBJECT_AGG vs JSONB_AGG of objects
An easy confusion:
JSONB_AGGalso builds JSON, but an array, not an object. The difference is in the result shape and how you navigate it afterward.-- object (map): key lookup by name SELECT JSONB_OBJECT_AGG(key, value) FROM user_settings WHERE user_id = 1; -- => {"theme": "dark", "lang": "en"} -- array of objects: ordered list, no direct key lookup SELECT JSONB_AGG(JSONB_BUILD_OBJECT('key', key, 'value', value)) FROM user_settings WHERE user_id = 1; -- => [{"key": "theme", "value": "dark"}, {"key": "lang", "value": "en"}]How to choose:
prefs->>'theme') and unique keys? Reach forJSONB_OBJECT_AGG. It is a dictionary.JSONB_AGG. It is a list.JSONB_AGGaccepts anORDER BYinside the aggregate; for an object the key order is meaningless anyway.Notes on other databases
In MySQL the equivalent is
JSON_OBJECTAGG(key, value), which works similarly, but duplicate-key behavior varies by version and mode, so deduplicate up front just as you would in PostgreSQL. ClickHouse has no direct aggregate: you build a map withmap()orgroupArrayof pairs and convert, and theMaptype replaces the JSON object. When you specifically need a compact lookup document per group,JSONB_OBJECT_AGGin PostgreSQL stays the most direct tool.