ES|QL limitations

By default, an ES|QL query returns up to 1,000 rows. You can increase the number of rows up to 10,000 using the LIMIT command.

For instance,

FROM index | WHERE field = "value"
		

is equivalent to:

FROM index | WHERE field = "value" | LIMIT 1000
		

Queries do not return more than 10,000 rows, regardless of the LIMIT command’s value. This is a configurable upper limit.

To overcome this limitation:

• Reduce the result set size by modifying the query to only return relevant data. Use WHERE to select a smaller subset of the data.
• Shift any post-query processing to the query itself. You can use the ES|QL STATS command to aggregate data in the query.

The upper limit only applies to the number of rows that are output by the query, not to the number of documents it processes: the query runs on the full data set.

Consider the following two queries:

FROM index | WHERE field0 == "value" | LIMIT 20000
		

and

FROM index | STATS AVG(field1) BY field2 | LIMIT 20000
		

In both cases, the filtering by field0 in the first query or the grouping by field2 in the second is applied over all the documents present in the index, irrespective of their number or indexes size. However, both queries will return at most 10,000 rows, even if there were more rows available to return.

The default and maximum limits can be changed using these dynamic cluster settings:

• esql.query.result_truncation_default_size
• esql.query.result_truncation_max_size

For time-series queries that use the TS or PROMQL source commands (including requests to the PromQL HTTP API), the default and maximum limits are higher and can be changed using these cluster settings:

• esql.query.timeseries_result_truncation_default_size
• esql.query.timeseries_result_truncation_max_size

Modifying these values involves trade-offs. A larger result-set involves a higher memory pressure and increased processing times. Internode traffic within and across clusters can also increase.

These limitations are similar to those enforced by the search API for pagination.

ES|QL currently supports the following field types:

• alias

• boolean

• date

• date_nanos (Tech Preview)

  • The following functions don’t yet support date nanos: bucket, date_format, date_parse, date_diff, date_extract
  • You can use to_datetime to cast to millisecond dates to use unsupported functions

• date_range range family

• double (float, half_float, scaled_float are represented as double)

• double_range range family

• dense_vector

• flattened

• ip

• keyword family including keyword, constant_keyword, and wildcard

• int (short and byte are represented as int)

• long

• null

• text family including text, semantic_text and match_only_text

• unsigned_long

• version

• Spatial types

  • geo_point
  • geo_shape
  • point
  • shape

• TSDB metrics

  • counter
  • gauge
  • aggregate_metric_double: Aggregation functions that do not natively support aggregate_metric_double will use the average value and treat it as a double.
  • exponential_histogram
  • tdigest

ES|QL does not support certain field types. If the limitation only applies to specific product versions, it is indicated in the following list:

• dense_vector

• TSDB metrics

  • counter
  • gauge
  • aggregate_metric_double

• Other types

  • binary
  • completion
  • float_range
  • histogram (can be queried via casting to tdigest or exponential_histogram)
  • integer_range
  • ip_range
  • long_range
  • nested
  • rank_feature
  • rank_features
  • rank_vectors
  • search_as_you_type

Querying a column with an unsupported type returns an error. If a column with an unsupported type is not explicitly used in a query, it is returned with null values, with the exception of nested fields. Nested fields are not returned at all. To understand how unsupported types are reported in the API response, refer to column metadata.

Some field types are not supported in all contexts:

• Spatial types are not supported in the SORT processing command. Specifying an expression that evaluates to one of these types as a sort key will result in an error:

  • geo_point
  • geo_shape
  • cartesian_point
  • cartesian_shape

• In addition, when querying multiple indexes, it’s possible for the same field to be mapped to multiple types. These fields cannot be directly used in queries or returned in results, unless they’re explicitly converted to a single type.

The spatial types geo_point, geo_shape, cartesian_point and cartesian_shape are maintained at source precision in the original documents, but indexed at reduced precision by Lucene, for performance reasons. To ensure this optimization is available in the widest context, all spatial functions will produce results at this reduced precision, aligned with the underlying Lucene index grid. For geo_point and geo_shape, this grid is smaller than 1 cm at the equator, which is still very high precision for most use cases. If the exact, original precision is desired, return the original field in the ES|QL query, which will maintain the original values. To prioritize performance over precision, simply drop that field.

For example:

FROM airports
| EVAL geohex = ST_GEOHEX(location, 1)
| KEEP location, geohex
		

This query will perform slowly, due to the need to retrieve the original location field from the source document. However, the following example will perform much faster:

