Combine result sets with ES|QL subqueries in a FROM command

A subquery is a complete ES|QL query wrapped in parentheses that can be used in place of an index pattern in the FROM command. Each subquery is executed independently. The final output combines all these results into a single list, including any duplicate rows.

FROM index_pattern [, subquery]* [METADATA fields]
FROM subquery [, subquery]* [METADATA fields]
		

1. When an index pattern is present, zero or more subqueries can follow.
2. Without an index pattern, one or more subqueries are required.

Each subquery is a source command followed by zero or more piped processing commands, all enclosed in parentheses. The source command can be FROM, TS, or ROW:

(FROM index_pattern [METADATA fields] [| processing_commands])
(TS index_pattern [METADATA fields] [| processing_commands])
(ROW column1 = value1[, ..., columnN = valueN] [| processing_commands])
		

Multiple subqueries and regular index patterns can be combined in a single FROM clause, separated by commas.

Much like views, subqueries enable you to combine results from multiple independently processed data sources within a single query. Each subquery runs its own pipeline of processing commands (such as WHERE, EVAL, STATS, or SORT) and the results are combined together with results from other index patterns, views or subqueries in the FROM clause.

Fields that exist in one source but not another are filled with null values.

The subquery pipeline can include commands such as the following:

Source commands:

• FROM: read from an index pattern.
• TS: read from a time series index pattern.
• ROW: synthesize rows from literal values.

Processing commands:

• CHANGE_POINT
• COMPLETION
• DISSECT
• DROP
• ENRICH
• EVAL
• GROK
• INLINE STATS
• KEEP
• LIMIT
• LOOKUP JOIN
• MV_EXPAND
• RENAME
• RERANK
• SAMPLE
• SORT
• STATS
• WHERE

The METADATA directive is also supported on either the subquery or the outer FROM.

The following examples show how to use subqueries within the FROM command.

Use a subquery alongside a regular index pattern to combine results from different indices:

FROM employees,
     (FROM sample_data)
| WHERE (emp_no >= 10091 AND emp_no < 10094)  OR emp_no IS NULL
| SORT emp_no, client_ip
| KEEP emp_no, languages, client_ip
		

Rows from employees have null for client_ip, while rows from sample_data have null for emp_no and languages, because each index has different fields.

You can use one or more subqueries without specifying a regular index pattern:

FROM (FROM employees)
| WHERE emp_no >= 10091 AND emp_no < 10094
| SORT emp_no
| KEEP emp_no, languages
		

The FROM clause contains only a subquery with no regular index pattern. The subquery wraps the employees index, and the outer query filters, sorts, and projects the results.

Apply a WHERE clause inside the subquery to pre-filter data before combining:

FROM employees,
     (FROM sample_data metadata _index
      | WHERE client_ip == "172.21.3.15")
     metadata _index
| WHERE (emp_no >= 10091 AND emp_no < 10094) OR emp_no IS NULL
| EVAL _index = MV_LAST(SPLIT(_index, ":"))
| SORT emp_no
| KEEP _index, emp_no, languages, client_ip
		

The WHERE inside the subquery filters sample_data to only rows where client_ip is 172.21.3.15 before combining with employees. The _index metadata field shows which index each row originated from.

Use STATS inside a subquery to aggregate data before combining with other sources:

FROM employees,
     (FROM sample_data metadata _index
      | STATS cnt = count(*) by _index, client_ip)
     metadata _index
| WHERE (emp_no >= 10091 AND emp_no < 10094) OR emp_no IS NULL
| EVAL _index = MV_LAST(SPLIT(_index, ":"))
| SORT _index, emp_no, client_ip
| KEEP _index, emp_no, languages, cnt, client_ip
		

The STATS inside the subquery aggregates sample_data by counting rows per client_ip before combining with employees. The cnt column is null for employees rows since that field only exists in the subquery output.

Multiple subqueries can be combined in a single FROM clause:

FROM employees,
     (FROM sample_data metadata _index
      | STATS cnt = count(*) by _index, client_ip),
     (FROM sample_data_str metadata _index
      | STATS cnt = count(*) by _index, client_ip)
     metadata _index
| EVAL client_ip = client_ip::ip, _index = MV_LAST(SPLIT(_index, ":"))
| WHERE client_ip == "172.21.3.15" AND cnt >0
| SORT _index, emp_no, client_ip
| KEEP _index, emp_no, languages, cnt, client_ip
		

Two subqueries aggregate sample_data and sample_data_str separately, each counting rows by client_ip. The results are combined and then filtered to only show rows where client_ip is 172.21.3.15. The _index field confirms each row's source.

