如何在SQL的unnest查询中过滤出表类型（r标识）的记录？

从你的描述和输入示例来看，你需要筛选出仅包含r标识（排除带v/f标识）的展开后记录。下面针对两种常见的数组结构场景给出可靠实现：

场景1：标识是数组的最后一个元素 #

如果每个展开的数组（比如{a,b,r}）最后一位固定是类型标识，直接提取最后一个元素判断即可：

简洁写法（用CTE避免重复计算）： #

WITH expanded AS (
    SELECT unnest(array_cat(a.object_references, a.dependent_object_references)) AS obj_array
    FROM mgmt.log_metrics a
)
SELECT obj_array
FROM expanded
WHERE obj_array[array_length(obj_array, 1)] = 'r';

单语句写法： #

SELECT unnest(array_cat(a.object_references, a.dependent_object_references)) AS obj_array
FROM mgmt.log_metrics a
WHERE (unnest(array_cat(a.object_references, a.dependent_object_references)))[array_length(unnest(array_cat(a.object_references, a.dependent_object_references)), 1)] = 'r';

场景2：数组中含r且无其他标识 #

如果数组里可能分散着标识，但你需要确保只存在r、完全没有v和f，用数组包含运算符@>来判断：

WITH expanded AS (
    SELECT unnest(array_cat(a.object_references, a.dependent_object_references)) AS obj_array
    FROM mgmt.log_metrics a
)
SELECT obj_array
FROM expanded
WHERE obj_array @> ARRAY['r']::text[]
AND NOT (obj_array @> ARRAY['v']::text[] OR obj_array @> ARRAY['f']::text[]);

运算符说明： #

• obj_array @> ARRAY['r']：判断数组是否包含r
• NOT (obj_array @> ARRAY['v'] OR ...)：确保数组不包含v或f

为什么你之前的方法失效？ #

你尝试的substring()、right()是字符串函数，但你操作的是数组类型，直接用这些函数会把数组转成字符串（比如{a,b,r}转成字符串后是"{a,b,r}"），格式处理容易出错。如果非要用字符串方法（不推荐，依赖格式），可以这样写：

WITH expanded AS (
    SELECT unnest(array_cat(a.object_references, a.dependent_object_references)) AS obj_array
    FROM mgmt.log_metrics a
)
SELECT obj_array
FROM expanded
WHERE right(obj_array::text, 2) = ',r}';

但这种方法不如直接操作数组稳定，建议优先用前面的数组操作方案。

内容的提问来源于stack exchange，提问作者user26309744