UNNEST是将数组展开为多行的表函数，非SQL标准，PostgreSQL、BigQuery、Trino等支持，MySQL/Oracle/SQL Server原生不支持；需配合LATERAL JOIN或逗号语法使用，输入必须为数组类型，且须指定别名。
UNNEST 是什么，哪些数据库能用
UNNEST 不是 SQL 标准函数，而是 PostgreSQL、BigQuery、Trino（原 PrestoSQL）等少数引擎支持的特有操作，用于把数组或结构化集合“炸开”成多行。MySQL、SQL Server、Oracle 原生不支持 UNNEST，硬写会报错 function unnest does not exist 或类似提示。
如果你在 pgAdmin 或 psql 里执行没问题，但换到 DBeaver 连 MySQL 就失败——先确认当前数据库是否真支持它。
- PostgreSQL：支持，语法为
SELECT * FROM UNNEST(array_column) - BigQuery：支持，但必须搭配
UNNEST()作为FROM子句的一部分，不能单独用 - Trino：支持，行为接近 PostgreSQL，但对空数组处理略有差异（默认跳过，需显式处理）
- SQLite / MySQL / SQL Server：不支持，得用递归 CTE、JSON_TABLE（MySQL 8.0+）、或字符串拆分模拟
UNNEST 的基本写法和常见错误
最典型错误是直接对非数组字段调用 UNNEST，比如误把字符串当数组：UNNEST('a,b,c') 在 PostgreSQL 会报错 cannot cast type text to array。
正确做法是确保输入是数组类型：
- PostgreSQL 示例：
SELECT * FROM UNNEST(ARRAY['apple', 'banana', 'cherry']) AS fruit; - BigQuery 示例：
SELECT fruit FROM UNNEST(['apple', 'banana', 'cherry']) AS fruit; - 如果字段本身是表中一列（如
tags TEXT[]），就写UNNEST(tags)，不是UNNEST(tags::TEXT[])——类型已明确，强制转换反而可能出错 - 别漏掉
AS alias：PostgreSQL 要求给展开结果起别名，否则报错column must have a name
和 JOIN 配合展开关联数组字段
实际业务中，UNNEST 很少单独用，常配合主表做横向展开。比如订单表带一个 product_ids INT[] 字段，想查每个订单对应每个商品 ID 的明细行。
这时不能写成 SELECT *, UNNEST(product_ids) FROM orders——PostgreSQL 允许，但 BigQuery 会报错 UNNEST expression references column orders.product_ids which is not in the GROUP BY clause。
- PostgreSQL 正确写法：
SELECT id, UNNEST(product_ids) AS pid FROM orders; - BigQuery 必须用逗号语法：
SELECT id, pid FROM orders, UNNEST(product_ids) AS pid; - 如果还要保留原数组长度信息，加
WITH ORDINALITY（PostgreSQL/Trino 支持）：UNNEST(product_ids) WITH ORDINALITY AS pid(pos)，pos是从 1 开始的序号 - 空数组处理：默认被跳过。若需保留空数组对应的空行，PostgreSQL 可用
LEFT JOIN LATERAL UNNEST(...)，BigQuery 无原生方案，得靠ARRAY_LENGTH(...) = 0单独补行
性能和数据一致性要注意什么
UNNEST 展开后行数可能爆炸式增长——100 行订单，每行平均 5 个标签，展开后就是 500 行。没加 LIMIT 或没过滤就跑全表，容易拖慢查询甚至 OOM。
- 避免在 WHERE 之前展开：先
WHERE status = 'paid'再UNNEST，而不是反过来 - BigQuery 对
UNNEST后的字段无法直接建分区或聚簇，如果常按展开字段过滤，考虑提前物化成宽表 - PostgreSQL 中，
UNNEST不走索引；如果频繁按数组内元素查，建议额外建 GIN 索引：CREATE INDEX ON orders USING GIN (product_ids); - 注意 NULL 数组：PostgreSQL 中
UNNEST(NULL::TEXT[])返回零行；BigQuery 中UNNEST(NULL)直接报错，必须先IFNULL(arr, [])
真正麻烦的不是语法，而是展开后行与行之间的语义关系容易断掉——比如原数组顺序丢失、重复元素去重逻辑缺失、空值边界模糊。这些得靠业务层校验，SQL 本身不保证。