FROM airports
| EVAL geohex = ST_GEOHEX(location, 1)
| EVAL x = ST_X(location), y = ST_Y(location)
| KEEP x, y, geohex
		

This query will perform much faster, since the original field location is not retrieved, and the three spatial functions used will all return values aligned with the Lucene index grid. Note that if you return both the original location and the extracted x and y you will see very slight differences in the extracted values due to the precision loss.

• The following types are only partially supported on 9.2.0. This is fixed in 9.2.1:

  • dense_vector: The KNN function and the TO_DENSE_VECTOR function will work and any field data will be retrieved as part of the results. However, the type will appear as unsupported when these functions are not used.

  • aggregate_metric_double: Using the TO_AGGREGATE_METRIC_DOUBLE function will work and any field data will be retrieved as part of the results. However, the type will appear as unsupported if this function is not used.

    Note

    This means that a simple query like FROM test will not retrieve dense_vector or aggregate_metric_double data. However, using the appropriate functions will work:

    • FROM test WHERE KNN("dense_vector_field", [0, 1, 2, ...])
    • FROM test | EVAL agm_data = TO_AGGREGATE_METRIC_DOUBLE(aggregate_metric_double_field)

ES|QL respects runtime fields defined in the index mapping and treats them like regular mapped fields. Use the EVAL command to compute fields at query time, the built-in equivalent of runtime fields.

Runtime fields are different from unmapped fields. An unmapped field is a field that does not exist in the mapping at all. By default, ES|QL returns an error when you reference an unmapped field, but you can change this behavior using the SET unmapped_fields directive. Loading unmapped fields from _source with SET unmapped_fields="load" is slower than querying mapped fields, and filters or sorts on loaded fields can force a full scan. To learn more, refer to Unmapped fields.

ES|QL does not support configurations where the _source field is disabled.

One limitation of full-text search is that it is necessary to use the search function, like MATCH, in a WHERE command directly after the FROM source command, or close enough to it. Otherwise, the query will fail with a validation error.

For example, this query is valid:

FROM books
| WHERE MATCH(author, "Faulkner") AND MATCH(author, "Tolkien")
		

But this query will fail due to the STATS command:

FROM books
| STATS AVG(price) BY author
| WHERE MATCH(author, "Faulkner")
		

INLINE STATS is an exception: it can appear between FROM and the WHERE command without causing the query to fail. Unlike STATS, it appends the aggregated values as new columns and keeps every input row, so the search function can still use the index:

FROM books
| INLINE STATS max_year = MAX(year) BY publisher
| WHERE MATCH(author, "Faulkner") AND year == max_year
		

This applies to MATCH, MATCH_PHRASE, and the : operator. Other search functions, such as KQL and QSTR, are still not supported after INLINE STATS. A STATS command before the search function still causes the query to fail, even if an INLINE STATS comes after it.

MATCH can also target an expression rather than an indexed field, for example a column produced by EVAL or STATS. It then evaluates by scanning the column's values row by row instead of using the index.

Because such a search does not use the index, the restriction above does not apply to it: it can appear anywhere in the query, including after STATS, LIMIT and FORK. (In earlier versions it was restricted to the same positions as a search on an indexed field. Searching a mapped text field that FORK merged is the one exception, described below.) For example, this query is accepted:

FROM books
| SORT book_no
| LIMIT 10
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien")
		

The restriction is lifted per search function, not per WHERE command, so a search on an indexed field sharing the command still fails. (e.g. if content was an indexed field instead, the query would be rejected.)

MATCH_PHRASE supports targeting text and keyword expressions in the same way, with the same limitations.

When searching expressions, function named parameters (match query options) are supported on text expressions. As on an indexed field, the analyzer option applies to the query string only: how the expression's values are analyzed is declared where the column is created, through TO_TEXT's analyzer option, and the query analyzer defaults to that values analyzer (standard when none is declared). Analyzer names must name a registered analyzer (prebuilt or plugin-contributed), not a per-index custom analyzer. On other expression types options are not supported.

An indexed text field is sometimes searched as an expression rather than through the index, and its values are then analyzed with the values analyzer of the column rather than the one its mapping declares: standard, unless it uses TO_TEXT with its optional analyzer argument. This applies whenever the field cannot be searched through the index, which includes the column MV_EXPAND expanded and a field that is not mapped the same way across every index the query reads. Searching the field where it can still use the index uses the mapping's analyzer.

What FORK outputs is not index-backed either, but there the mapped-text case is rejected rather than answered with the substituted analyzer. Declare the analyzer on the merged column to search it, which is allowed because the merged column is no longer an indexed field, or search the field inside the FORK branches instead:

