Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT author_id, json_group_array(title) AS titles
FROM books
GROUP BY author_id;

Try it

SQL An array of titles per author ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL An object that maps keys to values ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Nested JSON: arrays of objects ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- 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.
SQL
-- 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 BY inside the call the order is not defined. In SQLite, sort in a subquery first.
  • Duplicate keys. json_group_object keeps every pair, so duplicate keys appear twice; most JSON readers keep only the last. Make keys unique.
  • NULLs. SQLite writes null in the array. GROUP_CONCAT and STRING_AGG skip NULLs instead.
  • Nesting. To nest JSON inside JSON, wrap inner values in json_object or json_array; a plain string would be escaped as text.
  • Reading it back. Use json_extract and json_each.
  • Size. Big arrays are large values. Aggregate only what the caller needs.