数组函数

操作符能表达判断与筛选，但真正"改造"数组还要靠函数。本节按功能把 PostgreSQL 的数组函数分成查询信息、查找定位、增删替换、转换展开、聚合构造五组，逐组给出语法与可运行示例。

前置知识

阅读本节前，建议先了解：数组操作符

基础概念

数组函数（Array Function）

PostgreSQL 的数组函数数量不少，但按用途归组后逻辑非常清晰。

功能组	函数	用途
形状信息	`array_length` `cardinality` `array_dims` `array_ndims` `array_lower` `array_upper`	获知数组长度与维度
查找定位	`array_position` `array_positions`	找元素下标
增删替换	`array_append` `array_prepend` `array_cat` `array_remove` `array_replace`	修改内容
转换展开	`unnest` `string_to_array` `array_to_string`	数组 ↔ 行 / 数组 ↔ 文本
聚合构造	`array_agg` `array()` 子查询	行 → 数组

一维与多维的差异

有一部分函数只针对"扁平的一维数组"设计。最典型的是 array_append / array_prepend / array_cat / array_remove / array_replace：它们不支持多维数组，遇到二维数组会报错。

sql

-- 多维数组不能使用 array_append
SELECT array_append(ARRAY[[1,2],[3,4]], 5);
-- ERROR:  cannot append to multidimensional array

而 array_length / cardinality / array_dims / array_ndims / unnest 都支持多维。

下界（Lower Bound）与 `array_lower`

PostgreSQL 数组的下标默认从 1 开始，但下界是可以不为 1 的（切片会保留原下界）。array_lower / array_upper 用于获取真实的上下界，比 array_length 更精确。

sql

SELECT array_lower(ARRAY[1,2,3], 1) AS lb,   -- 1
       array_upper(ARRAY[1,2,3], 1) AS ub;   -- 3

-- 切片后下界可能不是 1
SELECT array_lower((ARRAY[10,20,30,40])[2:4], 1) AS lb,   -- 2
       array_upper((ARRAY[10,20,30,40])[2:4], 1) AS ub;   -- 4

术语提示

数组函数（Array Function）、展开（Unnest）、聚合（Aggregate）、下界（Lower Bound）、上界（Upper Bound）。本节反复使用这些术语。

语法与用法

常用函数的语法骨架：

text

-- 形状信息
array_length(arr, dim)          -- 指定维度的长度，为空返回 NULL
cardinality(arr)                -- 元素总数（所有维度相乘）
array_dims(arr)                 -- 形如 '[1:3]' 或 '[1:2][1:2]'
array_ndims(arr)                -- 维度数
array_lower(arr, dim)           -- 指定维度下界
array_upper(arr, dim)           -- 指定维度上界

-- 查找定位
array_position(arr, elem)              -- 首个匹配下标，找不到返回 NULL
array_position(arr, elem, start)       -- 从 start 开始找
array_positions(arr, elem)             -- 所有匹配下标组成的数组
array_replace(arr, from, to)           -- 替换（支持多维）

-- 增删
array_append(arr, elem)         -- 尾部追加（一维）
array_prepend(elem, arr)        -- 头部插入（一维，注意参数顺序）
array_cat(arr1, arr2)           -- 拼接（一维）
array_remove(arr, elem)         -- 删除所有匹配元素（一维）

-- 转换
unnest(arr)                            -- 展开为行
unnest(arr) WITH ORDINALITY            -- 展开并附带序号
string_to_array(str, delim)            -- 文本转数组
array_to_string(arr, delim)            -- 数组转文本

准备示例数据：

sql

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   TEXT     NOT NULL,
  tags   TEXT[]   NOT NULL DEFAULT '{}',     -- 标签
  prices NUMERIC(10,2)[] NOT NULL DEFAULT '{}' -- 各渠道价格
);

INSERT INTO products (name, tags, prices) VALUES
  ('机械键盘', ARRAY['electronics','keyboard','hot'], ARRAY[399.00, 429.00, 459.00]),
  ('人体工学椅', ARRAY['furniture','hot'],            ARRAY[1299.00, 1499.00]),
  ('降噪耳机',  ARRAY['electronics','audio','hot'],   ARRAY[899.00, 999.00, 1099.00, 1199.00]),
  ('显示器支架', ARRAY['furniture','stand'],          ARRAY[199.00]);

