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.