We use them a lot where the join table could be over a million rows. Saves a ton of performance and a join. Were talking seconds here on a 3-4s query before, ~1s after.
https://github.com/pjungwir/aggs_for_arrays/
Another time arrays are handy is when you don't know how many "columns" you need to return. SQL can't do this, but a variable-length array can. Here is a writeup for one time that came in handy:
http://illuminatedcomputing.com/posts/2013/03/fun-postgres-p...
Another time they are helpful is to throw an `array_agg` into an aggregate query to see what values are getting rolled up. This can be really useful if you're trying to debug weird behavior.
Also `(array_agg(...))[1]` is a poor-man's `first` function. :-)