FROM books
| FORK (SORT book_no | LIMIT 5)
       (SORT book_no DESC | LIMIT 5)
| EVAL t = TO_TEXT(title, {"analyzer": "whitespace"})
| WHERE MATCH(t, "Tolkien")
		

This only applies to a column the merge actually makes non-indexed. Where no branch contains a pipeline breaker such as LIMIT or STATS, the search is pushed into the branches and still uses the index, so it is unaffected. FORK branches that declare different values analyzers for the same column are rejected too, since the merged column can only carry one of them.

FROM books
| MV_EXPAND author
| EVAL a = TO_TEXT(author, {"analyzer": "whitespace"})
| WHERE MATCH(a, "Tolkien")
		

Which column MV_EXPAND expanded therefore decides how the field is searched. Both of these expand author, which a book can have several of, but only the first searches the expanded column and so becomes a runtime search:

FROM books
| MV_EXPAND author
| WHERE MATCH(author, "Tolkien")
		

The second searches an indexed field instead, which is still subject to the restriction above and fails after MV_EXPAND:

FROM books
| MV_EXPAND author
| WHERE MATCH(title, "Tolkien")
		

When using METADATA _score, MATCH on an expression contributes to the relevance score: a row scores the boost option (1.0 by default) for each query term occurrence it matches (duplicate query terms each contribute separately), rather than BM25, as there are no index statistics for an expression. In earlier versions, MATCH on an expression does not contribute to the score.

A search on an expression contributes to _score only once it runs. Filtering on _score after the search sees its score:

FROM books METADATA _score
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien Rings")
| WHERE _score > 1.5
		

Within one WHERE, a condition on _score combined with AND sees the score from every search in that WHERE, wherever it appears. Here _score > 1.5 sees the score from both MATCH functions:

FROM books METADATA _score
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien Rings") AND _score > 1.5 AND MATCH(content, "English")
		

Anything that uses _score before the search runs sees the score from before it, which is 0.0 if no earlier search has scored. For example, a WHERE on _score before the expression is computed, which keeps no books here:

FROM books METADATA _score
| WHERE _score > 1.5
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien Rings")
		

A value computed from _score before or alongside the expression, so s is 0.0:

FROM books METADATA _score
| EVAL s = _score + 0.0, content = TO_TEXT(CONCAT(title, " ", description))
| WHERE MATCH(content, "Tolkien Rings")
		

A SORT and LIMIT before the search, which pick 10 books before any of them have been scored:

FROM books METADATA _score
| EVAL content = TO_TEXT(CONCAT(title, " ", description))
| SORT _score DESC
| LIMIT 10
| WHERE MATCH(content, "Tolkien Rings")
		

Lastly, note that any queries on text fields that do not explicitly use the full-text functions, MATCH, MATCH_PHRASE, QSTR or KQL, will behave as if the fields are actually keyword fields: they are case-sensitive and need to match the full string.

As discussed in more detail in Using ES|QL to query multiple indices, ES|QL can execute a single query across multiple indices, data streams, or aliases. However, there are some limitations to be aware of:

• All underlying indexes and shards must be active. Using admin commands or UI, it is possible to pause an index or shard, for example by disabling a frozen tier instance, but then any ES|QL query that includes that index or shard will fail, even if the query uses WHERE to filter out the results from the paused index. If you see an error of type search_phase_execution_exception, with the message Search rejected due to missing shards, you likely have an index or shard in UNASSIGNED state.
• The same field must have the same type across all indexes. If the same field is mapped to different types it is still possible to query the indexes, but the field must be explicitly converted to a single type.

Date math expressions work well when the leftmost expression is a datetime, for example:

now() + 1 year - 2hour + ...
		

But using parentheses or putting the datetime to the right is not always supported yet. For example, the following expressions fail:

1year + 2hour + now()
now() + (1year + 2hour)
		

Date math does not allow subtracting two datetimes, for example:

now() - 2023-10-26
		

While all three enrich policy types are supported, there are some limitations to be aware of:

• The geo_match enrich policy type only supports the intersects spatial relation.
• It is required that the match_field in the ENRICH command is of the correct type. For example, if the enrich policy is of type geo_match, the match_field in the ENRICH command must be of type geo_point or geo_shape. Likewise, a range enrich policy requires a match_field of type integer, long, date, or ip, depending on the type of the range field in the original enrich index.
• However, this constraint is relaxed for range policies when the match_field is of type KEYWORD. In this case the field values will be parsed during query execution, row by row. If any value fails to parse, the output values for that row will be set to null, an appropriate warning will be produced and the query will continue to execute.

