Array aggregation with ARRAY_AGG in PostgreSQL - Deep Dive

Overview - Array aggregation with ARRAY_AGG

What is it?

ARRAY_AGG is a function in PostgreSQL that collects multiple values from rows into a single array. It helps you group data from many rows into one list, making it easier to work with related data together. This function is useful when you want to see all values from a group in one place instead of separate rows. It returns an array containing all the input values in the order they appear.

Why it matters

Without ARRAY_AGG, you would have to handle multiple rows separately when you want to see grouped data, which can be confusing and inefficient. ARRAY_AGG simplifies data analysis by combining related items into one array, making it easier to process and understand grouped information. This is especially helpful in reports, dashboards, or any place where you want to see collections of related data together.

Where it fits

Before learning ARRAY_AGG, you should understand basic SQL SELECT queries and GROUP BY clauses. After mastering ARRAY_AGG, you can explore more advanced array functions, JSON aggregation, and window functions to handle complex data transformations.

Mental Model

Core Idea

ARRAY_AGG gathers multiple row values into a single array, turning many rows into one grouped list.

Think of it like...

Imagine collecting all your friends' phone numbers on separate pieces of paper and then putting them together into one phonebook. ARRAY_AGG is like making that phonebook from many separate notes.

┌───────────────┐
│ Table rows    │
│ ┌───────────┐ │
│ │ Value 1   │ │
│ │ Value 2   │ │
│ │ Value 3   │ │
│ └───────────┘ │
└──────┬────────┘
       │ ARRAY_AGG
       ▼
┌─────────────────────┐
│ Single array result  │
│ [Value 1, Value 2,   │
│  Value 3]            │
└─────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding basic aggregation

Concept: Learn what aggregation means in SQL and how it groups data.

Aggregation means combining multiple rows into a single summary value. For example, SUM adds numbers, COUNT counts rows, and AVG finds averages. GROUP BY lets you aggregate data by categories, like counting how many orders each customer made.

Result

You can summarize data by groups, like total sales per customer.

Understanding aggregation is key because ARRAY_AGG is a type of aggregation that collects values instead of summarizing them.

2

Foundation

Introduction to arrays in PostgreSQL

Concept: Learn what arrays are and how PostgreSQL stores them.

An array is a list of values stored in one column. PostgreSQL supports arrays of any data type, like integers or text. You can create arrays using curly braces, e.g., '{1,2,3}', and access elements by position.

Result

You know how to recognize and write arrays in PostgreSQL.

Knowing arrays lets you understand the output format of ARRAY_AGG, which returns an array of values.

3

Intermediate

Using ARRAY_AGG to group values

🤔Before reading on: do you think ARRAY_AGG returns values in any order or preserves the original row order? Commit to your answer.

Concept: Learn how to use ARRAY_AGG to collect grouped values into arrays.

ARRAY_AGG is used with GROUP BY to collect values from multiple rows into one array per group. For example, SELECT department, ARRAY_AGG(employee_name) FROM employees GROUP BY department; collects all employee names per department.

Result

You get one row per group with an array of all values in that group.

Understanding how ARRAY_AGG groups values helps you combine related data efficiently for easier analysis.

4

Intermediate

Ordering elements inside ARRAY_AGG

🤔Before reading on: do you think you can control the order of elements inside ARRAY_AGG? Commit to yes or no.

Concept: Learn how to specify the order of values inside the aggregated array.

You can use ORDER BY inside ARRAY_AGG to control the order of elements. For example, ARRAY_AGG(employee_name ORDER BY hire_date) returns names sorted by hire date within each group.

Result

The array elements appear in the order you specify, not just any order.

Knowing how to order elements inside ARRAY_AGG lets you create meaningful, predictable arrays.

5

Intermediate

Handling duplicates with ARRAY_AGG

🤔Before reading on: does ARRAY_AGG automatically remove duplicate values? Commit to yes or no.

Concept: Learn how ARRAY_AGG treats duplicate values and how to remove them.

ARRAY_AGG includes all values, even duplicates. To remove duplicates, use ARRAY_AGG(DISTINCT column_name). For example, ARRAY_AGG(DISTINCT employee_name) collects unique names only.

Result

You can get arrays with or without duplicates depending on your needs.

Understanding duplicates helps you control data quality in aggregated arrays.

6

Advanced

Combining ARRAY_AGG with filters

🤔Before reading on: can you filter which rows contribute to ARRAY_AGG inside the aggregation? Commit to yes or no.