详细说明

形状信息函数

array_length 是最常用的长度函数，但它有两个陷阱：必须指定维度，且空数组返回 NULL 而不是 0。

sql

SELECT array_length(ARRAY[1,2,3], 1) AS len;      -- 3
SELECT array_length('{}'::INT[], 1)  AS empty_len; -- NULL（不是 0！）

cardinality 是更安全的替代：它返回元素总数，空数组返回 0。

sql

SELECT cardinality(ARRAY[1,2,3]) AS c1;        -- 3
SELECT cardinality('{}'::INT[]) AS c2;         -- 0
SELECT cardinality(ARRAY[[1,2],[3,4]]) AS c3;  -- 4（2×2）

array_dims 返回可读的形状串，array_ndims 返回维度数：

sql

SELECT array_dims(ARRAY[1,2,3]) AS d1;              -- [1:3]
SELECT array_dims(ARRAY[[1,2],[3,4]]) AS d2;        -- [1:2][1:2]
SELECT array_ndims(ARRAY[[1,2],[3,4]]) AS n2;       -- 2
SELECT array_dims('{}'::INT[]) AS d3;               -- NULL

在多维数组上，array_length 需要维度参数：

sql

SELECT array_length(ARRAY[[1,2,3],[4,5,6]], 1) AS dim1,   -- 2
       array_length(ARRAY[[1,2,3],[4,5,6]], 2) AS dim2;   -- 3
SELECT array_length(ARRAY[[1,2,3],[4,5,6]], 3) AS dim3;   -- NULL（无第 3 维）

形状信息函数对照表：

函数	`'{}'::INT[]`	`ARRAY[1,2,3]`	`ARRAY[[1,2],[3,4]]`
`array_length(a,1)`	`NULL`	`3`	`2`
`array_length(a,2)`	`NULL`	`NULL`	`2`
`cardinality(a)`	`0`	`3`	`4`
`array_dims(a)`	`NULL`	`[1:3]`	`[1:2][1:2]`
`array_ndims(a)`	`NULL`	`1`	`2`
`array_lower(a,1)`	`NULL`	`1`	`1`
`array_upper(a,1)`	`NULL`	`3`	`2`

查找定位函数

array_position 返回第一个匹配元素的下标，找不到返回 NULL（不是 0，也不会报错）。

sql

SELECT array_position(ARRAY['a','b','c'], 'b') AS pos1;   -- 2
SELECT array_position(ARRAY['a','b','c'], 'z') AS pos2;   -- NULL
SELECT array_position(ARRAY[1,2,3,2,1], 2, 3)  AS pos3;   -- 4（从第 3 位开始找）

array_positions 返回所有匹配下标的数组：

sql

SELECT array_positions(ARRAY[1,2,3,2,1], 2) AS positions;   -- {2,4}
SELECT array_positions(ARRAY['a','b'], 'z')  AS none;        -- {}

在业务中，array_position 常用于"判断元素是否在数组中，并拿到位置以便同步修改另一个数组"：

sql

-- 找出含 'hot' 标签且 'hot' 所在位置
SELECT name,
       array_position(tags, 'hot') AS hot_pos,
       CASE WHEN array_position(tags, 'hot') IS NULL THEN '普通' ELSE '热销' END AS level
FROM products
ORDER BY id;

-- 结果：
--     name    | hot_pos | level
-- ------------+---------+-------
--  机械键盘    |       3 | 热销
--  人体工学椅  |       2 | 热销
--  降噪耳机    |       3 | 热销
--  显示器支架  |         | 普通

增删替换函数

array_append 与 array_prepend 的参数顺序相反，这是最常见的笔误来源：

sql

SELECT array_append(ARRAY[1,2], 3)   AS app;   -- {1,2,3}
SELECT array_prepend(0, ARRAY[1,2])  AS pre;   -- {0,1,2}
-- 错误写法：array_prepend(ARRAY[1,2], 0)
-- ERROR:  function array_prepend(integer[], integer) does not exist

array_cat 与操作符 || 等价，但语义更明确、不需要类型推断：

sql

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS cat;   -- {1,2,3,4}
SELECT array_cat(ARRAY[1,2], '{}'::INT[]) AS cat2; -- {1,2}

