Postgresql regex function: regexp_matches

Question

Given the string, I want to extract all the expressions that match the regular expression (like email) as an array. Here is my actual code using PostgreSQL 9.4 :

select regexp_matches('[e-mail] lorem ipsum [e-mail]',
                      '([a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4})',
                      'g')

Conclusion - 2 entries:

 regexp_matches  
 -----------------
 {[e-mail]}
 {[e-mail]}
 (2 rows)

I want all matches in one array, for example:

regexp_matches  
-----------------
{[e-mail], [e-mail]}
(1 row)

How to do it?

Answer 1

You can unnesteach array of results, and then array_aggpart of them together.

This is a little ugly:

select array_agg(x)
from (
    select unnest(
       regexp_matches('[e-mail] lorem ipsum [e-mail]',
                      '([a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,4})',
                      'g')
    )
) a(x);