STRING_AGG and ARRAY_AGG in SQL
STRING_AGG and ARRAY_AGG are aggregate functions that collect multiple row values into a single output value per group. STRING_AGG concatenates values into a delimited string. ARRAY_AGG collects values into a PostgreSQL array. Both collapse a set of rows into one row, like COUNT or SUM, and their output is the collection of contributing values in a structured form.
STRING_AGG and ARRAY_AGG collect multiple row values into a single value per group. Instead of reducing rows to a count or sum, they package the values together and hand them back as a container.
This pattern shows up whenever you need to present grouped data in a single field: a comma-separated list of products in an order, a pipe-delimited set of tags per user, an array of event types per session. Standard aggregates like COUNT and SUM reduce the individual values to a single number. STRING_AGG and ARRAY_AGG preserve them.
The concrete use case: your manager wants a report where each customer is a single row with all the product names they've ever ordered listed together. GROUP BY collapses the rows, but COUNT and SUM throw away the individual names. STRING_AGG keeps them:
This produces one row per customer with a comma-separated list of every product they've ordered. The ', ' is the delimiter — it goes between values, not after the last one. The ORDER BY inside the aggregate controls the sequence of names within the list. Without it, the order is unpredictable.
ARRAY_AGG does the same thing but produces a PostgreSQL array instead of a text string:
SELECT
user_id,
ARRAY_AGG(tag ORDER BY tag) AS user_tags
FROM user_tag_assignments
GROUP BY user_idEach user gets an array of their tags. Arrays can be passed to functions, indexed into directly, and unnested back into rows with unnest(). Use ARRAY_AGG when the collected values need to stay structured for further processing. Use STRING_AGG when the goal is a display-ready text value.
The one thing that trips people up
STRING_AGG ignores NULLs silently — a NULL input row contributes nothing to the string. ARRAY_AGG includes NULLs by default, producing NULL elements in the output array. If a NULL in the array would break downstream logic, exclude them explicitly:
ARRAY_AGG(tag) FILTER (WHERE tag IS NOT NULL)The ORDER BY inside STRING_AGG and ARRAY_AGG is also separate from the ORDER BY at the query level. The one inside the aggregate controls element order within each collected value. The query-level ORDER BY controls which groups appear first in your results. Both can coexist and neither affects the other.
Both functions also accept DISTINCT to deduplicate before collecting:
STRING_AGG(DISTINCT category, ', ' ORDER BY category)This is useful when the source table has duplicate values per group that you don't want repeated in the output.
ARRAY_AGG and unnest()
One reason to reach for ARRAY_AGG over STRING_AGG is that arrays stay structured. If you later need to work with the individual elements again — filter on them, count unique values, or expand them back into rows — you can use unnest() to convert the array back into individual rows. A STRING_AGG result is just text; once values are concatenated into a string, you can't easily get them back out. Choose STRING_AGG for display and reporting. Choose ARRAY_AGG when the collected values need to remain queryable downstream.
9 STRING_AGG and ARRAY_AGG practice problems
Write a query to return every customer's ID and a comma-separated list of their order statuses, with the statuses arranged in alphabetical order within each list.
Write a query to return every session ID and an array of all event types that occurred in that session, with the array elements arranged in alphabetical order.
Write a query to return every department ID and a comma-separated list of employee names, with the names arranged alphabetically within each list.
Write a query to return every order ID and its product IDs as a comma-separated string, with the IDs arranged in ascending numeric order.
Write a query to return every customer ID and an array of all their order amounts, with the amounts sorted from smallest to largest within each array.
Write a query to return every session ID and a comma-separated list of the unique event types that occurred in that session, in alphabetical order.
Write a query to return every department ID and a semicolon-separated list of employee entries, where each entry is the employee's `name` followed by ` - ` and their `title`. Entries should be arranged alphabetically by employee name within each list.
Write a query to return every customer ID and a comma-separated list of order statuses arranged in ascending order of `total_amount` within that customer's history. Sort the final result by `customer_id` ascending.
Write a query to return every country, a comma-separated text list of `city` values in alphabetical order, and an array of `city` values in alphabetical order.
These problems are part of the STRING_AGG and ARRAY_AGG lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.