> This page location: Postgres guides > Functions > Aggregate functions > Full Neon documentation index: https://neon.com/docs/llms.txt > IMPORTANT: If this page contains inaccurate or outdated information, report it: POST to https://neon.com/api/docs-feedback with {"feedback": "describe the issue", "path": "/docs/functions/array_agg"} # Postgres array_agg() function Aggregate values into an array The Postges `array_agg()` function collects values from multiple rows into a single array. It's particularly useful for denormalizing data, creating comma-separated lists, or preparing data for JSON output. For example, you can use it to list all products in a category from a products catalog table or all orders for a customer from an orders table. ## Function signature The `array_agg()` function has this simple form: ```sql array_agg(expression) -> anyarray ``` - `expression`: The value to be aggregated into an array. This can be a column or expression of any data type. ```sql array_agg(expression ORDER BY sort_expression [ASC | DESC] [NULLS { FIRST | LAST }]) -> anyarray ``` - `expression`: The value to be aggregated into an array. - `ORDER BY`: Specifies the order in which the values should be aggregated. - `sort_expression`: The expression to sort by. - `ASC | DESC`: Specifies ascending or descending order (default is ASC). - `NULLS { FIRST | LAST }`: Specifies whether nulls should be first or last in the ordering (default depends on ASC or DESC). ## Example usage Consider an `orders` table with columns `order_id`, `product_id`, and `quantity`. You can use `array_agg()` to list all the product IDs for each order. ```sql WITH orders AS ( SELECT 1 AS order_id, 101 AS product_id, 2 AS quantity UNION ALL SELECT 1, 102, 1 UNION ALL SELECT 2, 103, 3 UNION ALL SELECT 2, 104, 1 UNION ALL SELECT 3, 101, 1 ) SELECT order_id, array_agg(product_id) AS products FROM orders GROUP BY order_id ORDER BY order_id; ``` This query groups the orders by `order_id` and aggregates the `product_id` values into an array for each order. ```text order_id | products ----------+----------- 1 | {101,102} 2 | {103,104} 3 | {101} (3 rows) ``` ## Advanced examples ### Ordered array aggregation You can specify an order for the elements in the resulting array: ```sql WITH employees AS ( SELECT 1 AS emp_id, 'John' AS name, 'SQL' AS skill UNION ALL SELECT 1, 'John', 'Python' UNION ALL SELECT 1, 'John', 'Java' UNION ALL SELECT 2, 'Jane', 'C++' UNION ALL SELECT 2, 'Jane', 'Ruby' ) SELECT emp_id, name, array_agg(skill ORDER BY skill) AS skills FROM employees GROUP BY emp_id, name ORDER BY emp_id; ``` This query aggregates the listed skills for each employee into an alphabetically ordered array. ```text emp_id | name | skills --------+------+------------------- 1 | John | {Java,Python,SQL} 2 | Jane | {C++,Ruby} (2 rows) ``` ### Combining with other aggregate functions `array_agg()` can be used in combination with other aggregate functions: ```sql WITH sales(category, product, price, sale_date) AS ( VALUES ('Electronics', 'Laptop', 1200, '2023-01-15'::date), ('Electronics', 'Smartphone', 800, '2023-01-20'::date), ('Electronics', 'Tablet', 500, '2023-02-10'::date), ('Books', 'Novel', 20, '2023-02-05'::date), ('Books', 'Textbook', 100, '2023-02-15'::date), ('Books', 'Cookbook', 30, '2023-03-01'::date) ) SELECT category, array_agg( (SELECT product || ': ' || SUM(price)::text FROM sales s2 WHERE s2.category = s1.category AND s2.product = s1.product GROUP BY s2.product) ) AS product_sales FROM sales s1 GROUP BY category; ``` This query aggregates products into an array with their total sales, for each category. ```text category | product_sales -------------+-------------------------------------------------- Electronics | {"Laptop: 1200","Smartphone: 800","Tablet: 500"} Books | {"Novel: 20","Textbook: 100","Cookbook: 30"} (2 rows) ``` ### Using array_agg() with DISTINCT You can use `DISTINCT` with `array_agg()` to remove duplicates from the output array: ```sql WITH user_logins AS ( SELECT 1 AS user_id, 'Chrome' AS browser UNION ALL SELECT 1, 'Firefox' UNION ALL SELECT 1, 'Chrome' UNION ALL SELECT 2, 'Safari' UNION ALL SELECT 2, 'Chrome' ) SELECT user_id, array_agg(DISTINCT browser ORDER BY browser) AS browsers_used FROM user_logins GROUP BY user_id; ``` This query creates an array of the browsers used by each user, without duplicates and in alphabetical order. ```text user_id | browsers_used ---------+------------------ 1 | {Chrome,Firefox} 2 | {Chrome,Safari} (2 rows) ``` ## Additional considerations ### Performance implications While `array_agg()` is powerful, it can be memory-intensive for large datasets. The function needs to hold all the aggregated values in memory before creating the final array. For very large result sets, consider using pagination or limiting the number of rows before aggregating. ### NULL handling By default, `array_agg()` includes NULL values in the resulting array. If you want to exclude NULL values, you can use it in combination with `FILTER`: ```sql SELECT array_agg(column_name) FILTER (WHERE column_name IS NOT NULL) FROM table_name; ``` ### Alternative functions - `string_agg()`: Concatenates string values into a single string, separated by a delimiter. - `json_agg()`: Aggregates values into a JSON array. ## Resources - [PostgreSQL documentation: Aggregate Functions](https://www.postgresql.org/docs/current/functions-aggregate.html) - [PostgreSQL documentation: Array Functions and Operators](https://www.postgresql.org/docs/current/functions-array.html) --- ## Related docs (Functions) - [Array functions](https://neon.com/docs/functions/array_length) - [Date / Time functions](https://neon.com/docs/functions/age) - [JSON functions](https://neon.com/docs/functions/array_to_json) - [Window functions](https://neon.com/docs/functions/dense_rank) - [String functions](https://neon.com/docs/functions/concat) - [Math functions](https://neon.com/docs/functions/math-abs)