Can my DB?

SELECT dept_id,
  JSON_AGG(
    JSON_BUILD_OBJECT('id', id, 'name', name)
    ORDER BY name
  ) AS employees
FROM employees
GROUP BY dept_id;

• JSON_AGG(expr) aggregates values into a JSON array; JSONB_AGG(expr) produces the binary JSONB equivalent.
• JSON_OBJECT_AGG(key, value) produces a JSON object; JSONB_OBJECT_AGG(key, value) is the JSONB variant.
• JSON_AGG supports ORDER BY inside the aggregate call.
• NULL values in JSON_AGG produce JSON null; use FILTER (WHERE expr IS NOT NULL) to exclude them.

SELECT dept_id,
  JSON_ARRAYAGG(name) AS employee_names,
  JSON_OBJECTAGG(id, name) AS id_name_map
FROM employees
GROUP BY dept_id;

• JSON_ARRAYAGG(expr) aggregates values into a JSON array.
• JSON_OBJECTAGG(key, value) produces a JSON object.
• JSON_ARRAYAGG does not support ORDER BY inside the aggregate in all versions; use a subquery with ORDER BY for ordered results.

SELECT dept_id,
  JSON_ARRAYAGG(name ORDER BY name) AS employee_names,
  JSON_OBJECTAGG(id, name) AS id_name_map
FROM employees
GROUP BY dept_id;

• JSON_ARRAYAGG(expr) and JSON_OBJECTAGG(key, value) added in MariaDB 10.5.
• JSON_ARRAYAGG supports an ORDER BY clause inside the aggregate.

SELECT dept_id,
  JSON_ARRAYAGG(name ORDER BY name ABSENT ON NULL) AS employee_names,
  JSON_OBJECTAGG(KEY TO_CHAR(id) VALUE name) AS id_name_map
FROM employees
GROUP BY dept_id;

• JSON_ARRAYAGG and JSON_OBJECTAGG added in Oracle 12.2.
• Supports ORDER BY inside JSON_ARRAYAGG.
• ABSENT ON NULL / NULL ON NULL controls whether NULL values are included.
• JSON_OBJECT (scalar row function) is available since Oracle 12.1.

-- SQL Server 2022+
SELECT dept_id,
  JSON_ARRAYAGG(name) AS employee_names
FROM employees
GROUP BY dept_id;

-- Pre-2022 workaround using FOR JSON PATH
SELECT e.dept_id,
  (
    SELECT e2.id, e2.name
    FROM employees e2
    WHERE e2.dept_id = e.dept_id
    FOR JSON PATH
  ) AS employees
FROM departments e
GROUP BY e.dept_id;

• JSON_ARRAYAGG aggregate function added in SQL Server 2022.
• Prior to 2022, FOR JSON PATH / FOR JSON AUTO in a subquery is the standard pattern for returning a JSON array.
• FOR JSON PATH converts a result set directly to a JSON array string.

SELECT dept_id,
  JSON_GROUP_ARRAY(name) AS employee_names,
  JSON_GROUP_OBJECT(CAST(id AS TEXT), name) AS id_name_map
FROM employees
GROUP BY dept_id;

• JSON_GROUP_ARRAY(expr) and JSON_GROUP_OBJECT(key, value) added in SQLite 3.38.0 (2022-02-22).
• No ORDER BY clause inside the aggregate; order depends on query plan.

-- Workaround: construct a JSON array string with LISTAGG
SELECT dept_id,
  '[' || LISTAGG('"' || name || '"', ',')
        WITHIN GROUP (ORDER BY name) || ']' AS names
FROM employees
GROUP BY dept_id;

• No native JSON aggregation functions.
• Use LISTAGG with manual JSON string construction as a workaround for simple arrays.
• Redshift SUPER type supports JSON_PARSE and JSON_SERIALIZE, but not a native group-level JSON array/object aggregate.

SELECT dept_id,
  JSON_ARRAYAGG(name) AS employee_names,
  JSON_OBJECTAGG(CAST(id AS VARCHAR(30)) VALUE name) AS id_name_map
FROM employees
GROUP BY dept_id;

• Db2 documents native JSON_ARRAYAGG and JSON_OBJECTAGG aggregate functions.
• Older JSON/BSON helper interfaces also exist, but current Db2 provides native JSON aggregate support.
• Behavior and exact JSON storage representation can vary by JSON/BSON input format.

SELECT dept_id,
  ARRAY_AGG(name) WITHIN GROUP (ORDER BY name) AS employee_names,
  OBJECT_AGG(id::TEXT, name::VARIANT) AS id_name_map
FROM employees
GROUP BY dept_id;

• ARRAY_AGG(expr) aggregates values into a Snowflake ARRAY.
• OBJECT_AGG(key, value) produces a Snowflake OBJECT (requires distinct keys).
• ARRAY_AGG supports WITHIN GROUP (ORDER BY ...) for ordered results.
• TO_JSON() and PARSE_JSON() convert between string and VARIANT.

SELECT dept_id,
  ARRAY_AGG(
    STRUCT(id, name)
    IGNORE NULLS
    ORDER BY name
  ) AS employees,
  TO_JSON(ARRAY_AGG(STRUCT(id, name))) AS employees_json
FROM employees
GROUP BY dept_id;

• ARRAY_AGG(expr IGNORE NULLS ORDER BY col) builds arrays with full ordering and null control.
• TO_JSON() converts a STRUCT or ARRAY to a JSON value.
• ARRAY_AGG can build arrays of STRUCTs which serve as JSON-like structures.
• JSON_OBJECT(key, value) available for building JSON objects directly.