Is your feature request related to a problem? Please describe.
Currently, if you want to aggregate multiple arrays from different rows into a single array, you first need to unnest all the arrays, and then re-aggregate them using array_agg. This often requires a LATERAL join, which may not be possible to decorrelate, resulting in a very inefficient plan. This issue is currently affecting the performance of the SQL activity page in the DB console.

Describe the solution you'd like
We should create a new builtin aggregate function called array_concat_agg, that allows a user to directly aggregate a set of arrays into a single array.

Describe alternatives you've considered
The alternative is to continue using unnest + array_agg, which as described above, is inefficient.

cc @maryliag

Jira issue: CRDB-24730