array_remove 删除所有等于目标值的元素；NULL 无法被删除：

sql

SELECT array_remove(ARRAY[1,2,3,2,1], 2) AS rm;      -- {1,3,1}
SELECT array_remove(ARRAY[1,2,3], 9)     AS rm_none; -- {1,2,3}
SELECT array_remove(ARRAY[1,NULL,3], NULL) AS rm_null; -- {1,NULL,3}（未删除）

array_replace 把所有匹配值替换成新值，且支持多维数组：

sql

SELECT array_replace(ARRAY[1,2,3,2], 2, 20) AS rep;        -- {1,20,3,20}
SELECT array_replace(ARRAY[[1,2],[2,3]], 2, 0) AS rep2;    -- {{1,0},{0,3}}

增删替换函数的能力边界表：

函数	支持多维	处理 NULL	会改长度
`array_append`	否	可追加 NULL	是
`array_prepend`	否	可插入 NULL	是
`array_cat`	否	保留	是
`array_remove`	否	不能删除 NULL	是
`array_replace`	是	可替换 NULL 为新值	否

转换与展开函数

string_to_array 与 array_to_string 是数组与文本之间的桥梁，处理 CSV、标签串等场景非常常用。

sql

-- 文本 → 数组
SELECT string_to_array('a,b,c', ',') AS a1;              -- {a,b,c}
SELECT string_to_array('a,b,,c', ',') AS a2;             -- {a,b,NULL,c}
SELECT string_to_array('a,b,c', ',', 'b') AS a3;         -- {a,NULL,c}（把 'b' 当 NULL）
SELECT string_to_array('a b  c', ' ') AS a4;             -- {a,b,"",c}

-- 数组 → 文本
SELECT array_to_string(ARRAY['a','b','c'], ',') AS s1;   -- a,b,c
SELECT array_to_string(ARRAY['a',NULL,'c'], ',') AS s2;  -- a,c（NULL 被跳过）
SELECT array_to_string(ARRAY['a',NULL,'c'], ',', 'N/A') AS s3; -- a,N/A,c

注意 array_to_string 默认跳过 NULL，这在拼接展示时很实用，但在需要保持位置对应时反而是坑。

unnest 把数组展开成一张"虚拟表"，是数组与关系模型之间的核心桥梁。

sql

-- 基本展开：每行一个元素
SELECT unnest(ARRAY['a','b','c']) AS elem;
-- 结果：
--  elem
-- ------
--  a
--  b
--  c

-- 带序号（PG 9.4+）
SELECT * FROM unnest(ARRAY['a','b','c']) WITH ORDINALITY AS t(elem, ord);
-- 结果：
--  elem | ord
-- ------+-----
--  a    |   1
--  b    |   2
--  c    |   3

-- 与表列配合展开
SELECT id, name, unnest(tags) AS tag
FROM products
ORDER BY id, tag;

unnest 还可以在 FROM 中并行展开多个数组，按位置配对：

sql

SELECT name, tag, price
FROM products
CROSS JOIN LATERAL unnest(products.tags, products.prices) AS t(tag, price)
ORDER BY name, price;

-- 结果：
--     name    |    tag     |  price
-- ------------+------------+---------
--  人体工学椅  | furniture  | 1299.00
--  人体工学椅  | hot        | 1499.00
--  机械键盘    | electronics |  399.00
--  机械键盘    | keyboard   |  429.00
--  机械键盘    | hot        |  459.00
--  显示器支架  | furniture  |  199.00
--  降噪耳机    | electronics |  899.00
--  ...

聚合函数 `array_agg`

array_agg 把多行聚合成一个数组，是 unnest 的逆向操作。

sql

-- 行 → 数组：把每篇文章的标签聚合成一个数组
SELECT array_agg(tag) AS all_tags
FROM (SELECT DISTINCT unnest(tags) AS tag FROM products) s;

-- 结果：
--                 all_tags
-- -----------------------------------------
--  {electronics,keyboard,hot,furniture,audio,stand}

重要：array_agg 必须显式 ORDER BY，否则顺序不确定。

sql

-- 顺序不确定（不推荐）
SELECT array_agg(name) FROM products;

-- 顺序确定（推荐）
SELECT array_agg(name ORDER BY id DESC) FROM products;