Concept: Learn how to filter rows inside ARRAY_AGG using FILTER clause.

You can use FILTER to include only certain rows in ARRAY_AGG. For example, ARRAY_AGG(employee_name) FILTER (WHERE active = true) collects names only for active employees.

Result

You get arrays that include only filtered data, making aggregation more precise.

Knowing how to filter inside ARRAY_AGG lets you build more targeted and efficient queries.

7

Expert

Performance and memory considerations

🤔Before reading on: do you think ARRAY_AGG can cause performance issues with very large groups? Commit to yes or no.

Concept: Understand how ARRAY_AGG works internally and its impact on performance.

ARRAY_AGG builds arrays in memory for each group, so very large groups can consume significant memory and slow queries. PostgreSQL processes rows sequentially, appending values to arrays. Using ORDER BY or DISTINCT inside ARRAY_AGG adds extra processing. Planning indexes and query structure helps optimize performance.

Result

You can write efficient queries that use ARRAY_AGG without slowing down your database.

Understanding internal behavior prevents performance pitfalls and helps design scalable queries.

Under the Hood

ARRAY_AGG works by scanning rows in each group and appending each value to an internal array structure in memory. It maintains the order of rows unless ORDER BY is specified. When DISTINCT is used, it tracks seen values to avoid duplicates. After processing all rows in a group, it returns the built array as the result for that group.

Why designed this way?

ARRAY_AGG was designed to provide a simple way to collect multiple values into one result, improving readability and usability of grouped data. It balances flexibility and performance by allowing ordering and distinct filtering. Alternatives like string concatenation were less structured and harder to use for complex data.

┌───────────────┐
│ Input rows    │
│ ┌───────────┐ │
│ │ Value 1   │ │
│ │ Value 2   │ │
│ │ Value 3   │ │
│ └───────────┘ │
└──────┬────────┘
       │
       ▼
┌─────────────────────┐
│ ARRAY_AGG function   │
│ ┌─────────────────┐ │
│ │ Append values   │ │
│ │ Track DISTINCT  │ │
│ │ Apply ORDER BY  │ │
│ └─────────────────┘ │
└──────┬──────────────┘
       │
       ▼
┌─────────────────────┐
│ Output array result  │
│ [Value 1, Value 2,   │
│  Value 3]            │
└─────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does ARRAY_AGG automatically sort the values it collects? Commit to yes or no.

Common Belief: ARRAY_AGG always returns values sorted in ascending order.

Reality: ARRAY_AGG returns values in the order they appear in the input unless you explicitly use ORDER BY inside it.

Why it matters: Assuming automatic sorting can lead to wrong assumptions about data order, causing bugs in reports or applications relying on order.

Quick: Does ARRAY_AGG remove duplicate values by default? Commit to yes or no.

Common Belief: ARRAY_AGG automatically removes duplicate values when aggregating.

Reality: ARRAY_AGG includes all values, duplicates included, unless you use DISTINCT explicitly.

Why it matters: Not knowing this can cause unexpected repeated values in arrays, leading to incorrect data analysis.

Quick: Can ARRAY_AGG be used without GROUP BY to aggregate all rows? Commit to yes or no.

Common Belief: ARRAY_AGG requires GROUP BY to work properly.

Reality: ARRAY_AGG can be used without GROUP BY to aggregate all rows in the query result into one array.

Why it matters: Missing this limits query design options and can lead to more complex or inefficient queries.

Quick: Does ARRAY_AGG always produce the same array size regardless of input size? Commit to yes or no.

Common Belief: ARRAY_AGG output size is fixed or limited by default.

Reality: ARRAY_AGG output size depends on the number of input rows; large groups produce large arrays, which can impact memory and performance.

Why it matters: Ignoring this can cause performance issues or crashes when aggregating very large datasets.

Expert Zone

1

ARRAY_AGG preserves NULL values inside arrays unless filtered out, which can affect downstream processing.

2

Using ORDER BY inside ARRAY_AGG can significantly increase query cost because it requires sorting within each group.

3

ARRAY_AGG combined with DISTINCT uses hashing internally, which can impact memory usage and performance on large datasets.

When NOT to use

Avoid ARRAY_AGG when aggregating extremely large groups that may cause memory bloat; consider using set-returning functions or pagination instead. For string concatenation, STRING_AGG might be more appropriate. When you need complex nested structures, JSON aggregation functions like JSON_AGG are better suited.

Production Patterns

