json
jsonb_agg
Postgres aggregate that collects rows into a jsonb array. Its sibling jsonb_object_agg builds a jsonb object from key/value pairs.
jsonb_agg(expression [ORDER BY ...])Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| ORDER BY | Control element order inside the resulting array |
| jsonb_object_agg(k, v) | Aggregate key/value pairs into a jsonb object |
| FILTER (WHERE ...) | Conditional inclusion of rows |
| COALESCE(jsonb_agg(x), '[]') | Guard against NULL for empty groups |
Examples
SELECT jsonb_agg(name ORDER BY name) FROM users;JSON array of usernames sorted alphabetically
SELECT jsonb_agg(to_jsonb(u.*)) FROM users u;Array of full user objects as jsonb
SELECT jsonb_object_agg(code, name) FROM countries;Build {"US": "United States", ...} lookup object
SELECT jsonb_agg(id) FILTER (WHERE active) FROM users;Only active users included in the array
Dialect notes / Gotcha
Postgres-only. Returns NULL for empty input, not '[]'::jsonb — wrap in COALESCE(jsonb_agg(x), '[]').
Related functions
ARRAY_AGG
Collects grouped values into a single array, preserving order when ORDER BY is supplied. This is a Postgres feature; MySQL has no direct array type but JSON_ARRAYAGG serves the same role.
STRING_AGG / GROUP_CONCAT
Concatenates non-NULL string values across a group into a single delimited string. Postgres and SQL Server spell it STRING_AGG; MySQL and SQLite use GROUP_CONCAT with a SEPARATOR clause.
jsonb_set
Postgres function that returns a copy of target with the value at path replaced by new_value. Creates the key if create_missing (default true); paths use text arrays like '{users,0,name}'.
-> ->> #> #>> (JSON accessors)
Postgres JSON navigation operators. Single-arrow -> and ->> take one key or index; hash-arrows #> and #>> take a text-array path for deep access. The double-arrow forms (->>, #>>) always return text; the single-arrow forms return jsonb.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet