title: is it possible to nest an array_agg inside another array_agg
body: <p>I have a SQL table <code>message(application, type, action, date, ...)</code> and I would like to get all the actions for a type and all the types for an application in a single query if possible.</p>
  
  <p>So far I have managed to get the result in two separate queries like so:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select application, array_agg(distinct type) as types from message group by application;
   application  |                                                           types                                                            
  --------------+----------------------------------------------------------------------------------------------------------------------------
   app1          | {company,user}
   app2          | {document,template}
   app3          | {organization,user}
  
  </code></pre>
  
  <p>and the second query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select type, array_agg(distinct action) as actions from message group by type;
              type                      |                 actions                 
  --------------------------------------+-----------------------------------------
   company                              | {created,updated}
   document                             | {created,tested,approved}
   organization                         | {updated}
   template                             | {deleted}
   user                                 | {created,logged,updated}
  
  </code></pre>
  
  <p>The most obvious single query I could come up with so far is just:</p>
  
  <pre><code>select application, type, array_agg(distinct action) from message group by application, type;
  </code></pre>
  
  <p>Which would require some programmatic processing to build the <code>type</code> array.</p>
  
  <p>What I wanted to do was something theoretically like:
  <code>select application, array_agg(type, array_agg(action)) from message group by application, type</code> which isn't possible as is but I feel there is a way to do it. I have also thought about nesting the second query into the first one but haven't found how to make it work yet.</p>