SQL JSON Aggregation by Database: Arrays and Objects

JSON aggregation combines values from multiple query rows into one JSON array or object. It is different from a JSON constructor such as JSON_ARRAY() or JSON_OBJECT(), which builds a value from expressions in one row. The aggregate function name, ordering syntax, NULL handling, and result type depend on the database.

To reformat any example below, open the browser-based SQL Formatter and choose the matching database dialect. It formats the query locally but does not validate or execute it.

Function names by database

The result representation and size limit matter to application code as well. MySQL returns JSON values; MariaDB’s maximum result length is controlled by group_concat_max_len. PostgreSQL’s json_ and jsonb_ aggregates return the corresponding json and jsonb types, while SQLite’s json_ variants return text and jsonb_ variants return JSONB blobs. SQL Server returns nvarchar(max) by default and supports RETURNING JSON; Oracle defaults to VARCHAR2(4000) and supports RETURNING CLOB, BLOB, or JSON. See the vendor references for MySQL, MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle.

For detailed SQLiz syntax and examples, see the references for MySQL JSON aggregates, MariaDB JSON aggregates, PostgreSQL json_agg and json_object_agg, SQLite JSON aggregates, SQL Server JSON functions, and Oracle JSON aggregates.

See the official references for the exact syntax and platform list: MySQL JSON aggregates and the MySQL 5.7.22 release notes, MariaDB JSON_ARRAYAGG and JSON_OBJECTAGG, PostgreSQL aggregate functions, SQLite JSON functions, SQL Server JSON_ARRAYAGG and JSON_OBJECTAGG, and Oracle JSON_ARRAYAGG and JSON_OBJECTAGG.

Build an array per group

Assume a staff table with department, employee_id, and employee_name columns. The goal is one ordered array of names for each department.

MySQL:

SELECT department,
       JSON_ARRAYAGG(employee_name) AS employees
FROM staff
GROUP BY department;

MySQL does not guarantee the order of values returned by JSON_ARRAYAGG. MariaDB has an ORDER BY clause in its aggregate syntax:

SELECT department,
       JSON_ARRAYAGG(employee_name ORDER BY employee_id) AS employees
FROM staff
GROUP BY department;

PostgreSQL uses json_agg or jsonb_agg. The ORDER BY belongs inside the aggregate call:

SELECT department,
       jsonb_agg(employee_name ORDER BY employee_id) AS employees
FROM staff
GROUP BY department;

PostgreSQL 16 and later also support the SQL-standard json_arrayagg name:

SELECT department,
       json_arrayagg(employee_name ORDER BY employee_id) AS employees
FROM staff
GROUP BY department;

SQLite uses json_group_array. SQLite 3.44.0 and later can order aggregate inputs directly:

SELECT department,
       json_group_array(employee_name ORDER BY employee_id) AS employees
FROM staff
GROUP BY department;

SQLite’s jsonb_group_array and jsonb_group_object return JSONB, but the aggregate JSON functions process their inputs as text. When aggregating a value produced by another JSON function, SQLite documents json(...) as more efficient than jsonb(...) for these aggregates.

SQL Server 2025 supports ordering inside JSON_ARRAYAGG:

SELECT department,
       JSON_ARRAYAGG(employee_name ORDER BY employee_id) AS employees
FROM staff
GROUP BY department;

In Oracle, use the same aggregate name with its SQL/JSON syntax. RETURNING CLOB avoids the default VARCHAR2(4000) result limit when the array can be large:

SELECT department,
       JSON_ARRAYAGG(employee_name ORDER BY employee_id RETURNING CLOB) AS employees
FROM staff
GROUP BY department;

Build an object per group

Object aggregates turn key/value rows into properties. The examples below group employees by department and use each employee ID as an object key. They assume an ID is unique within a department; if duplicate keys are possible, decide which row should win before aggregating.

MySQL:

SELECT department,
       JSON_OBJECTAGG(CAST(employee_id AS CHAR), employee_name) AS employees_by_id
FROM staff
GROUP BY department;

MariaDB:

SELECT department,
       JSON_OBJECTAGG(CAST(employee_id AS CHAR), employee_name) AS employees_by_id
FROM staff
GROUP BY department;

PostgreSQL:

SELECT department,
       jsonb_object_agg(employee_id::text, employee_name) AS employees_by_id
FROM staff
GROUP BY department;

PostgreSQL 16 and later also support the SQL-standard json_objectagg syntax:

SELECT department,
       json_objectagg(employee_id::text VALUE employee_name) AS employees_by_id
FROM staff
GROUP BY department;

SQLite:

SELECT department,
       json_group_object(CAST(employee_id AS TEXT), employee_name) AS employees_by_id
FROM staff
GROUP BY department;

SQL Server 2025:

SELECT department,
       JSON_OBJECTAGG(CONVERT(varchar(20), employee_id): employee_name) AS employees_by_id
FROM staff
GROUP BY department;

Oracle:

SELECT department,
       JSON_OBJECTAGG(KEY TO_CHAR(employee_id) VALUE employee_name RETURNING CLOB) AS employees_by_id
FROM staff
GROUP BY department;

The explicit casts make the object-key type visible in each example. The different argument forms are not interchangeable between engines.

Ensure keys are non-NULL and unique before aggregating. MySQL raises an error for a NULL key, while SQLite ignores rows whose NAME argument is NULL. MySQL keeps the last value for duplicate keys, and the selected row may be nondeterministic without a defined order. MariaDB’s documentation shows that duplicate keys can remain in the returned text. PostgreSQL 16 and later provide json_object_agg_unique and jsonb_object_agg_unique, which raise an error for duplicate keys. Other engines may validate or represent duplicate keys differently, so do not rely on one engine’s result shape in portable application code.

Check NULL and empty-input behavior

SQL NULL is not handled identically by every array aggregate. PostgreSQL’s json_agg and SQLite’s json_group_array include SQL NULL values as JSON null. SQL Server’s JSON_ARRAYAGG defaults to ABSENT ON NULL; specify NULL ON NULL to keep a JSON null element.

Object aggregates also distinguish a NULL key from a NULL value. PostgreSQL’s json_object_agg keeps null values as JSON null, while json_object_agg_strict skips them. SQLite’s json_group_object includes null values as JSON null but ignores rows with a NULL name. SQL Server’s JSON_OBJECTAGG defaults to NULL ON NULL (the opposite of its array aggregate); ABSENT ON NULL omits that property. Oracle’s JSON_OBJECTAGG supports both NULL ON NULL and ABSENT ON NULL; specify the clause required by the output.

Empty input also varies. SQLite’s JSON aggregate functions return [] or {} when there are no valid input rows. PostgreSQL aggregates return SQL NULL for no rows; MySQL’s JSON_ARRAYAGG and JSON_OBJECTAGG, and MariaDB’s JSON_ARRAYAGG and JSON_OBJECTAGG, also return SQL NULL when their result contains no rows. In PostgreSQL, use COALESCE(jsonb_agg(value), '[]'::jsonb) for an empty array or COALESCE(jsonb_object_agg(key, value), '{}'::jsonb) for an empty object when there are no rows. See the MySQL, MariaDB JSON_ARRAYAGG, and MariaDB JSON_OBJECTAGG references.

For each database, check the version and function reference before relying on a particular return type, ordering rule, or NULL policy. Use the database-specific SQLiz references above for detailed examples and limitations.