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?

Answer 1

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))