直接用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 提示可避免重复计算）。