ES|QL's WHERE clause can filter by another Elasticsearch subquery's results instead of a static ID list you copied by hand, with nested subqueries, NOT IN and compound conditions built in. The Elasticsearch Query Language (ES|QL) WHERE clause can now filter by the results of another query. If you've been running one query to find suspicious users or failing services, copying the IDs, then pasting them into a second query, you can stop. One ES|QL statement does the whole job: the subquery builds the filter list from live data, and it stays current every time you run it.
The feature ships as a technical preview in Elasticsearch 9. 5 and supports nesting, NOT IN and compound AND / OR conditions. The old copy-paste loop collapses into a single dynamic filter. Traditional IN filtering is still useful when the list is small and static: But many real investigations don’t start with a tidy list. They start with a question: Which users are suspicious , which hosts are noisy , which services are failing , or which accounts crossed a threshold? That’s where the WHERE IN subquery becomes useful. The list is produced by ES|QL rather than typed by hand.
Read it like this: Show me log events for users who appear in the list of users with at least 10 failed login attempts. The outer query asks the main question, and the subquery builds the dynamic filter list, eliminating the need to copy and paste. The subquery can target a different index or index pattern from the outer query. Imagine that you want to inspect traffic for the top failing services. First you run: Then you copy the five service names and paste them into another query: That’s fine once, but less fine when the top five change every hour.
The subquery finds the top failing services from the last two days, and the outer query returns the log events for those services. One query builds the full picture. Sometimes the interesting question is about what doesn’t belong: That pattern is useful for exclusion checks, gap analysis, and workflows that ask for the things outside an approved or expected set. An IN subquery replaces the literal value list with a query in parentheses. The inner query runs first and returns a single column, and the outer WHERE filters against it.