Postgres: filtering results using ARRAY_AGG and HAVING (instead of WHERE)

Question

So my database contains a listing of items:

items:  item.id | item.title

And I have a second table that associates a number of tags with the items:

tags:  tag.id | item.id

An item may have several tags (e.g. 5, 20, and 25) associated with it, and thus would have a row for each of those tag-values in the tags table.

I want to be able to determine if a particular tag is associated with an item, without losing all other tag data. For example,

SELECT items.id, items.title
FROM items
INNER JOIN tags ON (tag.tag_id=items.id)
WHERE tag.id =27

doesn't work because it eliminates the rows containing the other tag values (if tag.id is 27, it cannot be any other value on a particular row).

To solve that, I want to use ARRAY_AGG to roll up the individual tag numbers into an array, against which I can (should) easily test values for membership.

SELECT items.id, items.title
FROM items
INNER JOIN tags ON (tag.tag_id=items.id)
GROUP BY items.id
HAVING ANY(ARRAY_AGG(tags.tag_id)=27)

(I saw this post about how you can't SELECT an AS-named column with ARRAY_AGG )

But the above code produces a syntax error on the ANY portion of the HAVING clause.

Thoughts?

I was using the tags table to filter the results from the items table. For example, items may have one or more of 5 tags (1=Good, 2=Bad, 3=Red, 4=Green, 5=Yellow) and I want to select items that are both Good and Yellow. — bhoward
– bhoward, Commented Nov 13, 2013 at 1:25

bhoward · Accepted Answer · 2013-11-13 01:25:03Z

41

Turns out that Postgres's ANY keyword is sided and can't be used symmetrically.

Thus the working code is:

SELECT items.id, items.title, ARRAY_AGG(tags.tag_id)
FROM items
INNER JOIN tags ON (tag.tag_id=items.id)
GROUP BY items.id
HAVING 27 = ANY(ARRAY_AGG(tags.tag_id))

answered Nov 13, 2013 at 1:25

bhoward

2,9004 gold badges25 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

Postgres: filtering results using ARRAY_AGG and HAVING (instead of WHERE)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related