ARRAY_AGG only works on PostgreSQL, since it needs a native array type. JSON_ARRAYAGG solves the same problem, collecting a group into one structured, multi-value result, using JSON instead, which five of these six databases can actually produce in some form.
Learning Objectives
- Build a JSON array from a group's values, under each database's real function name and version floor.
- Know which database genuinely has no built-in way to do this at all yet, and its real workaround.
- Recognize PostgreSQL's two different, both-real approaches to the same result.
Group Rows
One group per project
JSON Array Aggregate
By a different real name on every database
One JSON Array
A string value, not a native array type
The Real Function, by Database
| Database | Building a JSON Array From a Group |
|---|---|
| MySQL (5.7.22+) / MariaDB (10.5+) | JSON_ARRAYAGG(col) |
| PostgreSQL | json_agg(col) / jsonb_agg(col), PostgreSQL's own original functions, or the SQL-standard JSON_ARRAYAGG(col) added alongside them in PostgreSQL 16+ |
| SQLite | json_group_array(col), same purpose, a different real name |
| Oracle | JSON_ARRAYAGG(col), or wrapping each row first, JSON_ARRAYAGG(JSON_OBJECT(*)) |
| SQL Server | No built-in aggregate yet, in preview for SQL Server 2025 and already available on Azure SQL Database. On current, generally-available on-premises SQL Server, build it with a correlated subquery and FOR JSON PATH instead. |
SQL Server is the genuine outlier here, not a different name, an actual missing feature on the generally-available on-premises product as of this writing, with its own real workaround rather than a one-line function call.
Try It Yourself
Return each project's id alongside a JSON array of every assigned employee id, on MySQL.
SELECT project_id, JSON_ARRAYAGG(employee_id) AS assigned_employees FROM project_assignments GROUP BY project_id;
Frequently Asked Questions
Why does PostgreSQL have two different ways to build the same JSON array?
json_agg/jsonb_agg are PostgreSQL's own original functions, present long before the SQL standard added its own JSON_ARRAYAGG syntax, which PostgreSQL 16+ also added for standards compliance. Both exist on a current PostgreSQL, jsonb_agg is generally preferred for new code since it returns the more efficient binary jsonb type.
What's the actual SQL Server workaround today, without JSON_ARRAYAGG?
A correlated subquery with FOR JSON PATH, selecting the grouped values in a subquery and wrapping that subquery's result with FOR JSON PATH to produce the JSON array text, a genuinely different shape of query than a one-line aggregate function call.
Common Beginner Mistakes
Expecting JSON_ARRAYAGG to work on a current, generally-available on-premises SQL Server
It doesn't yet, that function is only in a 2025 preview and on Azure SQL Database as of this writing. The FOR JSON PATH subquery pattern is the real, working approach on a generally-available on-premises install.
Assuming MySQL and MariaDB added JSON_ARRAYAGG at the same version
They didn't, MySQL added it in 5.7.22/8.0, MariaDB added it separately in 10.5, a genuinely different version floor despite both forks sharing the exact same function name and syntax.
Best Practices
- Check the real version floor (MySQL 5.7.22+/8.0+, MariaDB 10.5+) before relying on JSON_ARRAYAGG, it's not available on every still-deployed version.
- On PostgreSQL, prefer jsonb_agg for new code over the plain-text json_agg, unless the result specifically needs to stay as plain JSON text.
- On SQL Server, use the FOR JSON PATH subquery pattern today rather than waiting on JSON_ARRAYAGG, which isn't generally available on-premises yet.
Interview Questions
Which of these six databases genuinely has no built-in way to aggregate a group into a JSON array today, and what's the real workaround?
SQL Server, on its current, generally-available on-premises release, JSON_ARRAYAGG is only in a 2025 preview and on Azure SQL Database. The working approach today is a correlated subquery wrapped in FOR JSON PATH, producing the same JSON array text through a differently-shaped query.
Why does PostgreSQL have both json_agg and a standard JSON_ARRAYAGG function doing the same thing?
json_agg (and jsonb_agg) are PostgreSQL's own, older, proprietary functions. JSON_ARRAYAGG is the SQL-standard name, added in PostgreSQL 16 for standards compliance alongside the existing ones, not a replacement for them.
Summary
Building a JSON array from a group works, in some real form, on five of these six databases, JSON_ARRAYAGG (MySQL 5.7.22+/MariaDB 10.5+/Oracle/PostgreSQL 16+), json_agg/jsonb_agg (PostgreSQL, the original proprietary form), json_group_array (SQLite). SQL Server is the genuine exception today, with FOR JSON PATH as its real, differently-shaped workaround until its own JSON_ARRAYAGG leaves preview.
What's Next?
The matching key-value version, JSON_OBJECTAGG, building a JSON object from a group instead of an array, with the exact same real per-database split.
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 →