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.

Created Apr 11, 2026 Last updated Apr 11, 2026 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.

MySQL MariaDB

Engine-specific syntax

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');

Validated query

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;

Expected result

department	members
Engineering	["Alice", "Bob"]
Finance	["Carol"]

SQL Server

Engine-specific syntax

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');

Validated query

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;

Expected result

department	members
Engineering	[{"name":"Alice"},{"name":"Bob"}]
Finance	[{"name":"Carol"}]

PostgreSQL

Engine-specific syntax

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');

Validated query

SQL

SELECT
  department,
  JSON_AGG (
    name
    ORDER BY
      name
  ) AS members
FROM
  employees
GROUP BY
  department
ORDER BY
  department;

Expected result

department	members
Engineering	["Alice", "Bob"]
Finance	["Carol"]

SQLite

Engine-specific syntax

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');

Validated query

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;

Expected result

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.

Useful when

Where this command helps.

returning all tags or labels for each record as a JSON array in a single column
building a denormalized JSON payload from a one-to-many relationship for an API response

Explanation

What the command is doing.

JSON array aggregation is useful when you need to return a list of related values as a single JSON column — for example, all tags for a post, or all product names per order. MySQL 8.0+ provides JSON_ARRAYAGG(expr ORDER BY ...) which returns a JSON array of scalar values. MariaDB's JSON_ARRAYAGG does not support ORDER BY inside the aggregate; use a pre-sorted derived table to guarantee order. PostgreSQL uses JSON_AGG(expr ORDER BY ...). SQL Server has no scalar JSON array aggregate — FOR JSON PATH produces an array of objects rather than an array of scalars, which is a structural difference that warrants per-engine display.

Aggregate Rows Into A JSON Array

Collect employee names per department into a JSON array

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.