--- title: ES|QL limitations description: 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. ES|QL currently supports... url: https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/limitations products: - Elasticsearch applies_to: - Elastic Cloud Serverless: Generally available - Elastic Stack: Generally available --- # ES|QL limitations ## Result set size limit 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`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/limit) command. For instance, ```esql FROM index | WHERE field = "value" ``` is equivalent to: ```esql 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`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/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`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/stats-by) 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: ```esql FROM index | WHERE field0 == "value" | LIMIT 20000 ``` and ```esql 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`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/ts) or [`PROMQL`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/promql) source commands (including requests to the [PromQL HTTP API](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/promql/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](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/elasticsearch/rest-apis/paginate-search-results). | Functionality | Search | ES|QL | |-------------------------------------------------------------|-------------------------|------------------------------------------------------| | Results returned by default | 10 | 1,000 | | Results returned by default (time-series aggregations) | 10 | 10,000 | | Default upper limit | 10,000 | 10,000 | | Default upper limit (time-series aggregations) | 10,000 | 10,000,000 | | Specify number of results | `size` | `LIMIT` | | Change default number of results | n/a | esql.query.result_truncation_default_size | | Change default number of results (time-series aggregations) | n/a | esql.query.timeseries_result_truncation_default_size | | Change default upper limit | index-max-result-window | esql.query.result_truncation_max_size | | Change default upper limit (time-series aggregations) | index-max-result-window | esql.query.timeseries_result_truncation_max_size | ## Field types ### Supported types ES|QL currently supports the following [field types](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/elasticsearch/mapping-reference/field-data-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](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/elasticsearch/mapping-reference/range) Elastic Stack: Preview since 9.5 Elastic Cloud Serverless: Preview - `double` (`float`, `half_float`, `scaled_float` are represented as `double`) - `double_range` [range family](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/elasticsearch/mapping-reference/range) Elastic Stack: Planned Elastic Cloud Serverless: Preview - `dense_vector` Elastic Stack: Preview since 9.2 Elastic Cloud Serverless: Preview - `flattened` Elastic Stack: Preview since 9.5 - `ip` - `keyword` [family](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/elasticsearch/mapping-reference/keyword) including `keyword`, `constant_keyword`, and `wildcard` - `int` (`short` and `byte` are represented as `int`) - `long` - `null` - `text` [family](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/elasticsearch/mapping-reference/text) including `text`, `semantic_text` and `match_only_text` - `unsigned_long` Elastic Stack: Preview Elastic Cloud Serverless: Preview - `version` - Spatial types - `geo_point` - `geo_shape` - `point` - `shape` - TSDB metrics Elastic Stack: Preview since 9.2 Elastic Cloud Serverless: Preview - `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`. Elastic Stack: Preview since 9.4 Elastic Cloud Serverless: Preview - [`exponential_histogram`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-histogram-fields) Elastic Stack: Generally available since 9.4, Elastic Stack: Preview in 9.3 - [`tdigest`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-histogram-fields) Elastic Stack: Generally available since 9.4, Elastic Stack: Preview in 9.3 ### Unsupported types ES|QL does not support certain field types. If the limitation only applies to specific product versions, it is indicated in the following list: - Elastic Stack: Generally available from 9.0 to 9.1 `dense_vector` - Elastic Stack: Generally available from 9.0 to 9.1 TSDB metrics - `counter` - `gauge` - `aggregate_metric_double` - Other types - `binary` - `completion` - `float_range` - `histogram` (can be queried via [casting to `tdigest` or `exponential_histogram`](/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-histogram-fields#cast-between-histogram-types)) - `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](/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-rest#esql-rest-column-metadata). ### Limitations on supported types Some [field types](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/elasticsearch/mapping-reference/field-data-types) are not supported in all contexts: - Spatial types are not supported in the [SORT](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/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](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-multi-index), 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](/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-multi-index#esql-multi-index-union-types). #### Spatial precision 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](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/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: ```esql 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: ```esql 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. #### Partial support in 9.2.0 - Elastic Stack: Preview since 9.2 The following types are only partially supported on 9.2.0. This is fixed in 9.2.1: - `dense_vector`: The [`KNN` function](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/dense-vector-functions/knn) and the [`TO_DENSE_VECTOR` function](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/type-conversion-functions/to_dense_vector) 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](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/type-conversion-functions/to_aggregate_metric_double) 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. 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)` ## Runtime fields ES|QL respects [runtime fields](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/4572/manage-data/data-store/mapping/runtime-fields) defined in the index mapping and treats them like regular mapped fields. Use the [`EVAL`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/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`](/elastic/docs-builder/docs/4572/reference/query-languages/esql/directives/set#esql-unmapped_fields) directive. Loading unmapped fields from [`_source`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/elasticsearch/mapping-reference/mapping-source-field) 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](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-unmapped-fields). ## _source availability ES|QL does not support configurations where the [`_source`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/elasticsearch/mapping-reference/mapping-source-field) field is [disabled](/elastic/docs-builder/docs/4572/reference/elasticsearch/mapping-reference/mapping-source-field#disable-source-field). ## Full-text search ### Position restriction on indexed fields One limitation of [full-text search](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/search-functions) is that it is necessary to use the search function, like [`MATCH`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/search-functions/match), in a [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/where) command directly after the [`FROM`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/from) source command, or close enough to it. Otherwise, the query will fail with a validation error. For example, this query is valid: ```esql FROM books | WHERE MATCH(author, "Faulkner") AND MATCH(author, "Tolkien") ``` But this query will fail due to the [STATS](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/stats-by) command: ```esql FROM books | STATS AVG(price) BY author | WHERE MATCH(author, "Faulkner") ``` Elastic Stack: Planned Elastic Cloud Serverless: Generally available [`INLINE STATS`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/inlinestats-by) 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: ```esql FROM books | INLINE STATS max_year = MAX(year) BY publisher | WHERE MATCH(author, "Faulkner") AND year == max_year ``` This applies to `MATCH`, [`MATCH_PHRASE`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/search-functions/match_phrase), and the `:` operator. Other search functions, such as [`KQL`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/search-functions/kql) and [`QSTR`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/search-functions/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. ### Runtime search on expressions Elastic Stack: Preview in 9.5 Elastic Cloud Serverless: Generally available `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. Elastic Stack: Planned Elastic Cloud Serverless: Generally available 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: ```esql 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.) Elastic Stack: Planned Elastic Cloud Serverless: Generally available [`MATCH_PHRASE`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/search-functions/match_phrase) supports targeting `text` and `keyword` expressions in the same way, with the same limitations. Elastic Stack: Planned Elastic Cloud Serverless: Generally available When searching expressions, [function named parameters](/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-syntax#esql-function-named-params) (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`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/type-conversion-functions/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. ### Analyzer used for a runtime search Elastic Stack: Planned Elastic Cloud Serverless: Generally available 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`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/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. Elastic Stack: Planned Elastic Cloud Serverless: Generally available What [`FORK`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/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: ```esql 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. `MV_EXPAND`, and a field that is not mapped the same way across every index the query reads, substitute the analyzer without reporting it. A field whose mapping declares an analyzer other than `standard` is then searched differently depending on where in the query it is searched, and the query succeeds while quietly matching a different set of documents. Either search the field before the command that turns it into an expression, or declare the analyzer again on `TO_TEXT` afterwards, which is allowed once the column is no longer an indexed field: ```esql 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: ```esql 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`: ```esql FROM books | MV_EXPAND author | WHERE MATCH(title, "Tolkien") ``` ### Scoring for a runtime search Elastic Stack: Planned Elastic Cloud Serverless: Generally available 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: ```esql 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: ```esql 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: ```esql 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: ```esql 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: ```esql FROM books METADATA _score | EVAL content = TO_TEXT(CONCAT(title, " ", description)) | SORT _score DESC | LIMIT 10 | WHERE MATCH(content, "Tolkien Rings") ``` ### Text fields without a search function Lastly, note that any queries on `text` fields that do not explicitly use the full-text functions, [`MATCH`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/search-functions/match), [`MATCH_PHRASE`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/search-functions/match_phrase), [`QSTR`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/search-functions/qstr) or [`KQL`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/search-functions/kql), will behave as if the fields are actually `keyword` fields: they are case-sensitive and need to match the full string. ## Using ES|QL to query multiple indices As discussed in more detail in [Using ES|QL to query multiple indices](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-multi-index), 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`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/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](/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-multi-index#esql-multi-index-union-types). ## Time series data streams Time series data streams (TSDS) are supported in technical preview. ES|QL does not support querying time series data streams (TSDS). ## Date math limitations Date math expressions work well when the leftmost expression is a datetime, for example: ```txt 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: ```txt 1year + 2hour + now() now() + (1year + 2hour) ``` Date math does not allow subtracting two datetimes, for example: ```txt now() - 2023-10-26 ``` ## Enrich limitations 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 The `DISSECT` command does not support reference keys. ## Grok limitations The `GROK` command does not support configuring [custom patterns](/elastic/docs-builder/docs/4572/reference/ingest-processor/grok-processor#custom-patterns), or [multiple patterns](/elastic/docs-builder/docs/4572/reference/ingest-processor/grok-processor#trace-match). The `GROK` command is not subject to [Grok watchdog settings](/elastic/docs-builder/docs/4572/reference/ingest-processor/grok-processor#grok-watchdog). ## Multivalue limitations ES|QL [supports multivalued fields](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-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](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/mv-functions). ## INLINE STATS limitations [`CATEGORIZE`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/functions-operators/grouping-functions/categorize) grouping function is not currently supported. Also, [`INLINE STATS`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/inlinestats-by) cannot yet have an unbounded [`SORT`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/sort) before it. You must either move the SORT after it, or add a [`LIMIT`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/limit) before the [`SORT`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/sort). ## Subquery and view limitations [Subqueries](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-subquery) and [views](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-views) are closely related, since both extend the [`FROM`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/from) command with branched query plans. Their nesting and branching support varies by version, and each has additional limitations described in turn below. ### Subquery limitations #### Nested FROM subqueries A `FROM` subquery can contain further `FROM` subqueries. Nested branches are subject to the query-wide branch and command limits described in [How views execute](/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-views#how-views-execute).For example, each outer subquery in this query contains further `FROM` subqueries: ```esql 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 ``` A `FROM` subquery cannot contain another `FROM` subquery. Only one level of `FROM` nesting is allowed. A `FROM` subquery can still contain [`IN` subqueries](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-in-subquery) in its `WHERE` commands, `EVAL` commands, and `STATS` or `INLINE STATS` per-aggregate `WHERE` filters.Use only a single level of subqueries: ```esql 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 ``` #### FORK with FROM subqueries [`FORK`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/fork) can be used inside a `FROM` subquery or after a `FROM` that contains subqueries. Two user-written `FORK` commands still can't occur along the same uninterrupted pipeline.For example, `FORK` can be used inside a subquery: ```esql FROM (FROM employees | FORK (WHERE emp_no > 10090) (WHERE emp_no < 10010)) | KEEP emp_no, languages ``` It can also be used after a `FROM` with subqueries: ```esql FROM employees, (FROM sample_data | WHERE client_ip == "172.21.3.15") | FORK (WHERE emp_no > 10090) (WHERE client_ip IS NOT NULL) ``` `FORK` can't be used inside a `FROM` subquery or after a `FROM` that contains subqueries. ### View limitations [Views](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-views) reuse the same branching model as subqueries. The following limitations include the version-specific rules for nested branching and [query compaction](/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-views#query-compaction). #### Document- and field-level security 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](/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-views#esql-views-privileges). #### Branching inside views Views may be nested up to depth 10. View definitions can contain branched query plans created by `FROM` subqueries, nested views, and [`FORK`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/fork). These branches can be nested, subject to the branch and command limits described in [How views execute](/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-views#how-views-execute). Branching and nesting are allowed in combination only when there is no more than one branch point. A view can contain subqueries only when the resulting plan can be flattened through [query compaction](/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-views#query-compaction). #### Cross-cluster and serverless Views can reference remote indices in [cross-cluster search](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-cross-clusters), but you can't query a view that is defined on a remote cluster. Remote views are invisible to source resolution. An exact reference such as `FROM cluster:view` returns an `Unknown index` error. A query fails if its source expression matches a remote view. Views are available in serverless and [cross-project search](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-cross-serverless-projects), 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. #### Query DSL filters A Query DSL filter on the request applies to the output of a view, after the commands in the view definition. If Elasticsearch can't translate a filter clause to ES|QL, it omits that clause for the view and returns a warning. Check the response warnings because omitting a clause can produce more results than the complete filter would.For example, create a view that counts sales by region: ```json { "query": "FROM sales | STATS total = COUNT(*) BY region" } ``` The following request requires both `should` clauses to match: ```json { "query": "FROM sales_by_region | KEEP region, total | LIMIT 1000", "filter": { "bool": { "should": [ { "term": { "region": "eu" } }, { "term": { "total": 3 } } ], "minimum_should_match": 2 } } } ``` ES|QL can't translate a `minimum_should_match` value greater than `1`. It skips the `should` group for the view, which can return more rows than the complete filter would, and returns this warning: ```text 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 Query DSL filter on the request applies to the source indices in the view definition, not to the view output. #### Aliases, data streams, and transform results A view definition can read from index aliases and data streams. An alias cannot point to a view. An [Elasticsearch transform](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/4572/explore-analyze/transforms) 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. #### Data Federation datasets - Elastic Cloud Serverless: Unavailable - Elastic Stack: Experimental in 9.5 A view definition can read from a [dataset](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-data-federation-datasets), which [ES|QL Data Federation](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/esql-data-federation) uses to query external data. Elastic Stack: Planned A wildcard in the view definition matches datasets only when the view definition sets `wildcards_match_datasets` to `true`. #### Query settings Only the following settings are supported in a view definition: | Setting | Supported values | |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------| | [`unmapped_fields`](/elastic/docs-builder/docs/4572/reference/query-languages/esql/directives/set#esql-unmapped_fields) | `DEFAULT`, `NULLIFY`, and `LOAD` | | [`wildcards_match_views`](/elastic/docs-builder/docs/4572/reference/query-languages/esql/directives/set#esql-wildcards_match_views) | `true` and `false` | | [`wildcards_match_datasets`](/elastic/docs-builder/docs/4572/reference/query-languages/esql/directives/set#esql-wildcards_match_datasets) Elastic Stack: Planned | `true` and `false` | Elastic Cloud Serverless: Preview, Elastic Stack: Planned `LOAD_ALL` is not supported in a view definition.All other settings, including `time_zone`, are rejected in a view definition. A view definition can't contain a `SET` directive. ##### Settings across view boundaries The three settings supported in view definitions are scoped at every view boundary. Values from the calling query, request body, or an enclosing view don't enter the view. The cluster setting is used to set the default throughout the query, both inside and outside views. A value in a view definition overrides that default for its body, but doesn't enter a nested view. If neither is configured, the built-in default applies.Other query settings are rejected in a view definition, but aren't scoped to individual views. Their effective value for the calling query can apply while evaluating a view. For example, `time_zone` can apply inside a view.At a view boundary: - `unmapped_fields`: Fields loaded by an outer query are `null` for rows from a view unless the view produces those fields itself. If an outer setting would have loaded fields that the view doesn't produce, the response warning identifies the view and the fields that are `null`. - For each supported wildcard setting, exact names work regardless of the setting. A wildcard matches the corresponding target type only when the view definition sets the setting to `true`. If a `true` value from outside the view stops at the boundary and the wildcard would otherwise have matched targets, the response returns a warning that identifies the view and the missed matches. The effective query settings apply throughout the query, including inside view bodies. #### Query parameters 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. #### Time series data in views - Elastic Cloud Serverless: Generally available - Elastic Stack: Planned A view that starts with [`TS`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/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. #### Validation and dependencies When you create or update a view, Elasticsearch validates its name, size, query syntax, and the settings that are allowed in a view definition. Elastic Cloud Serverless: Generally available, Elastic Stack: Planned 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. ## Kibana limitations - The filter bar interface is not enabled when Discover is in ES|QL mode. To filter data, use [variable controls](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/4572/explore-analyze/discover/esql-variable-controls#add-variable-control), filter buttons within the table and field list, or write a query that uses the [`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/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`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/drop) or [`KEEP`](https://www.elastic.co/elastic/docs-builder/docs/4572/reference/query-languages/esql/commands/keep) to limit the number of fields returned. ## Known issues Refer to [Known issues](https://www.elastic.co/elastic/docs-builder/docs/4572/release-notes/elasticsearch/known-issues) for a list of known issues for ES|QL.