ES|QL limitations
editES|QL limitations
editResult set size limit
editBy 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:
-
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 ... BY
command to aggregate data in the query.
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
Field types
editSupported types
editES|QL currently supports the following field types:
-
alias
-
boolean
-
date
-
double
(float
,half_float
,scaled_float
are represented asdouble
) -
ip
-
keyword
family includingkeyword
,constant_keyword
, andwildcard
-
int
(short
andbyte
are represented asint
) -
long
-
null
-
text
-
[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.
unsigned_long
-
version
-
Spatial types
-
geo_point
-
geo_shape
-
point
-
shape
-
Unsupported types
editES|QL does not yet support the following field types:
-
TSDB metrics
-
counter
-
position
-
aggregate_metric_double
-
-
Date/time
-
date_nanos
-
date_range
-
-
Other types
-
binary
-
completion
-
dense_vector
-
double_range
-
flattened
-
float_range
-
histogram
-
integer_range
-
ip_range
-
long_range
-
nested
-
rank_feature
-
rank_features
-
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.
Limitations on supported types
editSome field types are not supported in all contexts:
-
Spatial types are not supported in the SORT processing command. Specifying a column of one of these types as a sort parameter 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.
_source availability
editES|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.
Full-text search
edit
[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
).
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"
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.
text
fields behave like keyword
fields
editWhile 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.
Using ES|QL to query multiple indices
editAs 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 typesearch_phase_execution_exception
, with the messageSearch rejected due to missing shards
, you likely have an index or shard inUNASSIGNED
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.
Time series data streams are not supported
editES|QL does not support querying time series data streams (TSDS).
Date math limitations
editDate 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
Enrich limitations
editThe ES|QL ENRICH
command only supports enrich policies of type match
.
Furthermore, ENRICH
only supports enriching on a column of type keyword
.
Dissect limitations
editThe DISSECT
command does not support reference keys.
Grok limitations
editThe GROK
command does not support configuring custom
patterns, or multiple patterns. The GROK
command is not
subject to Grok watchdog settings.
Multivalue limitations
editES|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.
Timezone support
editES|QL only supports the UTC timezone.
Kibana limitations
edit-
The user interface to filter data is not enabled when Discover is in ES|QL
mode. To filter data, 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. UseDROP
orKEEP
to limit the number of fields returned.