JSON_AGG / JSON_ARRAYAGGJSON
Buy Me A Coffee

Aggregate multiple rows into a single JSON array (or object), collapsing a one-to-many relationship into a nested JSON structure within a query.

PostgreSQL aggregate function that collects rows into a JSON array, the inverse of json_each. Combines with ORDER BY within the aggregate to control array element order. The foundation of SQL-to-JSON transformations.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.7.22 JSON_ARRAYAGG(expr) aggregates values into a JSON array. JSON_OBJECTAGG(key_expr, value_expr) aggregates key-value pairs into a JSON object. Both added in 5.7.22. No ORDER BY inside the aggregate — sort the outer query or use a subquery. NULL values in the expression are included as JSON null.
PostgreSQL ✓ Supported 9.3 json_agg(expr) aggregates into a json array (since 9.3); jsonb_agg(expr) returns jsonb (since 9.4). json_object_agg(key, value) / jsonb_object_agg(key, value) aggregate into a JSON object. ORDER BY is supported inside the aggregate. NULL values are included as JSON null. Use FILTER (WHERE ...) to exclude rows.
SQL Server ✗ Not Supported — No JSON_ARRAYAGG or json_agg. Use FOR JSON PATH / FOR JSON AUTO on a subquery to produce a JSON array. STRING_AGG can be combined with JSON_OBJECT (SQL Server 2022+) for simple cases. The result of FOR JSON is NVARCHAR, not a typed JSON column.
Oracle ✓ Supported 18c JSON_ARRAYAGG(expr) and JSON_OBJECTAGG(key VALUE value) added in Oracle 18c. In Oracle 12c, workarounds include LISTAGG combined with manual JSON construction, or using JSON_ARRAY with a subquery. ORDER BY and ON NULL clauses supported inside JSON_ARRAYAGG.
SQLite ✓ Supported 3.9.0 json_group_array(expr) aggregates values into a JSON array. json_group_object(name, value) aggregates into a JSON object. Both available since 3.9.0 when the JSON1 extension was added. In most SQLite distributions the JSON1 extension is compiled in by default.

Details

Function naming is completely non-standard across engines: json_agg (PostgreSQL), JSON_ARRAYAGG (MySQL/Oracle 18c), json_group_array (SQLite), and FOR JSON PATH (SQL Server). SQL Server is the only engine with no native aggregate function — it requires a subquery with FOR JSON. PostgreSQL is the only engine supporting ORDER BY inside the aggregate directly and offers FILTER (WHERE ...) for conditional aggregation. Oracle added JSON_ARRAYAGG in 18c; Oracle 12c users need a LISTAGG workaround.

Standard Syntax

-- PostgreSQL: SELECT dept_id, json_agg(employee_name ORDER BY employee_name) AS employees FROM employees GROUP BY dept_id; -- MySQL: SELECT dept_id, JSON_ARRAYAGG(employee_name) AS employees FROM employees GROUP BY dept_id;

Version Support

MySQL: Since 5.7.22 PostgreSQL: Since 9.3 SQL Server: Not supported Oracle: Since 18c SQLite: Since 3.9.0

Per-Database Syntax & Notes

MySQL

JSON_ARRAYAGG(expr) aggregates values into a JSON array. JSON_OBJECTAGG(key_expr, value_expr) aggregates key-value pairs into a JSON object. Both added in 5.7.22. No ORDER BY inside the aggregate — sort the outer query or use a subquery. NULL values in the expression are included as JSON null.

-- Array of values: SELECT dept_id, JSON_ARRAYAGG(employee_name) AS employees FROM employees GROUP BY dept_id; -- Object from key-value pairs: SELECT JSON_OBJECTAGG(setting_key, setting_value) AS config FROM app_settings WHERE user_id = 1; -- Aggregate full JSON objects (nest JSON_OBJECT inside): SELECT dept_id, JSON_ARRAYAGG( JSON_OBJECT('name', employee_name, 'salary', salary) ) AS staff FROM employees GROUP BY dept_id;

PostgreSQL

json_agg(expr) aggregates into a json array (since 9.3); jsonb_agg(expr) returns jsonb (since 9.4). json_object_agg(key, value) / jsonb_object_agg(key, value) aggregate into a JSON object. ORDER BY is supported inside the aggregate. NULL values are included as JSON null. Use FILTER (WHERE ...) to exclude rows.

-- json array (ordered): SELECT dept_id, json_agg(employee_name ORDER BY employee_name) AS employees FROM employees GROUP BY dept_id; -- jsonb array: SELECT dept_id, jsonb_agg(employee_name) AS employees FROM employees GROUP BY dept_id; -- Object from key-value pairs: SELECT json_object_agg(setting_key, setting_value) AS config FROM app_settings WHERE user_id = 1; -- Aggregate full row as JSON: SELECT dept_id, jsonb_agg(to_jsonb(e) - 'dept_id') AS staff FROM employees e GROUP BY dept_id;

SQL Server

No JSON_ARRAYAGG or json_agg. Use FOR JSON PATH / FOR JSON AUTO on a subquery to produce a JSON array. STRING_AGG can be combined with JSON_OBJECT (SQL Server 2022+) for simple cases. The result of FOR JSON is NVARCHAR, not a typed JSON column.

-- Subquery with FOR JSON PATH: SELECT d.dept_id, (SELECT e.employee_name FROM employees e WHERE e.dept_id = d.dept_id FOR JSON PATH) AS employees FROM departments d; -- STRING_AGG + manual JSON (simple case): SELECT dept_id, '[' + STRING_AGG('"' + employee_name + '"', ',') + ']' AS employees FROM employees GROUP BY dept_id;

Oracle

JSON_ARRAYAGG(expr) and JSON_OBJECTAGG(key VALUE value) added in Oracle 18c. In Oracle 12c, workarounds include LISTAGG combined with manual JSON construction, or using JSON_ARRAY with a subquery. ORDER BY and ON NULL clauses supported inside JSON_ARRAYAGG.

-- Array of values (18c+): SELECT dept_id, JSON_ARRAYAGG(employee_name ORDER BY employee_name) AS employees FROM employees GROUP BY dept_id; -- Object from key-value pairs (18c+): SELECT JSON_OBJECTAGG(setting_key VALUE setting_value) AS config FROM app_settings WHERE user_id = 1; -- 12c workaround with LISTAGG: SELECT dept_id, '[' || LISTAGG('"' || employee_name || '"', ',') WITHIN GROUP (ORDER BY employee_name) || ']' AS employees FROM employees GROUP BY dept_id;

SQLite

json_group_array(expr) aggregates values into a JSON array. json_group_object(name, value) aggregates into a JSON object. Both available since 3.9.0 when the JSON1 extension was added. In most SQLite distributions the JSON1 extension is compiled in by default.

-- Array of values: SELECT dept_id, json_group_array(employee_name) AS employees FROM employees GROUP BY dept_id; -- Object from key-value pairs: SELECT json_group_object(setting_key, setting_value) AS config FROM app_settings WHERE user_id = 1; -- Aggregate JSON objects: SELECT dept_id, json_group_array( json_object('name', employee_name, 'salary', salary) ) AS staff FROM employees GROUP BY dept_id;