PostgreSQL query get all entries with many-to-many relationship with another table

I have a table posts which has a many-to-many relationship with tags, the pivot table is called posts-tags.

I want to be able to retrieve all posts by a list of tag id’s.

Imagine

posts

id | text
--------
1  | "foo"
2  | "bar"
3  | "baz"

posts_tags

post_id | tag_id
-----------------
1       | 1
1       | 2
1       | 3
2       | 1
3       | 1

tags

id | name
--------
1  | "foo"
2  | "bar"
3  | "baz"

With tag id’s [1, 2, 3], I should get back [{id: 1, text: "foo"}]

With tag id’s [1], I should get back [{id: 1, text: "foo"}, {id: 2, text: "bar"}, {id: 3, text: "baz"}]

Basically, I want to retrieve all the posts related to the list of tags.

Answer

You can use a subquery to filter posts that have all the specified tags:

select json_agg(json_build_object('id', p.id, 'text', p.txt)) 
from posts p where (select count(*) from json_array_elements('[1, 2, 3]') v 
      join post_tags t on t.post_id = p.id and v.value::text::int = t.tag_id) = json_array_length('[1, 2, 3]')

See fiddle.