Array aggregation with ARRAY_AGG in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ARRAY_AGG function do in PostgreSQL?

ARRAY_AGG collects values from multiple rows into a single array.

beginner

How do you use ARRAY_AGG to get all employee names in one array from a table named employees?

Use: SELECT ARRAY_AGG(name) FROM employees;

intermediate

Can ARRAY_AGG be combined with GROUP BY? If yes, why?

Yes, to collect values into arrays grouped by a column, like departments.

intermediate

What happens if you use ARRAY_AGG on a column with NULL values?

NULL values are included in the resulting array unless filtered out.

advanced

How can you order elements inside the array created by ARRAY_AGG?

Use ORDER BY inside ARRAY_AGG, like ARRAY_AGG(name ORDER BY name ASC).

What is the main purpose of ARRAY_AGG in PostgreSQL?

A To sort rows alphabetically

B To count rows in a table

C To delete duplicate rows

D To combine multiple row values into an array

ARRAY_AGG collects values from multiple rows into a single array.

Which SQL clause is commonly used with ARRAY_AGG to group results?

A WHERE

B GROUP BY

C ORDER BY

D HAVING

GROUP BY groups rows so ARRAY_AGG can collect values per group.

How do you exclude NULL values from the array created by ARRAY_AGG?

A Use WHERE column IS NOT NULL before aggregation

B Use ARRAY_AGG with NULLS EXCLUDED option

C Use DISTINCT inside ARRAY_AGG

D NULLs are always excluded by default

Filtering NULLs before aggregation excludes them from the array.

How can you sort the elements inside the array returned by ARRAY_AGG?

A Use ORDER BY outside the query

B Sort after query in application code only

C Use ORDER BY inside ARRAY_AGG function

D ARRAY_AGG does not support sorting

You can specify ORDER BY inside ARRAY_AGG to sort elements.

What type of data does ARRAY_AGG return?

A An array of values

B A JSON object

C A single scalar value

D A table

ARRAY_AGG returns an array containing aggregated values.

Explain how ARRAY_AGG works and give an example query using it.

Think about gathering many values into one list.

Describe how to handle NULL values and ordering when using ARRAY_AGG.

Consider filtering and sorting inside the aggregation.

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