PostgreSQL ARRAY_AGG Function
intermediateaggregation
2 min read
What is ARRAY_AGG?
ARRAY_AGG collects values from multiple rows into a PostgreSQL array. Unlike STRING_AGG, it returns a native array type that you can use with array operators.
Syntax
SELECT ARRAY_AGG(column ORDER BY column)
FROM table_name;When to Use
- Collecting IDs for further processing
- Building arrays for JSON responses
- Grouping related values while preserving array operations
Key Points
- Returns an Array — The result is a PostgreSQL array, e.g.,
{1,2,3}. - ORDER BY — Control element order:
ARRAY_AGG(name ORDER BY name). - NULLs Included — Unlike STRING_AGG, ARRAY_AGG includes NULLs by default. Filter with
ARRAY_AGG(col) FILTER (WHERE col IS NOT NULL). - With DISTINCT —
ARRAY_AGG(DISTINCT category)removes duplicates. - Unnest — Use
unnest(array)to expand an array back into rows.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
PostgreSQL ARRAY_AGG Function Challenge
Write a query that solve this task: group student IDs into an array for each grade.
Expected result
Each grade with an array of student IDs, e.g., grade A: {1, 4, 7}.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is ARRAY_AGG?
Initializing database...Each run starts from fresh sample data.
More Examples
Course names as an array
Collect course names enrolled by each student.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
How do I remove NULLs from ARRAY_AGG?
Use the FILTER clause: ARRAY_AGG(column) FILTER (WHERE column IS NOT NULL).
Can I use ARRAY_AGG without GROUP BY?
Yes. Without GROUP BY, ARRAY_AGG collects all values from the result set into a single array.