ARRAY_AGG is commonly used to collect related child records into arrays for parent rows, such as gathering all order items per order. It is also used in reporting to show grouped lists, and combined with filters and ordering to create precise, readable summaries. In APIs, ARRAY_AGG helps prepare data for JSON responses efficiently.

Connections

JSON aggregation

builds-on

Understanding ARRAY_AGG helps grasp JSON_AGG, which similarly collects rows but outputs JSON arrays, useful for APIs and nested data.

Functional programming reduce operation

similar pattern

ARRAY_AGG acts like a reduce operation that accumulates values into a collection, showing how database functions mirror programming concepts.

Data compression

opposite pattern

While ARRAY_AGG expands multiple rows into one array, data compression reduces data size; understanding both helps balance data storage and retrieval.

Common Pitfalls

#1 Assuming ARRAY_AGG returns sorted arrays without specifying order.

Wrong approach:

SELECT department, ARRAY_AGG(employee_name) FROM employees GROUP BY department;

Correct approach:

SELECT department, ARRAY_AGG(employee_name ORDER BY employee_name) FROM employees GROUP BY department;

Root cause:

Misunderstanding that ARRAY_AGG preserves input order but does not sort automatically.

#2 Expecting ARRAY_AGG to remove duplicates automatically.

Wrong approach:

SELECT department, ARRAY_AGG(employee_name) FROM employees GROUP BY department;

Correct approach:

SELECT department, ARRAY_AGG(DISTINCT employee_name) FROM employees GROUP BY department;

Root cause:

Not knowing DISTINCT must be explicitly used inside ARRAY_AGG to remove duplicates.

#3 Using ARRAY_AGG on very large groups without considering performance.

Wrong approach:

SELECT category, ARRAY_AGG(product_name) FROM products GROUP BY category;

Correct approach:

Use pagination or limit group size, or consider alternative aggregation methods for large datasets.

Root cause:

Ignoring memory and processing limits when aggregating large numbers of rows.

Key Takeaways

ARRAY_AGG collects multiple row values into a single array, simplifying grouped data handling.

You can control the order and uniqueness of elements inside ARRAY_AGG using ORDER BY and DISTINCT.

ARRAY_AGG works with or without GROUP BY, allowing flexible aggregation of data.

Understanding ARRAY_AGG's internal behavior helps avoid performance issues with large datasets.

ARRAY_AGG is a foundational tool for building complex data summaries and preparing data for JSON or application use.

Practice

1. What does the PostgreSQL function ARRAY_AGG do?

easy

A. It counts the number of rows in a table.

B. It collects multiple row values into a single array.

C. It sorts rows in ascending order.

D. It deletes duplicate rows from a table.

Solution

1. Step 1: Understand the purpose of ARRAY_AGG

   ARRAY_AGG is used to gather multiple values from rows into one array value.

2. Step 2: Compare with other options

   Counting rows uses COUNT(), deleting duplicates or sorting rows uses other clauses like DISTINCT or ORDER BY, not ARRAY_AGG.

3. Final Answer:

   It collects multiple row values into a single array. -> Option B

4. Quick Check:

   ARRAY_AGG = collects rows into array [OK]

Hint: ARRAY_AGG groups rows into one array [OK]

Common Mistakes:

• Confusing ARRAY_AGG with COUNT or ORDER BY
• Thinking ARRAY_AGG sorts data by default
• Assuming ARRAY_AGG removes duplicates automatically

2. Which of the following is the correct syntax to aggregate values into an array using ARRAY_AGG in PostgreSQL?

easy

A. SELECT ARRAY_AGG(column_name) FROM table_name;

B. SELECT ARRAY_AGG column_name FROM table_name;

C. SELECT ARRAY_AGG(column_name) table_name;

D. SELECT ARRAY_AGG(column_name) FROM table_name WHERE;

Solution

1. Step 1: Recall correct function call syntax

   ARRAY_AGG requires parentheses around the column name and a FROM clause.

2. Step 2: Check each option

   SELECT ARRAY_AGG(column_name) FROM table_name; uses correct syntax with parentheses and FROM clause; others miss parentheses or have syntax errors.

3. Final Answer:

   SELECT ARRAY_AGG(column_name) FROM table_name; -> Option A

4. Quick Check:

   Correct ARRAY_AGG syntax uses parentheses and FROM [OK]

Hint: Always use parentheses with ARRAY_AGG(column) [OK]

Common Mistakes:

