{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"string-aggregation-and-arrays","title":"Listing the members of a pile: string_agg and array_agg","url":"https://addaly.com/learn/data-and-sql/string-aggregation-and-arrays","position":29,"of":66,"minutes":7,"answer":"string_agg and array_agg keep a pile's members instead of counting them; order inside the aggregate, watch MySQL's silent 1,024-character cut, and never store the joined list back into a column.","keyIdea":"string_agg and array_agg keep a pile's members instead of counting them; order inside the aggregate, watch MySQL's silent 1,024-character cut, and never store the joined list back into a column."},"body":{"format":"markdown","text":"## \"How many\" is not always the question\n\n`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.\n\n## string_agg: one row, one readable list\n\n```sql\nSELECT customer_id,\n       count(*)                                   AS orders,\n       string_agg(order_ref, ', ' ORDER BY placed_at) AS order_list\nFROM orders\nGROUP BY customer_id;\n```\n\nThe 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\".\n\nThe 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.\n\n## The silent truncation in MySQL\n\nMySQL'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.\n\n## array_agg: keeping the values as values\n\nA 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:\n\n```sql\nSELECT customer_id,\n       array_agg(product_id ORDER BY placed_at) AS products\nFROM order_items\nGROUP BY customer_id;\n```\n\nPostgres 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.\n\n```sql\n-- customers who bought product 88 and later bought 301\nSELECT customer_id\nFROM (SELECT customer_id, array_agg(product_id ORDER BY placed_at) AS p\n      FROM order_items GROUP BY customer_id) t\nWHERE array_position(p, 88) < array_position(p, 301);\n```\n\nThat is a \"did A happen before B\" question answered without a self-join, which the earlier lesson showed can be awkward.\n\n## Where this stops being a good idea\n\nAn 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.\n\nThere 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.\n\n## The habit\n\nReach 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."},"check":{"question":"A MySQL report lists each customer's order references with `group_concat(order_ref)`. For the biggest customers the list appears to end part-way through a reference, and no error is shown. What is happening?","options":[{"text":"`group_concat` cannot handle groups with more than a few hundred rows and stops aggregating once its internal buffer of rows is full, keeping what it has.","correct":false,"tempting":"There is no row limit; the limit is on the length of the output string, and it is configurable."},{"text":"The result is being cut at `group_concat_max_len`, 1,024 characters by default, and MySQL truncates rather than raising an error.","correct":true},{"text":"The order references contain commas, so the separator is splitting references into pieces that look truncated.","correct":false,"tempting":"Commas inside values would produce extra separators throughout the list, not a clean cut for only the largest customers."},{"text":"Some references are NULL, and `group_concat` stops at the first NULL it meets in the group.","correct":false,"tempting":"Aggregates skip NULLs entirely rather than stopping at them, and a skipped value would not cut a reference in half."}]},"prev":{"slug":"ratios-and-rates","title":"Rates: a numerator, a denominator, and the same grain for both","url":"https://addaly.com/learn/data-and-sql/ratios-and-rates"},"next":{"slug":"the-row-behind-the-max","title":"The row behind the MAX: which order was the biggest","url":"https://addaly.com/learn/data-and-sql/the-row-behind-the-max"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Listing the members of a pile: string_agg and array_agg', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/string-aggregation-and-arrays (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Listing the members of a pile: string_agg and array_agg","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"string_agg and array_agg keep a pile’s members instead of counting them; order inside the aggregate, watch MySQL’s silent 1,024-character cut, and never store the joined list back into a column.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"how-many-is-not-always-the-question","runs":[{"text":"“How many” is not always the question"}]},{"t":"p","runs":[{"text":"count(*)","code":true},{"text":" 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."}]},{"t":"h2","id":"stringagg-one-row-one-readable-list","runs":[{"text":"string_agg: one row, one readable list"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT customer_id,\n       count(*)                                   AS orders,\n       string_agg(order_ref, ', ' ORDER BY placed_at) AS order_list\nFROM orders\nGROUP BY customer_id;"},{"t":"p","runs":[{"text":"The output is one row per customer with a column like "},{"text":"A-1041, A-1187, A-1302, A-1350","code":true},{"text":". The "},{"text":"ORDER BY","code":true},{"text":" 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. "},{"text":"string_agg(DISTINCT city, ', ')","code":true},{"text":" collapses repeats, which is what you want for “which cities has this customer ordered from”."}]},{"t":"p","runs":[{"text":"The same function exists nearly everywhere under a different name. Postgres and DuckDB say "},{"text":"string_agg","code":true},{"text":"; SQLite and MySQL say "},{"text":"group_concat","code":true},{"text":"; BigQuery says "},{"text":"STRING_AGG","code":true},{"text":", and its "},{"text":"STRING_AGG(order_ref, ', ' ORDER BY placed_at)","code":true},{"text":" matches Postgres almost character for character. The syntax for ordering inside the aggregate varies, and SQLite’s "},{"text":"group_concat","code":true},{"text":" cannot order at all unless you feed it from an ordered subquery."}]},{"t":"h2","id":"the-silent-truncation-in-mysql","runs":[{"text":"The silent truncation in MySQL"}]},{"t":"p","runs":[{"text":"MySQL’s "},{"text":"group_concat","code":true},{"text":" 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 "},{"text":"SET SESSION group_concat_max_len = 1000000;","code":true},{"text":" before any query that could produce a long list, and treat any exported list whose length is exactly 1,024 as suspect."}]},{"t":"h2","id":"arrayagg-keeping-the-values-as-values","runs":[{"text":"array_agg: keeping the values as values"}]},{"t":"p","runs":[{"text":"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:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT customer_id,\n       array_agg(product_id ORDER BY placed_at) AS products\nFROM order_items\nGROUP BY customer_id;"},{"t":"p","runs":[{"text":"Postgres returns a real array, "},{"text":"{88,12,88,301}","code":true},{"text":", with functions to match: "},{"text":"cardinality(products)","code":true},{"text":" for its length, "},{"text":"products[1]","code":true},{"text":" for the first, "},{"text":"88 = ANY(products)","code":true},{"text":" to test membership. DuckDB calls this "},{"text":"list()","code":true},{"text":" and returns a list, with "},{"text":"len(l)","code":true},{"text":", "},{"text":"l[1]","code":true},{"text":", "},{"text":"list_contains(l, 88)","code":true},{"text":" and "},{"text":"list_position","code":true},{"text":" as the matching functions. SQLite has no array type at all; "},{"text":"json_group_array","code":true},{"text":" builds a JSON array you can read back with "},{"text":"json_each","code":true},{"text":", which is the nearest thing it offers. "},{"text":"unnest(products)","code":true},{"text":" turns the array back into rows, so you can aggregate, join to it, and re-aggregate without a temporary table."}]},{"t":"code","lang":"sql","label":"SQL","text":"-- customers who bought product 88 and later bought 301\nSELECT customer_id\nFROM (SELECT customer_id, array_agg(product_id ORDER BY placed_at) AS p\n      FROM order_items GROUP BY customer_id) t\nWHERE array_position(p, 88) < array_position(p, 301);"},{"t":"p","runs":[{"text":"That is a “did A happen before B” question answered without a self-join, which the earlier lesson showed can be awkward."}]},{"t":"h2","id":"where-this-stops-being-a-good-idea","runs":[{"text":"Where this stops being a good idea"}]},{"t":"p","runs":[{"text":"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. "},{"text":"tags = 'red, sale, new'","code":true},{"text":" cannot be indexed usefully, cannot be joined, cannot be counted per tag without splitting the string, and will one day contain "},{"text":"'red,sale'","code":true},{"text":" without the space. Aggregate for display and export; store one value per row."}]},{"t":"p","runs":[{"text":"There is also a size question. "},{"text":"string_agg","code":true},{"text":" 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. "},{"text":"array_agg(x ORDER BY placed_at)[1:10]","code":true},{"text":" in Postgres, or "},{"text":"list(x)[1:10]","code":true},{"text":" in DuckDB, gives the first ten without building the rest."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Reach for "},{"text":"string_agg","code":true},{"text":" when a person will read the result and "},{"text":"array_agg","code":true},{"text":" 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."}]}],"check":{"question":[{"text":"A MySQL report lists each customer’s order references with "},{"text":"group_concat(order_ref)","code":true},{"text":". For the biggest customers the list appears to end part-way through a reference, and no error is shown. What is happening?"}],"options":[{"text":[{"text":"group_concat","code":true},{"text":" cannot handle groups with more than a few hundred rows and stops aggregating once its internal buffer of rows is full, keeping what it has."}],"correct":false,"tempting":[{"text":"There is no row limit; the limit is on the length of the output string, and it is configurable."}]},{"text":[{"text":"The result is being cut at "},{"text":"group_concat_max_len","code":true},{"text":", 1,024 characters by default, and MySQL truncates rather than raising an error."}],"correct":true,"tempting":null},{"text":[{"text":"The order references contain commas, so the separator is splitting references into pieces that look truncated."}],"correct":false,"tempting":[{"text":"Commas inside values would produce extra separators throughout the list, not a clean cut for only the largest customers."}]},{"text":[{"text":"Some references are NULL, and "},{"text":"group_concat","code":true},{"text":" stops at the first NULL it meets in the group."}],"correct":false,"tempting":[{"text":"Aggregates skip NULLs entirely rather than stopping at them, and a skipped value would not cut a reference in half."}]}],"order":[2,3,0,1],"why":null},"prev":{"slug":"ratios-and-rates","title":"Rates: a numerator, a denominator, and the same grain for both","minutes":9},"next":{"slug":"the-row-behind-the-max","title":"The row behind the MAX: which order was the biggest","minutes":9}}}