Postgres Aggregate over unnest

Question

I have a query like the following:

select count(unnest(regexp_matches(column_name, regex))) 
from table_name group by unnest(regexp_matches(column_name, regex));

The above query gives the following error:

ERROR: aggregate function calls cannot contain set-returning function calls
  Hint: You might be able to move the set-returning function into a LATERAL FROM item.

I know I can first calculate unnested values by nesting a select query in from clause and then find the total count. But I was wondering why Postgres does not allow such expression?

Answer 1

It's unclear to me, what result you are after. But in general, you need to move the unnest to the FROM clause to do anything "regular" with the values

If you want to count per value extracted you can use:

select u.val, count(*) 
from table_name t
  cross join unnest(regexp_matches(t.column_name, regex)) as u(val)
group by u.val;

Or maybe you want to count per "column_name"?

select t.column_name, count(*) 
from table_name t
  cross join unnest(regexp_matches(t.column_name, regex)) as u(val)
group by t.column_name;