• Omitting parentheses around column_name
• Leaving incomplete WHERE clause
• Missing FROM keyword

3. Given the table students with columns class and name, what is the output of this query?

SELECT class, ARRAY_AGG(name ORDER BY name) FROM students GROUP BY class ORDER BY class;

medium

A. [{class: '1A', array_agg: ['Alice']}, {class: '2B', array_agg: ['Charlie', 'David', 'Bob']}]

B. [{class: '1A', array_agg: ['Bob', 'Alice']}, {class: '2B', array_agg: ['David', 'Charlie']}]

C. [{class: '1A', array_agg: ['Alice', 'Charlie']}, {class: '2B', array_agg: ['Bob', 'David']}]

D. [{class: '1A', array_agg: ['Alice', 'Bob']}, {class: '2B', array_agg: ['Charlie', 'David']}]

Solution

1. Step 1: Understand GROUP BY and ARRAY_AGG with ORDER BY

   The query groups rows by class and collects names into arrays sorted alphabetically.

2. Step 2: Analyze the ordering inside ARRAY_AGG

   Names inside each class array are sorted ascending, so 'Alice' comes before 'Bob' in '1A', and 'Charlie' before 'David' in '2B'.

3. Final Answer:

   [{class: '1A', array_agg: ['Alice', 'Bob']}, {class: '2B', array_agg: ['Charlie', 'David']}] -> Option D

4. Quick Check:

   ARRAY_AGG with ORDER BY sorts array elements [OK]

Hint: ARRAY_AGG(... ORDER BY ...) sorts array elements [OK]

Common Mistakes:

• Ignoring ORDER BY inside ARRAY_AGG
• Mixing names between groups
• Assuming ARRAY_AGG sorts groups, not elements

4. Identify the error in this query:

SELECT class, ARRAY_AGG(name ORDER BY) FROM students GROUP BY class;

medium

A. Missing column name after ORDER BY inside ARRAY_AGG.

B. GROUP BY clause is not allowed with ARRAY_AGG.

C. ARRAY_AGG cannot be used with ORDER BY.

D. Missing FROM keyword before table name.

Solution

1. Step 1: Check syntax of ORDER BY inside ARRAY_AGG

   ORDER BY inside ARRAY_AGG requires a column name to specify sorting order.

2. Step 2: Validate other parts of the query

   GROUP BY is valid with ARRAY_AGG; FROM clause is present; ORDER BY without column is invalid.

3. Final Answer:

   Missing column name after ORDER BY inside ARRAY_AGG. -> Option A

4. Quick Check:

   ORDER BY needs a column name inside ARRAY_AGG [OK]

Hint: ORDER BY inside ARRAY_AGG needs a column name [OK]

Common Mistakes:

• Leaving ORDER BY empty inside ARRAY_AGG
• Thinking GROUP BY disallows ARRAY_AGG
• Forgetting to specify sorting column

5. You have a table orders with columns customer_id and product. You want to create a query that returns each customer_id with an array of unique products they ordered, sorted alphabetically. Which query achieves this?

hard

A. SELECT customer_id, ARRAY_AGG(product ORDER BY product) FROM orders GROUP BY customer_id;

B. SELECT customer_id, ARRAY_AGG(DISTINCT product) FROM orders GROUP BY customer_id ORDER BY product;

C. SELECT customer_id, ARRAY_AGG(DISTINCT product ORDER BY product) FROM orders GROUP BY customer_id;

D. SELECT customer_id, ARRAY_AGG(product) FROM orders ORDER BY customer_id;

Solution

1. Step 1: Use ARRAY_AGG with DISTINCT and ORDER BY

   To get unique products sorted, use ARRAY_AGG(DISTINCT product ORDER BY product).

2. Step 2: Group by customer_id to aggregate per customer

   GROUP BY customer_id ensures aggregation per customer; ORDER BY inside ARRAY_AGG sorts products alphabetically.

3. Final Answer:

   SELECT customer_id, ARRAY_AGG(DISTINCT product ORDER BY product) FROM orders GROUP BY customer_id; -> Option C

4. Quick Check:

   Use DISTINCT inside ARRAY_AGG with ORDER BY for unique sorted arrays [OK]

Hint: Use DISTINCT inside ARRAY_AGG with ORDER BY for unique sorted arrays [OK]

Common Mistakes:

• Omitting DISTINCT causes duplicates in array
• Placing ORDER BY outside ARRAY_AGG affects rows, not array elements
• Not grouping by customer_id