-- 结果：
--                    array_agg
-- -----------------------------------------------
--  {显示器支架,降噪耳机,人体工学椅,机械键盘}

配合 GROUP BY 做分组聚合：

sql

-- 每个标签下有哪些商品
SELECT tag, array_agg(name ORDER BY name) AS product_names
FROM products, unnest(tags) AS tag
GROUP BY tag
ORDER BY tag;

-- 结果：
--     tag     |           product_names
-- ------------+-----------------------------------
--  audio      | {降噪耳机}
--  electronics| {机械键盘,降噪耳机}
--  furniture  | {人体工学椅,显示器支架}
--  hot        | {人体工学椅,机械键盘,降噪耳机}
--  keyboard   | {机械键盘}
--  stand      | {显示器支架}

array() 构造器加子查询可以实现"子查询结果直接变成数组"：

sql

SELECT ARRAY(SELECT name FROM products ORDER BY id) AS names;
-- 结果：{机械键盘,人体工学椅,降噪耳机,显示器支架}

`unnest` 与聚合互转

两者互为逆操作，掌握这个双向映射在很多场景能大幅简化 SQL。

sql

-- 展开 → 处理后 → 重新聚合（典型模式）
SELECT array_agg(tag ORDER BY tag) AS sorted_tags
FROM unnest(ARRAY['c','a','b']) AS tag;

-- 结果：{a,b,c}

用这个模式可以做"数组内元素去重排序"：

sql

-- 合并多个数组并去重排序
SELECT array_agg(DISTINCT tag ORDER BY tag) AS merged
FROM unnest(ARRAY['b','a','c'] || ARRAY['c','d']) AS tag;

-- 结果：{a,b,c,d}

也可以做"过滤数组元素"：

sql

-- 只保留长度大于 6 的标签
SELECT array_agg(tag ORDER BY tag) AS long_tags
FROM unnest(ARRAY['hot','furniture','electronics','audio']) AS tag
WHERE length(tag) > 6;

-- 结果：{furniture,electronics}

实战示例

用"商品标签与价格分析"串起本节全部函数。

第 1 步：标签统计概览

sql

-- 每个商品的标签数、标签总字符数
SELECT name,
       cardinality(tags) AS tag_count,
       array_to_string(tags, '/') AS tag_text,
       array_length(prices, 1) AS price_count
FROM products
ORDER BY id;

-- 结果：
--     name    | tag_count |      tag_text       | price_count
-- ------------+-----------+---------------------+-------------
--  机械键盘    |         3 | electronics/keyboard/hot | 3
--  人体工学椅  |         2 | furniture/hot       | 2
--  降噪耳机    |         3 | electronics/audio/hot | 4
--  显示器支架  |         2 | furniture/stand     | 2

第 2 步：把价格数组转成行做统计

sql

-- 每个商品的最低、最高、平均价格
SELECT p.name,
       min(v.price) AS min_price,
       max(v.price) AS max_price,
       round(avg(v.price), 2) AS avg_price
FROM products p
CROSS JOIN LATERAL unnest(p.prices) AS v(price)
GROUP BY p.id, p.name
ORDER BY p.name;

-- 结果：
--     name    | min_price | max_price | avg_price
-- ------------+-----------+-----------+-----------
--  人体工学椅  |   1299.00 |   1499.00 |   1399.00
--  机械键盘    |    399.00 |    459.00 |    429.00
--  显示器支架  |    199.00 |    199.00 |    199.00
--  降噪耳机    |    899.00 |   1199.00 |   1049.00

第 3 步：查找特定元素位置并修改

sql

-- 把 'hot' 标签替换成 'sales'
UPDATE products
SET tags = array_replace(tags, 'hot', 'sales')
WHERE 'hot' = ANY (tags)
RETURNING name, tags;

-- 结果：
--     name    |            tags
-- ------------+-----------------------------
--  机械键盘    | {electronics,keyboard,sales}
--  人体工学椅  | {furniture,sales}
--  降噪耳机    | {electronics,audio,sales}

第 4 步：删除不需要的标签

sql

-- 从所有商品中移除 'sales' 标签
UPDATE products
SET tags = array_remove(tags, 'sales')
WHERE tags @> ARRAY['sales']
RETURNING name, tags;

