Tier 4 · Advanced

STRING_AGG and ARRAY_AGG in SQL

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

Before this GROUP BY, String Concatenation and Formatting, ORDER BY and Result Sorting

What are STRING_AGG and ARRAY_AGG in SQL?

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:

SELECT c.name AS customer_name,
  STRING_AGG(p.name, ', ' ORDER BY p.name) AS products_ordered
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY c.id, c.name
LIMIT 5

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.

What is the difference between STRING_AGG and ARRAY_AGG?

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_id

Each 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.

How do STRING_AGG and ARRAY_AGG handle NULL?

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.

How do you deduplicate values inside STRING_AGG?

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.

How do you turn an aggregated array back into rows?

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.

Practice STRING_AGG and ARRAY_AGG in SQL

Practice · easy ecommerce · Brightlane

Brightlane's CRM team wants a one-row summary per customer showing every status that customer has experienced across their order history.

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.

Assumptions:

The orders table has one row per order with a customer_id and a status.
Each customer_id with at least one order should appear once in the result.
For each customer, the status list contains every status value across that customer's orders (one entry per order, no de-duplication), separated by ', ' and arranged alphabetically.

Output:

One row per customer with at least one order, with columns customer_id and order_statuses.

Schema · ecommerce5 tables? = nullable

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.

easy ecommerce

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.

easy analytics

Write a query to return every department ID and a comma-separated list of employee names, with the names arranged alphabetically within each list.

easy hr

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.

medium ecommerce

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.

medium ecommerce

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.

medium analytics

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.

medium hr

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.

hard ecommerce

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.

hard ecommerce

Start learning to practice all 9 STRING_AGG and ARRAY_AGG problems, with instant grading and mastery tracking.

Deeper guides on STRING_AGG and ARRAY_AGG

what to write instead of GROUP_CONCAT
string_agg needs a delimiter, and the one-argument call fails with an error about the function not existing.
sorting the values inside the list
ORDER BY belongs inside the parentheses. At the end of the query it raises a GROUP BY error instead.

Common questions about STRING_AGG and ARRAY_AGG

Does the order of values inside STRING_AGG follow the query ORDER BY?

No. The ordering inside the aggregate is written inside its own brackets, and without it the order is arbitrary. The query-level ORDER BY decides which groups appear first and has no say over what happens within one.

Does ARRAY_AGG put a separator between values?

No, it builds an array rather than a string, so the elements stay separate rather than being joined by anything. STRING_AGG is the one that takes a separator, because a string needs something between the parts.

Can you deduplicate inside STRING_AGG?

Yes. Write DISTINCT before the value and repeated entries collapse before the join happens, so a product ordered three times appears once in the list rather than three times.