SQL Reference ยท Aggregate Functions
SQL JSON aggregates
Reference for SQL JSON aggregation: json_group_array and json_group_object in SQLite, with JSON_AGG, JSON_ARRAYAGG and JSON_OBJECT_AGG in other databases.
JSON aggregates collapse the rows of a group into one JSON value: an array of values, or an object of key/value pairs. They let a single query return nested data, such as an author with a list of books, without a second round trip.
SQLite calls them json_group_array and json_group_object. PostgreSQL has json_agg, jsonb_agg and json_object_agg; MySQL has JSON_ARRAYAGG and JSON_OBJECTAGG; SQL Server 2025 and Oracle have JSON_ARRAYAGG and JSON_OBJECTAGG as well.
SELECT author_id, json_group_array(title) AS titles FROM books GROUP BY author_id;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- SQLite json_group_array(expr) json_group_object(name, value) -- PostgreSQL json_agg(expr) jsonb_agg(expr) json_object_agg(name, value) -- MySQL, Oracle, SQL Server 2025 JSON_ARRAYAGG(expr) JSON_OBJECTAGG(name : value)
| Part | Description |
|---|---|
expr |
A value to put in the array. Text becomes a JSON string, numbers stay numbers. |
name, value |
The key and value of each object member. The key is converted to text. |
Returns: a JSON value as text (SQLite, MySQL) or as a json / jsonb type (PostgreSQL). A group always yields at least one row, so the array is never empty unless the query itself has no GROUP BY and no input rows (SQLite then returns []).
Dialect differences
| Database | Array | Object | Notes |
|---|---|---|---|
| SQLite | json_group_array(x) |
json_group_object(k, v) |
Result is text; NULL values appear as JSON null. |
| PostgreSQL | json_agg(x), jsonb_agg(x) |
json_object_agg(k, v) |
Supports ORDER BY inside the call. An empty group gives NULL. |
| MySQL | JSON_ARRAYAGG(x) |
JSON_OBJECTAGG(k, v) |
Order of elements is not guaranteed. |
| SQL Server | JSON_ARRAYAGG (2025) |
JSON_OBJECTAGG (2025) |
Older versions use FOR JSON PATH. |
| Oracle | JSON_ARRAYAGG(x ORDER BY y) |
JSON_OBJECTAGG(KEY k VALUE v) |
Returns VARCHAR2 or CLOB. |
-- PostgreSQL SELECT author_id, json_agg(title ORDER BY published) AS titles FROM books GROUP BY author_id;
Usage notes
- Order inside the array. Without an
ORDER BYinside the call the order is not defined. In SQLite, sort in a subquery first. - Duplicate keys.
json_group_objectkeeps every pair, so duplicate keys appear twice; most JSON readers keep only the last. Make keys unique. - NULLs. SQLite writes
nullin the array.GROUP_CONCATandSTRING_AGGskipNULLs instead. - Nesting. To nest JSON inside JSON, wrap inner values in
json_objectorjson_array; a plain string would be escaped as text. - Reading it back. Use
json_extractandjson_each. - Size. Big arrays are large values. Aggregate only what the caller needs.