直接用LATERAL UNNEST()展开数组是PostgreSQL 16中最可靠高效的方式；因UNNEST返回多行，不可用于标量子查询（否则报“more than one row”错误），必须置于FROM子句中配合JOIN LATERAL或LEFT JOIN LATERAL使用，并确保外层表与子查询均带别名。
直接用 LATERAL UNNEST() 展开数组是 PostgreSQL 16 中最可靠、最高效的方式；其他写法要么报错，要么语义错误或性能极差。
为什么不能在子查询 SELECT 列表里直接用 UNNEST
因为 UNNEST() 返回多行结果，而标量子查询（即括号包着的 (SELECT ...)）只允许返回零个或一个值。一执行就报错：ERROR: more than one row returned by a subquery used as an expression。
- ❌ 错误写法：
SELECT id, (SELECT UNNEST(tags)) FROM users; - ✅ 正确思路：把
UNNEST放进FROM子句，让它参与行集生成，而不是当“单个值”用 - ⚠️ 注意：
UNNEST(ARRAY[]::text[])对空数组返回零行，不是NULL—— 这会影响JOIN行为，比如LEFT JOIN LATERAL才能保留原行
LATERAL UNNEST 的标准写法和常见错误
LATERAL 的核心作用是允许右侧子查询引用左侧表的列，这是展开数组并保持上下文关联的关键。
- ✅ 必须带
JOIN LATERAL或LEFT JOIN LATERAL，不能只写LATERAL (SELECT ...) - ✅ 外层表必须有别名（如
u），子查询中引用字段要带该别名：UNNEST(u.tags) - ✅ 子查询本身也要有别名（如
tag），否则后续SELECT或GROUP BY无法引用 - ❌ 常见语法错误：
FROM users, LATERAL UNNEST(tags) AS t—— 缺少JOIN关键字，PostgreSQL 会报invalid reference to FROM-clause entry
正确示例（查每个 tag 出现次数）：
SELECT tag, COUNT(*) FROM users u JOIN LATERAL UNNEST(u.tags) AS tag ON true GROUP BY tag;
需要保留空数组行？LEFT JOIN LATERAL + ON true 是唯一解
如果某条记录的 tags 是空数组或 NULL，用普通 JOIN LATERAL 会导致该行被丢弃 —— 因为 UNNEST 不产生任何行，自然无法匹配。
- ✅ 保留所有用户（含无标签者）：
LEFT JOIN LATERAL UNNEST(u.tags) AS tag ON true - ⚠️
ON true是强制语法要求，漏写会报错；它不表示逻辑条件，只是满足 JOIN 语法结构 - ? 此时若需统计“有标签的用户数”，得用
COUNT(tag)（跳过 NULL），而非COUNT(*)
例如，列出所有用户及其第一个标签（空数组显示为 NULL）：
SELECT u.username, t.tag AS first_tag FROM users u LEFT JOIN LATERAL ( SELECT UNNEST(u.tags) LIMIT 1 ) t ON true;
多个数组字段同时展开？小心笛卡尔积
如果一张表有 tags text[] 和 roles text[]，想分别展开，不能简单写两个 LATERAL 并列 —— 它们会交叉组合，产生 |tags| × |roles| 行。
- ✅ 想“各自展开、不交叉”：用两个独立查询
UNION ALL，或改用jsonb_array_elements()+jsonb_build_object合并成统一结构 - ✅ 想“按顺序对齐展开”（如 tags[i] 对应 roles[i]）：先用
generate_subscripts()获取下标，再用下标取值：tags[i]和roles[i] - ⚠️
LATERAL展开本质是集合扩展，不是向量化操作；对齐需求本质上已超出纯 SQL 数组语义，建议前置处理或用应用层合并
真正容易被忽略的一点：LATERAL 子查询每次执行都重新计算，它不缓存中间结果。如果你在同一个查询里多次引用同一个 LATERAL UNNEST 结果，数据库会重复展开 —— 这种场景更适合用 CTE 提前物化（加 MATERIALIZED 提示可避免重复计算）。