Enrich subquery results with a lookup join before combining:

FROM employees,
     (FROM sample_data
      | EVAL client_ip = client_ip::keyword
      | LOOKUP JOIN clientips_lookup ON client_ip)
| WHERE (emp_no >= 10091 AND emp_no < 10094) OR emp_no IS NULL
| SORT emp_no, client_ip
| KEEP emp_no, languages, client_ip, env
		

The LOOKUP JOIN inside the subquery joins each sample_data row with the env field from clientips_lookup based on client_ip. The env column is null for employees rows since the lookup only applies within the subquery.

Use SORT and LIMIT inside a subquery to return only top results:

FROM employees,
     (FROM sample_data
      | STATS cnt = count(*) by client_ip
      | SORT cnt DESC
      | LIMIT 1)
| WHERE (emp_no >= 10091 AND emp_no < 10094) OR emp_no IS NULL
| SORT emp_no, client_ip
| KEEP emp_no, languages, cnt, client_ip
		

The subquery aggregates sample_data by client_ip, sorts by count in descending order, and limits to the top result. Only the client_ip with the highest count (172.21.3.15 with 4 occurrences) is included when combined with employees.

Use a TS subquery to read from a time series index and combine the results with a regular index:

FROM sample_data,
     (TS k8s
      | STATS max_rate=max(rate(network.total_bytes_in)) BY cluster
      | EVAL max_rate=ROUND(max_rate, 4))
| EVAL has_ts = max_rate IS NOT NULL, rate_per_minute = max_rate * 60
| WHERE rate_per_minute IS NOT NULL OR client_ip == "172.21.3.15"
| SORT has_ts, cluster, @timestamp
| KEEP @timestamp, client_ip, cluster, max_rate, rate_per_minute
		

The TS subquery reads the k8s time series index and uses the RATE function to compute the maximum per-second rate of the network.total_bytes_in counter per cluster. The outer query combines these aggregates with sample_data, derives rate_per_minute from max_rate, and keeps the time series rows plus the sample_data rows for client IP 172.21.3.15. Because each branch exposes different fields, sample_data rows have null for cluster, max_rate, and rate_per_minute, while the time series rows have null for client_ip.

Combine several TS subqueries in a single FROM clause, without a regular index pattern:

FROM
    (TS k8s | STATS max_bytes=max(to_long(network.total_bytes_in)) BY cluster | KEEP cluster, max_bytes),
    (TS k8s-downsampled | STATS max_eth0_tx = max(network.eth0.tx) BY pod | KEEP pod, max_eth0_tx)
| SORT cluster, pod
| KEEP cluster, max_bytes, pod, max_eth0_tx
		

Each TS subquery aggregates a different time series index (k8s by cluster and k8s-downsampled by pod). Because the two branches expose different fields, each row carries null for the columns that come from the other subquery.

Use a ROW subquery to introduce rows with literal values alongside data read from an index:

FROM
    employees,
    (ROW emp_no = 99999, languages = 99)
| WHERE (emp_no >= 10091 AND emp_no < 10094) OR emp_no == 99999
| SORT emp_no
| KEEP emp_no, languages
		

The ROW subquery contributes a single synthesized (emp_no, languages) row that is combined with the matching rows from employees.

Multiple ROW subqueries can be combined without a regular index pattern:

FROM
    (ROW emp_no = 1, languages = 5),
    (ROW emp_no = 2, languages = 10),
    (ROW emp_no = 3, languages = 15)
| SORT emp_no
| KEEP emp_no, languages
		

Each ROW subquery contributes one row, and the outer query sorts and projects the combined results.

Different source commands can be mixed in a single FROM clause. This example combines a regular index pattern with a FROM subquery, a TS subquery, and a ROW subquery:

FROM sample_data,
     (FROM sample_data | WHERE client_ip == "172.21.0.5"),
     (TS k8s
      | STATS max_bytes=max(to_long(network.total_bytes_in)) BY cluster
      | SORT cluster),
     (ROW cluster = "synthetic", max_bytes = to_long(99999))
| WHERE max_bytes IS NOT NULL OR client_ip == "172.21.0.5"
| SORT max_bytes, cluster, @timestamp
| KEEP @timestamp, client_ip, cluster, max_bytes
		

Each branch contributes its own rows to the combined result: sample_data and the FROM subquery provide the client_ip rows, the TS subquery provides the per-cluster aggregates, and the ROW subquery provides the synthesized synthetic row. Fields that don't exist in a given branch are filled with null.

The METADATA directive is supported both inside and outside a subquery. If the directive is used only outside the subquery, it will report null for the values within the subquery:

