UNNEST不能在标量子查询中直接使用，因其返回多行而非单值；正确方式是用LATERAL UNNEST配合FROM子句；存在性判断应优先用@>或ANY操作符。
子查询里直接用 UNNEST 会报错，因为标量子查询只允许返回零或一个值，而 UNNEST 天然返回多行——这是最常卡住人的第一步。
为什么不能在 SELECT 或 WHERE 子查询中直接调用 UNNEST
PostgreSQL 要求标量子查询（即括号内独立的 SELECT）必须返回至多一行一列。但 UNNEST(arr) 是集合返回函数（SRF），哪怕数组只有一个元素，它也返回一行“结果集”，不是标量值。
- 错误写法：
SELECT id, (SELECT UNNEST(tags)) FROM products→ 报错more than one row returned by a subquery used as an expression - 即使数组为空（
{}）或为NULL，该子查询也不算“返回零行”，而是根本无法通过语法校验 - 这种限制与 PostgreSQL 16 的类型推断更严格无关，而是 SQL 标准层面的语义约束
LATERAL UNNEST 是唯一安全的展开路径
真正能用的场景是把 UNNEST 放进 FROM 子句，配合 LATERAL 让其能引用外层字段，并参与连接逻辑。
- 正确写法：
SELECT p.id, t.tag FROM products p, LATERAL UNNEST(p.tags) AS t(tag) -
LATERAL是关键：没有它，UNNEST(p.tags)中的p.tags会被视为未定义 - 若需保留原表中
tags为空或NULL的记录，必须改用LEFT JOIN LATERAL UNNEST(...) ON true，否则这些行会彻底消失 - 多个数组字段同时展开？可以写多个
LATERAL，但注意笛卡尔积风险——例如LATERAL UNNEST(a) AS x和LATERAL UNNEST(b) AS y会按长度做交叉配对
替代方案：用 @> 或 ANY 判断存在性，别硬展开
如果目标只是检查数组是否含某值（比如 “订单是否含高风险商品”），完全没必要走 UNNEST —— 操作符更高效、可走索引、语义清晰。
- 推荐：
WHERE items @> ARRAY['risk_item']（需 GIN 索引：CREATE INDEX ON orders USING GIN (items)） - 兼容老版本或简单场景：
WHERE 'risk_item' = ANY(items)（B-tree 索引需声明：CREATE INDEX ON orders ((items::text[]))） - 避免：
WHERE 'risk_item' IN (SELECT UNNEST(items))—— 触发嵌套循环，性能差且难读，还容易因空数组报错 - 注意：
ANY和@>对空数组都返回FALSE，行为一致且符合直觉
空数组和 NULL 数组必须显式兜底
UNNEST 对 NULL 数组或空数组 {} 返回零行，这会导致连接丢失整行数据——这不是 bug，是 SQL 集合语义的自然结果。
- 修复方式：用
COALESCE(tags, ARRAY[]::text[])将NULL转为空数组，再传给UNNEST - 若字段类型不明确（比如是
jsonb转来的数组），必须显式类型转换：UNNEST(COALESCE(tags::text[], ARRAY[]::text[])) - 大批量展开时还要警惕 TOAST 解压开销：数组过大（>2KB）会触发整块解压，哪怕你只取前几个元素
真正复杂的点不在语法，而在于判断「要不要展开」——多数业务查询其实只需要存在性判断或聚合统计，强行展开反而引入冗余行、破坏执行计划、放大锁竞争。先想清楚目的，再选路径。
