Menu

PostgreSQL array_agg() Function

Updated on

The PostgreSQL array_agg() aggregate collects input values into an array. It keeps NULL inputs as NULL array elements. The order of the elements is not guaranteed unless you specify an order inside the aggregate call.

array_agg() Syntax

array_agg(value [ORDER BY sort_expression])
  • value: A value or expression to add to the array.
  • ORDER BY sort_expression: Optional. Controls the order of elements in the result array.

The result is an array of the input type. If the aggregate receives no rows, it returns SQL NULL, not an empty array.

array_agg() Example

This query collects each person’s hobbies. It preserves the NULL hobby and orders the elements by sort_order:

WITH hobbies(name, hobby, sort_order) AS (
    VALUES
        ('Tim', 'Football', 1),
        ('Tim', NULL::text, 2),
        ('Tim', 'Baseball', 3),
        ('Tom', 'Piano', 1),
        ('Tom', 'Violin', 2)
)
SELECT
    name,
    array_agg(hobby ORDER BY sort_order) AS hobbies
FROM hobbies
GROUP BY name
ORDER BY name;

 name |          hobbies
------+----------------------------
 Tim  | {Football,NULL,Baseball}
 Tom  | {Piano,Violin}
(2 rows)

The ORDER BY inside array_agg() orders elements within each array. The final ORDER BY name sorts the result rows. If you need an empty text array when there are no input rows, use COALESCE(array_agg(value), ARRAY[]::text[]) with a text expression.

Compare this function with string_agg(), which concatenates values into text, and json_agg(), which builds a JSON array.

For more details, see PostgreSQL’s official aggregate function documentation.