Given the sample below, I'm looking for how to generate this output. It's like GROUP BY, but generating an array instead of an aggreate number.
Group By creates groups, that's it. How you aggregate the data that are in those groups is determined by which function you call. Sure, a function like "count()" produces a single number, but there are other functions. Even a whole documentation section listing them.