IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.
« ES|QL task management ES|QL examples »
Elastic Docs ›Elasticsearch Guide [8.17] ›ES|QL

ES|QL limitations

edit
IMPORTANT: This documentation is no longer updated. Refer to Elastic's version policy and the latest documentation.

ES|QL limitations

edit

Result set size limit

edit

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

However, doing so involves trade-offs. A larger result-set involves a higher memory pressure and increased processing times; the internode traffic within and across clusters can also increase.

These limitations are similar to those enforced by the Paginate search results.

Functionality Search ES|QL

Results returned by default

10

1.000

Default upper limit

10,000

10,000

Specify number of results

size

LIMIT

Change default number of results

n/a

esql.query.result_truncation_default_size

Change default upper limit

index-max-result-window

esql.query.result_truncation_max_size

Field types

edit

Supported types

edit

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
  • double (float, half_float, scaled_float are represented as double)
  • ip
  • keyword family including keyword, constant_keyword, and wildcard
  • int (short and byte are represented as int)
  • 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

edit

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

  • TSDB metrics

    • counter
    • position
    • aggregate_metric_double

  • Date/time

    • 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

edit

Some 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

edit

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.

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

Because of the way ES|QL treats text values, queries on text fields are like queries on keyword fields: they are case-sensitive and need to match the full string.

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

text fields behave like keyword fields

edit

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.

Note that ES|QL’s retrieval of keyword subfields may have unexpected consequences. An ES|QL query on a text field is case-sensitive. 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.

Time series data streams are not supported

edit

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

Date math limitations

edit

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

Enrich limitations

edit

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.

Dissect limitations

edit

The DISSECT command does not support reference keys.

Grok limitations

edit

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

Multivalue limitations

edit

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.

Timezone support

edit

ES|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. Use DROP or KEEP to limit the number of fields returned.

Known issues

edit

A bug in the ES|QL STATS command may yield incorrect results. The bug only happens in very specific cases that follow this pattern: STATS ... BY keyword1, keyword2, i.e. the command must have exactly two grouping fields, both keywords, where the first field has high cardinality (more than 65k distinct values).

The bug is described in detail in [this issue](https://github.com/elastic/elasticsearch/issues/130644). The problem was introduced in 8.16.0 and [fixed](https://github.com/elastic/elasticsearch/pull/130705) in 8.17.9, 8.18.7.

Possible workarounds include: * switching the order of the grouping keys (eg. STATS ... BY keyword2, keyword1, if the keyword2 has a lower cardinality) * reducing the grouping key cardinality, by filtering out values before STATS

« ES|QL task management ES|QL examples »