Listing the members of a pile: string_agg and array_agg

“How many” is not always the question

count(*) tells you a customer placed 4 orders. The person asking often wants to know which four. GROUP BY seems to forbid that, since the pile has to collapse into one row and there are four order numbers. The way out is an aggregate that keeps the values instead of summarising them.

string_agg: one row, one readable list

SELECT customer_id,
       count(*)                                   AS orders,
       string_agg(order_ref, ', ' ORDER BY placed_at) AS order_list
FROM orders
GROUP BY customer_id;

The output is one row per customer with a column like A-1041, A-1187, A-1302, A-1350. The ORDER BY inside the aggregate is what makes the list predictable; without it the order is whatever the engine happened to do, and it changes between runs. string_agg(DISTINCT city, ', ') collapses repeats, which is what you want for “which cities has this customer ordered from”.

The same function exists nearly everywhere under a different name. Postgres and DuckDB say string_agg; SQLite and MySQL say group_concat; BigQuery says STRING_AGG, and its STRING_AGG(order_ref, ', ' ORDER BY placed_at) matches Postgres almost character for character. The syntax for ordering inside the aggregate varies, and SQLite’s group_concat cannot order at all unless you feed it from an ordered subquery.

The silent truncation in MySQL

MySQL’s group_concat stops at 1,024 characters by default and does not raise an error. It returns the first 1,024 characters of the list. A customer with 200 orders gets a list that ends mid-reference, and nothing in the result says so. Raise the limit for the session with SET SESSION group_concat_max_len = 1000000; before any query that could produce a long list, and treat any exported list whose length is exactly 1,024 as suspect.

array_agg: keeping the values as values

A comma-joined string is for people to read. When the list is for a program, or you will need to filter or count it later, keep it as an array:

SELECT customer_id,
       array_agg(product_id ORDER BY placed_at) AS products
FROM order_items
GROUP BY customer_id;

Postgres returns a real array, {88,12,88,301}, with functions to match: cardinality(products) for its length, products[1] for the first, 88 = ANY(products) to test membership. DuckDB calls this list() and returns a list, with len(l), l[1], list_contains(l, 88) and list_position as the matching functions. SQLite has no array type at all; json_group_array builds a JSON array you can read back with json_each, which is the nearest thing it offers. unnest(products) turns the array back into rows, so you can aggregate, join to it, and re-aggregate without a temporary table.

-- customers who bought product 88 and later bought 301
SELECT customer_id
FROM (SELECT customer_id, array_agg(product_id ORDER BY placed_at) AS p
      FROM order_items GROUP BY customer_id) t
WHERE array_position(p, 88) < array_position(p, 301);

That is a “did A happen before B” question answered without a self-join, which the earlier lesson showed can be awkward.

Where this stops being a good idea

An aggregated list in a query result is fine. An aggregated list stored in a table column is the mistake the lesson on keeping each fact in one place warned about. tags = 'red, sale, new' cannot be indexed usefully, cannot be joined, cannot be counted per tag without splitting the string, and will one day contain 'red,sale' without the space. Aggregate for display and export; store one value per row.

There is also a size question. string_agg over a million-row group builds a string of tens of megabytes in memory. If the list would be too long for a human to read, the human does not want the list; they want a count, a sample, or the first ten. array_agg(x ORDER BY placed_at)[1:10] in Postgres, or list(x)[1:10] in DuckDB, gives the first ten without building the rest.

The habit

Reach for string_agg when a person will read the result and array_agg when a program will. Always order inside the aggregate. In MySQL, raise the length limit first. And if you find yourself storing the result, stop and add a table instead.