FROM employees,
     (FROM sample_data)
         METADATA _index, _index_mode
| WHERE emp_no == 10091 OR emp_no IS NULL
| STATS count=COUNT(*) BY emp_no, _index, _index_mode
		

To see the combined values from within the subquery include the directive inside as well:

FROM employees,
     (FROM sample_data METADATA _index, _index_mode)
         METADATA _index, _index_mode
| WHERE emp_no == 10091 OR emp_no IS NULL
| STATS count=COUNT(*) BY emp_no, _index, _index_mode
		

If you only have the directive within the subquery, null values will be returned for the indices outside the subquery:

FROM employees,
     (FROM sample_data METADATA _index, _index_mode)
| WHERE emp_no == 10091 OR emp_no IS NULL
| STATS count=COUNT(*) BY emp_no, _index, _index_mode
		

A subquery cannot contain another subquery in the FROM command. Only one level of nesting is allowed.

For example, this query is not supported because the inner FROM itself contains subqueries:

FROM
    (FROM
        (FROM employees | WHERE emp_no > 10090),
        (FROM sample_data | WHERE client_ip == "172.21.3.15")
    ),
    (FROM
        (FROM employees | WHERE emp_no < 10010),
        (FROM sample_data | WHERE client_ip == "172.21.0.5")
    )
| KEEP emp_no, languages, client_ip
		

Instead, use only a single level of subqueries:

FROM
    (FROM employees | WHERE emp_no > 10090),
    (FROM sample_data | WHERE client_ip == "172.21.3.15"),
    (FROM employees | WHERE emp_no < 10010),
    (FROM sample_data | WHERE client_ip == "172.21.0.5")
| KEEP emp_no, languages, client_ip
		

The FORK command cannot be used inside a subquery or after a FROM that contains subqueries.

For example, using FORK inside a subquery is not supported:

FROM
    (FROM employees
     | FORK
         (WHERE emp_no > 10090)
         (WHERE emp_no < 10010))
| KEEP emp_no, languages
		

And using FORK after a FROM with subqueries is also not supported:

FROM
    employees,
    (FROM sample_data | WHERE client_ip == "172.21.3.15")
| FORK
    (WHERE emp_no > 10090)
    (WHERE client_ip IS NOT NULL)
		

Views, subqueries and the FORK command are related. There are many similarities and differences between them.

• FORK allows data coming from previous commands, like an initial FROM index command, to be processed in parallel in multiple different branches, each performing different commands on the same original data.
• Subqueries also enable parallel processing, but allow each branch to use a different source index with a different FROM command per branch. Views are reusable, named queries that act like virtual indices. Each view has its own FROM command and processing pipeline, and can be referenced like a regular index.

• Dynamic execution. All three mechanisms will process the entire set of query definitions at query time, resulting in an up-to-date response when source indexes are changed and the query is re-run.
• Union of columns. Columns from the results of multiple branches are merged into the main query, expanding the table of results, and inserting null values if any branch has different columns than the others.
• Supported commands. Complex processing commands can be used inside both views and subqueries, as detailed in the description of subqueries.
• No nested branching. Nested branching is generally not supported, but views can work around this limitation through query compaction.
• Maximum branch count. All of these approaches to parallel processing are bound by the same maximum branch count of 8.

The FORK command never includes a FROM command, and relies entirely on an existing query to provide the incoming columns. This also means that all branches will receive identical incoming data, the same columns and the same rows. This is not true of subqueries or views, which can receive completely different columns and rows from their own FROM commands. Only one FORK command is allowed per query, so nested branches are not possible. This limitation is partially true for views and subqueries, but to a lesser extent as described below.

Views have names, and these names are unique within the index namespace. This means a view cannot have the same name as an index, and vice versa. Views can be nested within one another, as long as neither of the following two rules are broken:

• Cyclic references are not allowed. For example, if viewA references viewB and viewB references viewC it is not allowed to have viewC reference viewA.
  • Detection of cyclic references is done at main query execution time
• Multiple branching points do not exist

This last point highlights a difference between views and subqueries. While subqueries simply disallow the use of further subqueries or FORK within a subquery, views will allow this under limited conditions.

• Query multiple sources: high-level overview of combining data from multiple indices, clusters, subqueries, and views.
• Define virtual indices using ES|QL views: the closest alternative to subqueries, with a persisted, named definition.
• FROM command: full reference for index expressions, where subqueries are used.
• FORK command: the other branching construct in ES|QL, which shares the same branching limits.
• Query multiple indices: how index patterns, wildcards, and date math combine sources in a single FROM.