ES|QL limitations

By default, an ES|QL query returns up to 1000 rows. You can increase the number of rows up to 10,000 using the LIMIT command. Queries do not return more than 10,000 rows, regardless of the LIMIT command’s value.

This limit only applies to the number of rows that are retrieved by the query. Queries and aggregations run on the full data set.

To overcome this limitation:

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

ES|QL currently supports the following field types:

ES|QL does not yet support the following field types:

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.

Some field types are not supported in all contexts:

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.

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

[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. ES|QL’s support for synthetic _source is currently experimental.

[preview] This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. ES|QL’s support for full-text search is currently in Technical Preview. 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. Another limitation is that any WHERE command containing a full-text search function cannot also use disjunctions (OR) unless all functions used in the OR clauses are full-text functions themselves.

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")

And this query will fail due to the disjunction:

FROM books
| WHERE MATCH(author, "Faulkner") OR author LIKE "Hemingway"

However this query will succeed because it uses full text functions on both OR clauses:

FROM books
| WHERE MATCH(author, "Faulkner") OR QSTR("author: Hemingway")

Note that, because of the way ES|QL treats text values, any queries on text fields that do not explicitly use the full-text functions, MATCH or QSTR, will behave as if the fields are actually keyword fields: they are case-sensitive and need to match the full string.

While ES|QL supports text fields, ES|QL does not treat these fields like the Search API does. ES|QL queries do not query or aggregate the analyzed string. Instead, an ES|QL query will try to get a text field’s subfield of the keyword family type and query/aggregate that. If it’s not possible to retrieve a keyword subfield, ES|QL will get the string from a document’s _source. If the _source cannot be retrieved, for example when using synthetic source, null is returned.

Once a text field is retrieved, if the query touches it in any way, for example passing it into a function, the type will be converted to keyword. In fact, functions that operate on both text and keyword fields will perform as if the text field was a keyword field all along.

For example, the following query will return a column greatest of type keyword no matter whether any or all of field1, field2, and field3 are of type text:

| FROM index
| EVAL greatest = GREATEST(field1, field2, field3)

Note that ES|QL’s retrieval of keyword subfields may have unexpected consequences. Other than when explicitly using the full-text functions, MATCH and QSTR, any ES|QL query on a text field is case-sensitive.

For example, after indexing a field of type text with the value Elasticsearch query language, the following WHERE clause does not match because the LIKE operator is case-sensitive:

| WHERE field LIKE "elasticsearch query language"

The following WHERE clause does not match either, because the LIKE operator tries to match the whole string:

| WHERE field LIKE "Elasticsearch"

As a workaround, use wildcards and regular expressions. For example:

| WHERE field RLIKE "[Ee]lasticsearch.*"

Furthermore, a subfield may have been mapped with a normalizer, which can transform the original string. Or it may have been mapped with ignore_above, which can truncate the string. None of these mapping operations are applied to an ES|QL query, which may lead to false positives or negatives.

To avoid these issues, a best practice is to be explicit about the field that you query, and query keyword sub-fields instead of text fields. Or consider using one of the full-text search functions.

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:

ES|QL does not support querying time series data streams (TSDS).

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 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.

ES|QL only supports the UTC timezone.