Explain ARRAY_AGG in SQL

ARRAY_AGG is an aggregate function in SQL that combines values from multiple rows into a single array. It's the inverse of a typical GROUP BY function like SUM or COUNT, which reduces multiple rows to a single value. Instead of getting a single number, you get an array of all the aggregated values.

How it Works

The ARRAY_AGG function is used within a GROUP BY clause. For each group, it collects all the values from the specified column and puts them into an array. . This is especially useful for tasks where you need to see a list of related items for each group.

SELECT
  grouping_column,
  ARRAY_AGG(column_to_aggregate)
FROM
  table_name
GROUP BY
  grouping_column;

Example

Imagine a table named orders with customer_id and product_name columns. You want to see a list of all products each customer has purchased.

SELECT
  customer_id,
  ARRAY_AGG(product_name) AS purchased_products
FROM
  orders
GROUP BY
  customer_id;

The result would look like this:

Get Kandaanusha’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

customer_id purchased_products

101 {“Laptop”, “Mouse”, “Keyboard”}

102 {“Phone”, “Charger”}103{“Headphones”}

Key Features and Considerations

• ORDER BY Clause: The order of elements in the resulting array is not guaranteed by default. You can use an ORDER BY clause inside the ARRAY_AGG function to specify the sort order of the elements.

SELECT customer_id, ARRAY_AGG(product_name ORDER BY product_name) AS ordered_products FROM orders GROUP BY customer_id;

• DISTINCT Keyword: You can use the DISTINCT keyword to remove duplicate values from the aggregated array.

SELECT customer_id, ARRAY_AGG(DISTINCT product_name) AS unique_products FROM orders GROUP BY customer_id;

• Platform Specificity: While ARRAY_AGG is a common aggregate function, its syntax and available options can vary slightly between different SQL databases like PostgreSQL, BigQuery, and Snowflake. Some systems may use similar functions like STRING_AGG or LISTAGG for string concatenation, which is a related concept but returns a single string instead of an array.