Elasticsearch equivalent of NOT IN (SELECT ..)

Question

We have something like this (we save an item for each episode, show and user. And all items are saved on the same index.):

and we need to list all episodes from a show, but we need to filter the episodes already watched by the user.

In PostgreSQL i would have run something like this:

SELECT * FROM Episode WHERE show_id = 'showID' AND episode_id NOT IN (SELECT episode_id FROM watched WHERE user_id = 'userId')

But since we are using Elasticsearch, we are not sure what would be the alternative.

We also assume that the watched list is going to scale, so we can't just send an array of ids to ES.

Is there a way for Elasticsearch to handle all the logic, and filter from a dynamic array of values on a single query.

Answer 1

We just used joins, and has_child.

We filter all parents that have a watched child item.