How to join a view on an array_agg column in Postgresql?

Question

I'm searching for the syntax to join a view with an array_agg column.

View:

CREATE VIEW sampleview AS
SELECT groupid, array_agg(akey) AS keyarray
FROM sampletable
GROUP BY groupid;

Now I want do something like this:

SELECT s.groupid, a.somedata
FROM sampleview AS s
JOIN anothertable AS a ON a.akey IN s.keyarray;

Error message:

ERROR:  syntax error at or near "s"
LINE 3: JOIN anothertable AS a ON a.akey IN s.keyarray;

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.

expected output for the pseudo query above (with the testing code):

 groupid | somedata 
---------+----------
       1 | foo
       1 | bar
       2 | monkey
(3 rows)

testing 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');

Why are you aggregating akey? That is already bad enough in a table and in a view you gain absolutely nothing as the view will be calculated at each query. — Clodoaldo Neto
– Clodoaldo Neto, Commented Jul 31, 2013 at 14:41
My sample was just a break down of the actual problem. I have a table where courses are stored, a table with the user mapping and a table (moodlecourses) where the information is stored which course of the courses table should created in a different system (moodle). In Addition it's possible to merge two or more courses together for creation in moodle. My goal was to create two views. One for the courses that should created in moodle and one for the user mapping just for this courses. My moodlecourses table has 2 columns: moodlecourseid and courseid of courses table. — jojoob
– jojoob, Commented Jul 31, 2013 at 16:13

Clodoaldo Neto · Accepted Answer · 2013-07-31 14:26:30Z

3

SELECT s.groupid, a.somedata
FROM sampleview AS s
JOIN anothertable AS a ON a.akey = any(s.keyarray);

answered Jul 31, 2013 at 14:26

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jojoob Over a year ago

Thanks a lot! Can you say me where I can find this in the documentation? I'm just interested.

Clodoaldo Neto Over a year ago

@jojoob postgresql.org/docs/current/static/…

Collectives™ on Stack Overflow

How to join a view on an array_agg column in Postgresql?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related