-- 结果：
--     name    |        tags
-- ------------+---------------------
--  机械键盘    | {electronics,keyboard}
--  人体工学椅  | {furniture}
--  降噪耳机    | {electronics,audio}

第 5 步：按标签分组聚合

sql

-- 每个标签对应哪些商品，按商品名排序
SELECT tag,
       count(*) AS product_cnt,
       array_agg(name ORDER BY name) AS names
FROM products, unnest(tags) AS tag
GROUP BY tag
ORDER BY product_cnt DESC, tag;

-- 结果：
--     tag      | product_cnt |        names
-- -------------+-------------+---------------------
--  electronics |           2 | {机械键盘,降噪耳机}
--  audio       |           1 | {降噪耳机}
--  furniture   |           1 | {人体工学椅}
--  keyboard    |           1 | {机械键盘}

第 6 步：标签串与数组互转

sql

-- 把 'a,b,c' 形式的标签串导入数组
INSERT INTO products (name, tags)
VALUES ('桌面音箱', string_to_array('electronics,audio,new', ','))
RETURNING name, tags, cardinality(tags) AS cnt;

-- 结果：
--    name   |            tags            | cnt
-- ----------+----------------------------+-----
--  桌面音箱  | {electronics,audio,new}   |   3

第 7 步：清理

sql

DROP TABLE products;

注意事项

array_length 对空数组返回 NULL：做加法或比较前要 COALESCE(array_length(a,1), 0)，或直接用 cardinality(a)。
array_length 必须传维度参数：array_length(ARRAY[1,2,3]) 会报 ERROR: function array_length(integer[]) does not exist。
array_append / array_prepend / array_cat / array_remove 不支持多维数组：只适用于一维。
array_prepend 参数顺序与 array_append 相反：array_prepend(elem, arr)，写反会报函数不存在。
array_remove 无法删除 NULL：PostgreSQL 的语义限制，需要改用 array_agg + unnest 过滤。
array_position 找不到返回 NULL：不能用 <> 0 判断，要用 IS NULL。
array_to_string 默认跳过 NULL：会导致位置信息丢失，需要保留时传第三个参数作为替代文本。
array_agg 不保证顺序：必须显式 ORDER BY，否则结果可能随执行计划变化。
unnest 多数组按位置配对：数组长度不一致时短的那个用 NULL 补齐。
array_replace 的第三参数不能是 NULL：想删除请用 array_remove。
多维数组元素类型一致但形状必须矩形：ARRAY[[1,2],[3]] 会报错。

最佳实践

长度判断统一用 cardinality：避免 array_length 的 NULL 陷阱，代码更简洁。
array_agg 一律显式排序：array_agg(x ORDER BY y)，保证结果可复现。
数组 → 行用 unnest，行 → 数组用 array_agg：两者互为逆操作，是数组与关系模型互转的标准手段。
需要序号时用 WITH ORDINALITY：比自己维护计数器更简洁、更不易错。
构造数组优先用 ARRAY(SELECT ...)：比 array_agg 少一层 NULL 合并逻辑，语义更直观。
文本与数组互转用 string_to_array / array_to_string：不要自己写 split 逻辑。
过滤数组元素用 unnest + array_agg 的组合：这是唯一能删除 NULL 元素的可靠方式。
给高频筛选的数组列加 GIN 索引：函数本身不走索引，但配套的 @> / && 查询可以。

继续学习：多维数组

参考链接

PostgreSQL 官方文档：Array Functions and Operators
PostgreSQL 官方文档：Arrays
PostgreSQL 官方文档：Aggregate Functions
PostgreSQL 官方文档：Row and Array Comparisons

数组函数 ​

基础概念 ​

数组函数（Array Function） ​

一维与多维的差异 ​

下界（Lower Bound）与 array_lower ​

语法与用法 ​

详细说明 ​

形状信息函数 ​

查找定位函数 ​

增删替换函数 ​

转换与展开函数 ​

聚合函数 array_agg ​

unnest 与聚合互转 ​

实战示例 ​

第 1 步：标签统计概览 ​

第 2 步：把价格数组转成行做统计 ​

第 3 步：查找特定元素位置并修改 ​

第 4 步：删除不需要的标签 ​

第 5 步：按标签分组聚合 ​

第 6 步：标签串与数组互转 ​

第 7 步：清理 ​

注意事项 ​

最佳实践 ​