The DISSECT command does not support reference keys.

The GROK command does not support configuring custom patterns, or multiple patterns. The GROK command is not subject to Grok watchdog settings.

ES|QL supports multivalued fields, but functions return null when applied to a multivalued field, unless documented otherwise. Work around this limitation by converting the field to single value with one of the multivalue functions.

CATEGORIZE grouping function is not currently supported.

Also, INLINE STATS cannot yet have an unbounded SORT before it. You must either move the SORT after it, or add a LIMIT before the SORT.

Subqueries and views are closely related, since both extend the FROM command with branched query plans. Their nesting and branching support varies by version, and each has additional limitations described in turn below.

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
		

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
		

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

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

Views reuse the same branching model as subqueries. The following limitations include the version-specific rules for nested branching and query compaction.

A role entry that grants read on a view name must not carry DLS or FLS restrictions. If it does, the query fails with a 403. DLS/FLS on the underlying indices is applied normally. Refer to view privileges.

Views may be nested up to depth 10.

Views can reference remote indices in cross-cluster search, but you can't query a view that is defined on a remote cluster.

Views are available in serverless and cross-project search, but with some limitations:

• You can define views in both origin and linked projects.
• CPS resolves index expressions against indices in every project that the expression targets, but it resolves views only in the origin project. Views in linked projects are invisible to the query, including through qualified expressions such as FROM linked-project:view.
• CPS uses the same resolution process for index expressions in the top-level query and inside view definitions.
• An unqualified index expression can match a view in the origin project and indices in linked projects. CPS returns results from both.

				PUT /_query/view/sales_by_region
					{
  "query": "FROM sales | STATS total = COUNT(*) BY region"
}
		

				POST /_query
					{
  "query": "FROM sales_by_region | KEEP region, total | LIMIT 1000",
  "filter": {
    "bool": {
      "should": [
        { "term": { "region": "eu" } },
        { "term": { "total": 3 } }
      ],
      "minimum_should_match": 2
    }
  }
}
		

The request filter could not be fully applied to view(s); the following Query DSL constructs are not supported and were skipped: [bool[minimum_should_match=2]] on view [sales_by_region]. Use a WHERE clause to filter rows from views instead
		

A view definition can read from index aliases and data streams. An alias cannot point to a view.

An Elasticsearch transform is not an ES|QL source. To query transform results from a view, use the transform's destination index as the source in the view definition.

A view definition can read from a dataset, which ES|QL Data Federation uses to query external data.

A wildcard in the view definition matches datasets only when the view definition sets wildcards_match_datasets to true.

Query parameters are not allowed in a stored view definition. A calling query can still use parameters in commands after FROM view_name, but those parameters are not substituted into the view body.

A view that starts with TS must also contain a STATS command after it. A view without STATS is rejected when you create or update it. This ensures the view returns plain tabular results. A PROMQL view must also use an aggregation function.

Query a time series view with FROM view_name. A view name cannot be used as a source in TS or PROMQL. An exact view name in one of those source commands is rejected, and a wildcard source expression skips matching views.

When you create or update a view, Elasticsearch validates its name, size, query syntax, and the settings that are allowed in a view definition.

For a time series view, Elasticsearch also validates that TS is followed by STATS.

Elasticsearch doesn't resolve or execute the query, and it doesn't check whether referenced indices, fields, datasets, or views exist, or whether nested views form a cycle. Creation doesn't return warnings for those problems. Elasticsearch reports them when you query the view.

Deleting a resource doesn't check whether a view depends on it. Deleting an index, data stream, alias, dataset, or nested view can therefore cause existing views to fail when queried.

• The filter bar interface is not enabled when Discover is in ES|QL mode. To filter data, use variable controls, filter buttons within the table and field list, or write a query that uses the WHERE command instead.
• Discover shows no more than 10,000 rows. This limit only applies to the number of rows that are retrieved by the query and displayed in Discover. Queries and aggregations run on the full data set.
• Discover shows no more than 50 columns. If a query returns more than 50 columns, Discover only shows the first 50.
• CSV export from Discover shows no more than 10,000 rows. This limit only applies to the number of rows that are retrieved by the query and displayed in Discover. Queries and aggregations run on the full data set.
• Querying many indices at once without any filters can cause an error in kibana which looks like [esql] > Unexpected error from Elasticsearch: The content length (536885793) is bigger than the maximum allowed string (536870888). The response from ES|QL is too long. Use DROP or KEEP to limit the number of fields returned.

Refer to Known issues for a list of known issues for ES|QL.