Postgres having query with any in array

Question

Using the following query:

select bc.name, b.* from blog b
left join blog_to_blog_category btbc on b.id = btbc.blog_id
join blog_category bc on btbc.blog_category_id = bc.id
group by b.id, bc.name
having array_agg(btbc.blog_category_id) @> array[5,6];

It will show me blogs that are in both category 5 AND 6. How do I alter this so it will show me blogs that are in either category 5 OR 6?

Bergi · Accepted Answer · 2021-05-25 23:14:14Z

1

Use the overlaps operator && instead of the contains operator @> for the two arrays.

You can however simplify by not using an aggregation but just a WHERE condition (with a normal JOIN instead of the LEFT JOIN):

select bc.name, b.*
from blog b
join blog_to_blog_category btbc on b.id = btbc.blog_id
join blog_category bc on btbc.blog_category_id = bc.id
where bc.id in (5, 6)
group by b.id, bc.name

_{(I suspect this will also be more efficient / optimised better than the array aggregate)}

edited May 25, 2021 at 23:14

answered May 25, 2021 at 23:09

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

2 Comments

Guerrilla Over a year ago

Thank you. What is reason that left join shouldn't be used?

Bergi Over a year ago

Without the having clause, you'd get all blogs (and null for the category name) when doing a left join and filtering only categories

Collectives™ on Stack Overflow

Postgres having query with any in array

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