PostgreSQL中如何提取集合元素及处理regexp_matches匹配结果？

1. 将匹配结果展开为单条记录 #

用unnest()函数能把regexp_matches返回的数组拆成单独的行。如果需要去重，直接在CTE里嵌套unnest就行：

WITH raw_matches AS(
    SELECT DISTINCT unnest(regexp_matches(f.bar, 'my-fancy-regex', 'g')) AS match
    FROM foo f
)
SELECT match
FROM raw_matches;

要是不需要先在CTE里处理，直接在主查询里展开+去重也可以：

SELECT unnest(regexp_matches(f.bar, 'my-fancy-regex', 'g')) AS match
FROM foo f
GROUP BY match;

2. 将匹配结果转换为字符串数组（或直接操作数组） #

其实regexp_matches返回的本身就是text类型的数组，直接用PostgreSQL的数组函数就能处理：

• 查看数组长度：

WITH raw_matches AS(
    SELECT regexp_matches(f.bar, 'my-fancy-regex', 'g') AS match_arr
    FROM foo f
)
SELECT match_arr, array_length(match_arr, 1) AS 匹配项数量
FROM raw_matches;

• 把所有匹配项合并成一个全局数组：

SELECT array_agg(DISTINCT unnest(regexp_matches(f.bar, 'my-fancy-regex', 'g'))) AS 所有匹配项数组
FROM foo f;

注意：加g参数是全局匹配，会返回所有符合正则的结果组成的数组；不加g的话，只会返回第一个匹配项的数组。

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