Command guide
advanced json MySQL MariaDB SQL Server PostgreSQL SQLite
Aggregate Rows Into A JSON Array
Collect values from multiple rows into a single JSON array, ordered and grouped by other columns.
5/5 supported engines validation-green 1 example 2 scenarios
Docker-validated Not currently validation-green
Example 1
Collect employee names per department into a JSON array
Engineering has two members (Alice and Bob) and Finance has one (Carol). MySQL and PostgreSQL return a compact scalar array of strings. MariaDB achieves the same result by sorting in a derived table first, since its JSON_ARRAYAGG does not accept ORDER BY. SQL Server's FOR JSON PATH always wraps each value in an object keyed by the column name, producing an array of objects rather than plain strings. If your application needs a scalar array from SQL Server, consider extracting values in application code or building the array string manually with STRING_AGG.
Source table data Setup
CREATE TABLE employees (id INT, department VARCHAR(50), name VARCHAR(50));
INSERT INTO
employees (id, department, name)
VALUES
(1, 'Engineering', 'Alice'),
(2, 'Engineering', 'Bob'),
(3, 'Finance', 'Carol');
SQL
SELECT
department,
JSON_ARRAYAGG (name) AS members
FROM
(
SELECT
department,
name
FROM
employees
ORDER BY
department,
name
) sub
GROUP BY
department
ORDER BY
department;
| department | members |
|---|
| Engineering | ["Alice", "Bob"] |
| Finance | ["Carol"] |
Setup
CREATE TABLE employees (id INT, department VARCHAR(50), name VARCHAR(50));
INSERT INTO
employees (id, department, name)
VALUES
(1, 'Engineering', 'Alice'),
(2, 'Engineering', 'Bob'),
(3, 'Finance', 'Carol');
SQL
SELECT
department,
(
SELECT
name
FROM
employees e2
WHERE
e2.department = e.department
ORDER BY
name FOR JSON PATH
) AS members
FROM
employees e
GROUP BY
department
ORDER BY
department;
| department | members |
|---|
| Engineering | [{"name":"Alice"},{"name":"Bob"}] |
| Finance | [{"name":"Carol"}] |
Setup
CREATE TABLE employees (id INT, department VARCHAR(50), name VARCHAR(50));
INSERT INTO
employees (id, department, name)
VALUES
(1, 'Engineering', 'Alice'),
(2, 'Engineering', 'Bob'),
(3, 'Finance', 'Carol');
SQL
SELECT
department,
JSON_AGG (
name
ORDER BY
name
) AS members
FROM
employees
GROUP BY
department
ORDER BY
department;
| department | members |
|---|
| Engineering | ["Alice", "Bob"] |
| Finance | ["Carol"] |
Setup
CREATE TABLE employees (id INT, department VARCHAR(50), name VARCHAR(50));
INSERT INTO
employees (id, department, name)
VALUES
(1, 'Engineering', 'Alice'),
(2, 'Engineering', 'Bob'),
(3, 'Finance', 'Carol');
SQL
SELECT
department,
json_group_array (name) AS members
FROM
(
SELECT
department,
name
FROM
employees
ORDER BY
department,
name
) sub
GROUP BY
department
ORDER BY
department;
| department | members |
|---|
| Engineering | ["Alice","Bob"] |
| Finance | ["Carol"] |
MySQL and PostgreSQL use JSON_ARRAYAGG / JSON_AGG with ORDER BY inside the aggregate. MariaDB uses a pre-ordered derived table. SQL Server FOR JSON PATH produces an array of objects [{"name":"Alice"},{"name":"Bob"}] instead of a scalar array ["Alice","Bob"]. MySQL, MariaDB, and PostgreSQL produce identical scalar arrays.