Aggregate values into an array (ARRAY_AGG / JSON_ARRAYAGG)

“All order IDs per customer, as an array.” PostgreSQL, BigQuery, DuckDB and Snowflake return a real array type; MySQL and SQLite return a JSON array instead, since neither has a native array column type.

-- PostgreSQL, DuckDB  (native ARRAY, ordered inside the call)
SELECT customer_id, array_agg(product ORDER BY product) AS products
FROM orders GROUP BY customer_id;

-- BigQuery  (also supports LIMIT inside the call: ARRAY_AGG(product ORDER BY product LIMIT 5))
SELECT customer_id, ARRAY_AGG(product ORDER BY product) AS products
FROM orders GROUP BY customer_id;

-- Snowflake  (ordering goes in a separate WITHIN GROUP clause, not inside the parens)
SELECT customer_id, ARRAY_AGG(product) WITHIN GROUP (ORDER BY product) AS products
FROM orders GROUP BY customer_id;

-- MySQL 8+  (JSON array; JSON_ARRAYAGG has no ORDER BY argument — order the source rows first)
SELECT customer_id, JSON_ARRAYAGG(product) AS products
FROM (SELECT customer_id, product FROM orders ORDER BY customer_id, product) AS ordered
GROUP BY customer_id;

-- SQLite 3.44+  (json_group_array now accepts ORDER BY, like any aggregate)
SELECT customer_id, json_group_array(product ORDER BY product) AS products
FROM orders GROUP BY customer_id;

-- SQLite, older versions  (order the source rows first, same trick as MySQL)
SELECT customer_id, json_group_array(product) AS products
FROM (SELECT customer_id, product FROM orders ORDER BY customer_id, product)
GROUP BY customer_id;

Array type vs JSON array. PostgreSQL, BigQuery, Snowflake and DuckDB give you a first-class array value you can index, UNNEST, or pass to other array functions. MySQL’s JSON_ARRAYAGG and SQLite’s json_group_array instead return a JSON-typed string — fine to send to a client as JSON, but you’ll need JSON_EXTRACT/-> or another UNNEST-style step to work with individual elements back in SQL. DuckDB also has a plain list(product ORDER BY product), a lighter-weight alias for array_agg that some people find reads better.

Deduplicate with DISTINCT inside the aggregate, same as any other aggregate function:

SELECT array_agg(DISTINCT product) FROM orders;         -- PostgreSQL, DuckDB
SELECT ARRAY_AGG(DISTINCT product) FROM orders;          -- BigQuery, Snowflake
SELECT JSON_ARRAYAGG(DISTINCT product) FROM orders;      -- not supported in MySQL — dedupe in a subquery