怎样在PostgreSQL中用UNNEST函数将数组行转列展开-SQL-PHP中文网

unnest函数将数组展开为多行，但会完全丢弃null或空数组；必须用coalesce兜底、lateral关联原表、with ordinality保序号，且空数组需显式类型声明如array[]::text[]。

怎样在postgresql中用unnest函数将数组行转列展开

UNNEST函数的基本用法和必须注意的NULL行为

UNNEST 是 PostgreSQL 中将数组“炸开”成多行的核心函数，但它默认会把 NULL 数组整个丢弃——这点极易被忽略。比如你写 SELECT UNNEST(ARRAY[1,2,NULL,4])，结果是 4 行（1、2、NULL、4）；但如果你传的是 UNNEST(ARRAY[1,2] || NULL)，即整个数组为 NULL，那这行就完全不会出现在结果里。

实操建议：

如果源字段可能为 NULL，务必先用 COALESCE(col, ARRAY[]::text[]) 做兜底，避免整行丢失
多个数组同时展开时，UNNEST(a, b) 要求长度严格一致，否则报错 ERROR: cannot unnest a null array 或长度不匹配
想保留原行结构（比如带 id 的主表），必须搭配 LATERAL 使用，直接 SELECT *, UNNEST(arr) 会报错

用LATERAL JOIN实现安全的数组展开（带ID关联）

这是最常见也最容易出错的场景：你有一张用户表，其中 tags 是 text[] 类型，想把每个 tag 拆成一行，同时保留 user_id。直接 SELECT user_id, UNNEST(tags) 会失败，因为 UNNEST 不是标量函数，不能混在普通列中。

正确做法是用 LATERAL 把展开变成“每行驱动一次子查询”：

SELECT u.user_id, t.tag
FROM users u,
LATERAL UNNEST(u.tags) AS t(tag);

说明：

LATERAL 允许右边引用左边的列，UNNEST(u.tags) 就能逐行计算
AS t(tag) 是给展开结果起别名和列名，不写会默认叫 unnest，容易混淆
逗号写法是隐式 CROSS JOIN LATERAL，等价于显式写 CROSS JOIN LATERAL UNNEST(...)

UNNEST配合WITH ORDINALITY获取下标位置

有时你需要知道某个元素在原数组里的位置（比如第几个标签最重要），这时 WITH ORDINALITY 就不可少。它会给每一行加一个序号列，默认叫 ordinality，从 1 开始。

示例：

SELECT u.user_id, t.tag, o.idx
FROM users u,
LATERAL UNNEST(u.tags) WITH ORDINALITY AS t(tag, idx);

关键点：

WITH ORDINALITY 必须紧跟 UNNEST(...) 后面，不能写成 UNNEST(...) AS t(tag) WITH ORDINALITY
别名中的第二列（这里是 idx）就是序号，类型是 bigint，不是从 0 开始
如果数组为空（{}），WITH ORDINALITY 仍返回 0 行，不会产生 idx=0

性能和数据类型陷阱：大数组和跨类型展开

当数组元素超过几千个，或频繁调用 UNNEST，性能会明显下降——因为每次都是内存构造新行集。更隐蔽的问题是类型推导：PostgreSQL 对空数组 {} 默认无法推断类型，常导致 UNNEST(ARRAY[]) 报错 ERROR: cannot determine type of empty array。

规避方法：