aggregate
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.
ARRAY_AGG(expr [ORDER BY ...] [FILTER (WHERE ...)])Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| ORDER BY | Control element order in the resulting array |
| DISTINCT | Collapse duplicates before aggregating |
| FILTER (WHERE ...) | Only include rows meeting a predicate |
| JSON_ARRAYAGG | MySQL 8 equivalent producing a JSON array |
Examples
SELECT ARRAY_AGG(id ORDER BY id) FROM users;Postgres integer array of user IDs, ascending
SELECT dept, ARRAY_AGG(DISTINCT skill) FROM emp GROUP BY dept;Unique skills per department as a text array
SELECT JSON_ARRAYAGG(name) FROM users;MySQL 8 equivalent producing a JSON array
SELECT ARRAY_AGG(name) FILTER (WHERE active) FROM users;Postgres: only active users included in the array
Dialect notes / Gotcha
Postgres-only for true arrays; MySQL/SQLite emulate via JSON_ARRAYAGG (MySQL 8) or json_group_array (SQLite). ARRAY_AGG with no rows returns NULL rather than an empty array — COALESCE with '{}'.
Related functions
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_agg
Postgres aggregate that collects rows into a jsonb array. Its sibling jsonb_object_agg builds a jsonb object from key/value pairs.
COALESCE
Returns the first non-NULL argument, or NULL if every argument is NULL. Short-circuit evaluated — later expressions are not computed once a non-NULL value is found.
COUNT
Returns the number of rows matching a query, optionally counting non-NULL values of an expression or distinct values. COUNT(*) counts every row including NULLs, while COUNT(expr) skips NULLs.
SUM
Adds up all non-NULL numeric values in a column or expression. Returns NULL (not 0) if every input row is NULL or the result set is empty.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet