PostgreSQL - Array Functions

PostgreSQL - Modern Array Functions

PostgreSQL features a powerful array data type, allowing you to store multiple values in a single table cell. A rich set of functions is available to manipulate these arrays. One of the most common tasks is to aggregate values from multiple rows into a single array, which is accomplished using the ARRAY_AGG function.

The ARRAY_AGG Function

The ARRAY_AGG(expression) function aggregates values from multiple rows into a new array. It is an aggregate function, so it’s typically used with a GROUP BY clause to create an array for each group.

Let’s consider a more practical example. Imagine a products table and a tags table that links products to various tags.

-- Sample schema for products and their tags
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50)
);

CREATE TABLE tags (
    tag_id INT PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    tag_name VARCHAR(50) NOT NULL
);

-- Sample data
INSERT INTO products (product_id, product_name, category) VALUES
(1, 'Laptop Pro', 'Electronics'),
(2, 'Organic Coffee', 'Groceries'),
(3, 'Wireless Mouse', 'Electronics');

INSERT INTO tags (tag_id, product_id, tag_name) VALUES
(101, 1, 'powerful'),
(102, 1, '16GB RAM'),
(103, 1, 'SSD'),
(104, 2, 'fair-trade'),
(105, 2, 'arabica'),
(106, 3, 'bluetooth'),
(107, 3, 'ergonomic');

Now, let’s aggregate all tags for each product into a single array.

SELECT
    p.product_name,
    ARRAY_AGG(t.tag_name)
FROM products p
JOIN tags t ON p.product_id = t.product_id
GROUP BY p.product_name;

The above query will produce the following result:

   product_name   |              array_agg
------------------+--------------------------------------
 Laptop Pro       | {powerful,"16GB RAM",SSD}
 Organic Coffee   | {fair-trade,arabica}
 Wireless Mouse   | {bluetooth,ergonomic}
(3 rows)

Advanced Usage: Ordering within Aggregation

A key feature of ARRAY_AGG is the ability to specify the order of elements within the resulting array using an ORDER BY clause inside the function call. This is extremely useful for predictable and user-friendly results.

-- Aggregate tags, ordered alphabetically
SELECT
    p.product_name,
    ARRAY_AGG(t.tag_name ORDER BY t.tag_name ASC)
FROM products p
JOIN tags t ON p.product_id = t.product_id
GROUP BY p.product_name;

This will produce a sorted array for each product:

   product_name   |              array_agg
------------------+--------------------------------------
 Laptop Pro       | {"16GB RAM",SSD,powerful}
 Organic Coffee   | {arabica,fair-trade}
 Wireless Mouse   | {bluetooth,ergonomic}
(3 rows)

Other Useful Array Functions

Once you have an array, PostgreSQL provides many other functions to work with it:

• UNNEST(array): Expands an array into a set of rows. This is the inverse of ARRAY_AGG.
• CARDINALITY(array): Returns the number of elements in an array.
• array_append(array, element): Appends an element to the end of an array.
• array_prepend(element, array): Prepends an element to the beginning of an array.
• array_cat(array1, array2): Concatenates two arrays.

Practical Application

Array aggregation is frequently used in APIs where you need to return a nested data structure. Instead of making multiple database calls (one for the parent object, and N for its children), you can use a JOIN with ARRAY_AGG to fetch all the data in a single, efficient query. This is a common pattern for building REST or GraphQL APIs.

postgresql_useful_functions.htm