The last lesson's STRING_AGG collapses a group into one piece of text. ARRAY_AGG collapses a group into an actual array value instead, a real, structured, multi-element value the application can work with as a list, not a string it has to split apart again.
Learning Objectives
- Use ARRAY_AGG on PostgreSQL, the one database here with a genuinely native array type.
- Explain why the other five databases can't offer the same function, they have no array column type for it to return.
- Know each database's real alternative for collecting a group into a structured, non-string value.
Group Rows
One group per project
ARRAY_AGG()
PostgreSQL only, a native array type
One Array Value
Not a string, a real structured value
ARRAY_AGG on PostgreSQL
ARRAY_AGG returns PostgreSQL's native array type, directly usable in further PostgreSQL-specific operations (array containment checks, unnesting back into rows, and so on), this is possible because PostgreSQL columns can natively hold an array in the first place, a feature none of the other five databases in this course have at all.
SELECT project_id, ARRAY_AGG(employee_id ORDER BY employee_id) AS assigned_employees FROM project_assignments GROUP BY project_id;
Why the Other Five Don't Have ARRAY_AGG
| Database | The Real Alternative |
|---|---|
| Oracle | COLLECT(), returning a nested table, but only into a collection TYPE declared in the schema first, a real structural type, not a plain function call. |
| MySQL / MariaDB / SQLite | No array type and no COLLECT()-style function either, the practical replacement is the next lesson's JSON_ARRAYAGG / json_group_array. |
| SQL Server | No array type at all, the practical replacements are STRING_AGG (this module's first lesson) for text, or FOR JSON PATH for a structured result. |
ARRAY_AGG isn't missing elsewhere by oversight, it's missing because an array to return it into doesn't exist there. The next lesson's JSON array aggregate is genuinely the closest equivalent on five of these six databases.
Try It Yourself
Return each department's id alongside an array of every employee id in it, on PostgreSQL.
SELECT department_id, ARRAY_AGG(id ORDER BY id) AS employee_ids FROM employees GROUP BY department_id;
Frequently Asked Questions
Can ARRAY_AGG remove duplicates or sort its result?
Yes, ARRAY_AGG(DISTINCT col ORDER BY col) works directly on PostgreSQL, same general pattern as the other aggregates in this module.
Can a PostgreSQL array column be unpacked back into separate rows?
Yes, PostgreSQL's UNNEST() function does exactly that, the reverse operation of ARRAY_AGG, turning one array value back into one row per element.
Common Beginner Mistakes
Expecting ARRAY_AGG to exist on MySQL, SQL Server, or Oracle
It doesn't, none of the three has a native array column type for it to return into. The real equivalents are a declared collection TYPE with COLLECT() on Oracle, or the JSON array aggregate covered next on the others.
Treating a PostgreSQL array value the same as a comma-separated string
They're genuinely different types. An array supports PostgreSQL-specific operators (containment, unnesting, indexing by position) a plain string from STRING_AGG simply doesn't have.
Best Practices
- Use ARRAY_AGG on PostgreSQL when the result is staying inside PostgreSQL or going to a client that understands its array type, not as a generic, portable pattern.
- On the other five databases, reach for JSON_ARRAYAGG (or that database's equivalent) rather than assuming an array type exists to aggregate into.
- Add an explicit ORDER BY inside ARRAY_AGG when the array's element order matters to the caller.
Interview Questions
Why does ARRAY_AGG exist on PostgreSQL but not on MySQL, SQL Server, or Oracle?
ARRAY_AGG returns a value of the database's array type. PostgreSQL has a native, built-in array type a column can hold directly, the other three don't, so there's no matching type for an equivalent function to return a result into, that's a structural gap, not a missing convenience function.
What's PostgreSQL's reverse operation of ARRAY_AGG, turning an array value back into rows?
UNNEST(), it takes an array value and returns one row per element, the inverse of ARRAY_AGG collapsing one row per element into a single array value.
Summary
ARRAY_AGG collapses a group into a real array value, possible only because PostgreSQL has a native array type to return it into. The other five databases here have no array type at all, Oracle's closest equivalent needs a declared collection TYPE, the rest fall back to the JSON array aggregate covered next.
What's Next?
JSON_ARRAYAGG, the aggregate that actually works across most of these six databases (unlike ARRAY_AGG's PostgreSQL-only reach), building a JSON array instead of a native array type.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →