title: How to join a view on an array_agg column in Postgresql?
body: <p>I'm searching for the syntax to join a view with an array_agg column.</p>
  
  <p>View:</p>
  
  <pre><code>CREATE VIEW sampleview AS
  SELECT groupid, array_agg(akey) AS keyarray
  FROM sampletable
  GROUP BY groupid;
  </code></pre>
  
  <p>Now I want do something like this:</p>
  
  <pre><code>SELECT s.groupid, a.somedata
  FROM sampleview AS s
  JOIN anothertable AS a ON a.akey IN s.keyarray;
  </code></pre>
  
  <p>Error message:</p>
  
  <pre><code>ERROR:  syntax error at or near "s"
  LINE 3: JOIN anothertable AS a ON a.akey IN s.keyarray;
  </code></pre>
  
  <p>either the syntax is wrong (most likely) or it is not possible. I don't believe this is not possible but if it is I asking for an alternative option.</p>
  
  <p>expected output for the pseudo query above (with the testing code):</p>
  
  <pre><code> groupid | somedata 
  ---------+----------
         1 | foo
         1 | bar
         2 | monkey
  (3 rows)
  </code></pre>
  
  <p>testing code:</p>
  
  <pre><code>CREATE TABLE sampletable (groupid int not null, akey int not null);
  CREATE TABLE anothertable (akey int not null, somedata varchar(20));
  CREATE VIEW sampleview AS SELECT groupid, array_agg(akey) AS keyarray FROM sampletable GROUP BY groupid;
  INSERT INTO sampletable VALUES(1,20),(1,22),(2,33);
  INSERT INTO anothertable VALUES(20, 'foo'),(22,'bar'),(33,'monkey');
  </code></pre>