查数组需分场景:取值要校验长度并注意下标从1开始;判断元素存在优先用@>而非ANY以利用GIN索引;展开数组必须用LATERAL UNNEST;区分NULL与空数组唯一可靠方式是array_length。

直接查数组字段本身不会报错,但多数人真正想要的不是“把整个数组当字符串吐出来”,而是安全、准确地判断元素是否存在、提取特定位置值或展开成行——这三类需求对应完全不同的写法,混用就会漏数据或返回空结果。
查数组中某个位置的值要先确认长度
PostgreSQL 数组下标从 1 开始,tags[0] 或 tags[-1] 不报错但返回 NULL;更危险的是 tags[2] 在只有 1 个元素的行里也返回 NULL,而 WHERE tags[2] = 'mobile' 因为 NULL = 'mobile' 永远为 FALSE,该行直接被过滤掉。
- 必须加长度校验:
WHERE array_length(tags, 1) >= 2 AND tags[2] = 'mobile' - 如果字段本身是
NULL,array_length(tags, 1)也返回NULL,所以这个条件天然跳过NULL行 - 仅当业务强依赖顺序(如“第二标签代表主分类”)时才用下标,否则优先用
ANY或@>
判断数组是否含某元素别只用 = ANY
'SQL' = ANY(skills) 语法简单,但性能差:即使 skills 上建了 GIN 索引,ANY 通常也无法有效利用,容易退化为全表扫描。
- 单值匹配推荐:
skills @> ARRAY['SQL']—— 支持GIN索引,且语义明确 - 多值同时存在(交集):
skills @> ARRAY['Python', 'Docker'] - 只要有一个重叠(并集):
skills && ARRAY['Java', 'C++'] - 注意:
skills @> '{}'::TEXT[]对空数组返回TRUE,但对NULL字段返回NULL(即不匹配)
用 UNNEST 展开数组必须配合 LATERAL
UNNEST 不是过滤函数,不能直接写在 WHERE 里,比如 WHERE UNNEST(tags) = 'electronics' 会报语法错误。
- 正确写法是:
SELECT id, tag FROM array_demo, LATERAL UNNEST(tags) AS tag WHERE tag = 'electronics' - 如果原行
tags是NULL,LATERAL UNNEST默认不生成任何行,该记录消失;需保留就改用LEFT JOIN LATERAL UNNEST(tags) AS tag ON TRUE - 展开后行数会膨胀,
LIMIT 10要放在最外层,否则可能只取到第一行数组展开的前几条,漏掉其他行的数据
区分 NULL 和空数组得靠 ARRAY_LENGTH
ARRAY_LENGTH(tags, 1) 对 NULL 输入返回 NULL,对空数组(如 '{}'::TEXT[])返回 0。这是唯一能稳定区分二者的方式。
- 错误写法:
WHERE tags IS NOT NULL—— 排除了NULL,但空数组仍参与后续逻辑,可能造成误判 - 安全写法:
WHERE array_length(tags, 1) > 0—— 同时排除NULL和空数组 - 如果业务需要分别处理(比如空数组算“未打标”,
NULL算“数据异常”),就必须用array_length+IS NULL组合判断

















