PostgreSQL - 数组函数

PostgreSQL - 现代数组函数

PostgreSQL 提供强大的数组数据类型，允许您在单个表格单元格中存储多个值。它还提供了一整套丰富的函数来操作这些数组。其中最常见的任务是将多行中的值聚合到一个数组中，这通过使用 ARRAY_AGG 函数来实现。

ARRAY_AGG 函数

ARRAY_AGG(expression) 函数将多行中的值聚合到一个新数组中。它是一个聚合函数（aggregate function），因此通常与 GROUP BY 子句一起使用，为每个分组创建一个数组。

让我们考虑一个更实际的例子。假设有一个 products 表和一个 tags 表，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
);

-- 示例数据
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');

现在，让我们将每个产品的所有标签聚合到一个数组中。

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;

上述查询将产生以下结果：

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

高级用法：聚合内的排序

ARRAY_AGG 的一个关键特性是，它允许您在函数调用内部使用 ORDER BY 子句来指定结果数组中元素的顺序。这对于获得可预测且用户友好的结果非常有用。

-- 聚合标签，按字母顺序排序
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;

这将为每个产品生成一个排序后的数组：

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

其他有用的数组函数

一旦您拥有一个数组，PostgreSQL 还提供了许多其他函数来处理它：

UNNEST(array): 将数组展开为一组行。它是 ARRAY_AGG 的逆操作。
CARDINALITY(array): 返回数组中的元素数量。
array_append(array, element): 将元素追加到数组的末尾。
array_prepend(element, array): 将元素前置到数组的开头。
array_cat(array1, array2): 连接两个数组。

实际应用

数组聚合（Array aggregation）常用于 API 中，当您需要返回嵌套数据结构时。您无需进行多次数据库调用（一次用于父对象，N 次用于其子对象），而是可以使用 JOIN 和 ARRAY_AGG 在单个高效查询中获取所有数据。这是构建 REST 或 GraphQL API 的常见模式。

postgresql_useful_functions.htm