Filter application logs by live metric behavior and combine indexed data with inline test values. Your filter lists pull from time-series data on the fly, so nothing is hard-coded. Elasticsearch Query Language (ES|QL) subqueries now support three source commands in Elasticsearch 9. 5: FROM for indexed data, TS for time-series metrics, and ROW for inline literal values. You can use them individually or combine all three in a single query, filtering log data by live metric behavior or mixing real and synthetic rows without any index setup.
If you've been exploring ES|QL, you might have noticed that a subquery used to feel like it had exactly one front door: FROM . Most of the time, queries start with a simple directive to go fetch documents from an index. In an earlier post , we taught the WHERE command a new trick: IN subqueries. And before that, subqueries showed up in the FROM command to combine data sources. In both instances, every subquery started the same way, with FROM . But not every useful query starts with a bulk document fetch. Sometimes you need to evaluate time-series semantics.
Other times, you just need to whip up a tiny inline row for testing. Sometimes, the absolute best input to a filter is a dynamic query that builds the list for you on the fly, rather than a static, hard-coded list. The FROM , TS and ROW source commands are generally available (GA) in Elasticsearch 9. 5, while the WHERE IN subquery remains in technical preview in 9. Think of subqueries as having two specific placements and three different engines. Inside the FROM command: The subquery is an independent result source, contributing its rows to the outer query.
Fields that exist in one source but not the other are gracefully filled with null values. Inside the WHERE command: The IN subquery contributes dynamic values to be used as a predicate or filter. FROM is the familiar workhorse. You use it when the subquery should read stored data from indices, data streams, aliases, or views. One of its best use cases is the "stop-copy-pasting-IDs" pattern. Instead of running one query, manually copying the output values, and pasting them into the filter of another query, the subquery becomes your live filter list.