ARRAY_AGG

MySQL equivalent:

Collects the values of a group of rows into an array.

PostgreSQL 17.9

ARRAY_AGG([DISTINCT] expression [ORDER BY sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST]])

DISTINCT: Collect only unique values
KeywordOptional
expression: Expression whose values are collected into the array
ORDER BY sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST]: Order of elements, arbitrary without it
Optional
sort_expression: Column or expression to sort by
ASC | DESC: Direction, ASC by default
KeywordOptional
ASCDESC
NULLS FIRST | NULLS LAST: Where NULLs go, by default last with ASC and first with DESC
KeywordOptional
NULLS FIRSTNULLS LAST

Return value

An array of values of the same type, NULLs included; NULL for an empty set

PostgreSQL 17.9

SELECT ARRAY_AGG(
		n
		ORDER BY n DESC
	)
FROM GENERATE_SERIES(1, 5) AS n;

array_agg
[5,4,3,2,1]

PostgreSQL 17.9

SELECT status,
	ARRAY_AGG(member_name) AS members
FROM FamilyMembers
GROUP BY status
ORDER BY status;

status	members
daughter	["Lela Quincey","Annie Quincey","Wednesday Addams"]
father	["Headley Quincey","Ernest Forrest"]
mother	["Flavia Quincey","Constance Forrest"]
son	["Andie Quincey"]

Unlike STRING_AGG, ARRAY_AGG does not skip NULL values.

Array elements are numbered from 1: the expression (ARRAY_AGG(member_name ORDER BY birthday))[1] returns the name of the oldest family member.