ES|QL SET directive

The SET directive can be used to specify query settings that modify the behavior of an ES|QL query.

SET setting_name = setting_value[, ..., settingN = valueN]; <query>

Multiple SET directives can be included in a single query, separated by semicolons. If the same setting is defined multiple times, the last definition takes precedence.

Enables query approximation if possible for the query. A boolean value false (default) disables query approximation and true enables it with default settings. Map values enable query approximation with custom settings.

Serverless Unavailable Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Unavailable This functionality is not available in Elastic Cloud Serverless. Stack Planned The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Planned We plan to add this functionality in a future Elastic Stack update. Subject to changes. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. The default itself is configurable. If a query does not specify a value, the esql.query.settings.approximation cluster setting supplies it. If that cluster setting is not configured either, query approximation is off. Enabling it cluster-wide requires an Enterprise license; without one the cluster default does not apply and queries run exactly.

Type: boolean map_param

Map entries:

• rows ([integer]): Number of sampled rows used for approximating the query. Must be at least 10,000. Null uses the system default.
• confidence_level ([double]): Confidence level of the computed confidence intervals. Default is 0.90. Null disables computing confidence intervals.

Approximate the sum using default settings.

SET approximation=true;
FROM many_numbers | STATS sum=SUM(sv)

Approximate the median based on 10,000 rows.

SET approximation={"rows":10000};
FROM many_numbers | STATS median=MEDIAN(sv)

When enabled, column metadata is added to the _query response as additional _meta properties. Defaults to false. Currently, only _meta.bucket is added for columns corresponding to the BUCKET function and contains bucket interval and unit for queries where it can be determined.

Serverless Unavailable Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Unavailable This functionality is not available in Elastic Cloud Serverless. Stack Planned The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Planned We plan to add this functionality in a future Elastic Stack update. Subject to changes. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. The default itself is configurable. If a query does not specify a value, the esql.query.settings.column_metadata cluster setting supplies it. If that cluster setting is not configured either, the value is false.

Type: boolean

Limits the scope of a cross-project search (CPS) to specific projects before query execution, based on a Lucene query expression evaluated against project tags. Excluded projects are not queried, which can reduce cost and latency.

Type: keyword

Route a query to a specific project by alias:

SET project_routing="_alias:my-project";
FROM logs*
| STATS COUNT(*)

When enabled, time-series aggregates are reduced separately in each index before results are combined. This uses less memory and can improve query performance, but results may be approximate for time buckets that overlap index rollover boundaries and for windowed aggregates whose windows reach across those boundaries. Defaults to false.

Type: boolean

The default timezone to be used in the query. Defaults to UTC, and overrides the time_zone request parameter. See timezones.

Serverless Unavailable Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Unavailable This functionality is not available in Elastic Cloud Serverless. Stack Planned The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Planned We plan to add this functionality in a future Elastic Stack update. Subject to changes. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. The default itself is configurable. If a query does not specify a timezone, the esql.query.settings.time_zone cluster setting supplies it. If that cluster setting is not configured either, the timezone is UTC.

Type: keyword

SET time_zone = "+05:00";
TS k8s
| WHERE @timestamp == "2024-05-10T00:04:49.000Z"
| STATS by @timestamp, bucket = TBUCKET(3 hours)
| SORT @timestamp
| LIMIT 2

Determines how unmapped fields are treated. For a conceptual overview and use cases, including performance considerations, refer to Unmapped fields.

An unmapped field is present in documents but not defined in the index mapping. Across the queried indices, a field is:

• fully unmapped if none of the queried indices map it.
• partially mapped (also called partially unmapped) if some of the queried indices map it and others don't. In the indices that don't map it, its values are null unless they are loaded with LOAD.

Possible values are:

• DEFAULT : Standard ESQL queries fail when referencing fully unmapped fields.
• NULLIFY : Treats referenced unmapped fields as null values. Fully unmapped fields that are never mentioned do not appear in the output.
• LOAD : Loads referenced fully unmapped fields from the stored _source with type keyword. Or nullifies them if absent from _source. Also loads partially mapped fields from _source in the indices where they are unmapped, even if the query doesn't reference them. Stack GA 9.5+ … The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available since 9.5 This functionality is generally available and ready for production usage. Preview in 9.4 This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.
• LOAD_ALL : Does everything LOAD does, and also returns every unmapped field that the query does not name, in a single _unmapped_fields column. That column holds, for each row, a JSON object of the row's unmapped _source fields. Refer to Load all unmapped fields. Stack Planned The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Planned We plan to add this functionality in a future Elastic Stack update. Subject to changes. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. Serverless Preview Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Preview This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Serverless interfaces and procedures might differ from classic Elastic Stack deployments.

PROMQL queries have their own specific semantics for unmapped fields.

Special notes about the LOAD and LOAD_ALL options:

• PROMQL is not supported with LOAD or LOAD_ALL.
• Referencing subfields of flattened parents is not supported.
• LOAD_ALL supports a subset of commands, and notably not TS. Other commands fail with an error that lists the supported ones. Stack Planned The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Planned We plan to add this functionality in a future Elastic Stack update. Subject to changes. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. Serverless Preview Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Preview This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Serverless interfaces and procedures might differ from classic Elastic Stack deployments.
• Full-text search functions are supported, although unmapped fields cannot be loaded without an explicit invocation of to_text. Stack 9.5+ The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available since 9.5 This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.
  • Full-text search functions are not supported anywhere in the query. Stack Preview 9.4 The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Preview in 9.4 This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.
• Partially unmapped non-keyword fields can be used in expressions. If the field is mapped to a single type and there's an available conversion from keyword to that type, the implicit conversion is applied. If there's no available conversion (for example text, aggregate_metric_double, or dense_vector), and an explicit one has not been provided by the user, values retain the mapped type but are null for rows from indices where the field is unmapped. Stack 9.5+ The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available since 9.5 This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.
  • Partially unmapped non-keyword fields must be referenced inside a cast or conversion function (e.g. ::TYPE or TO_TYPE), unless referenced in KEEP or DROP. Stack Preview 9.4 The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Preview in 9.4 This functionality is in technical preview and is ready for evaluation. Use with caution in production; it is not recommended for mission-critical workloads. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features. Specific Support terms apply. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.

Serverless Unavailable Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Unavailable This functionality is not available in Elastic Cloud Serverless. Stack Planned The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Planned We plan to add this functionality in a future Elastic Stack update. Subject to changes. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. The default itself is configurable. If a query does not specify a value, the esql.query.settings.unmapped_fields cluster setting supplies it. If that cluster setting is not configured either, the value is DEFAULT.

Type: keyword

Field unmapped_message is not mapped; it doesn't appear in the mapping of index partial_mapping_sample_data. It appears, however, in the stored _source of all documents in this index.

The NULLIFY option will treat this field as null.

SET unmapped_fields="nullify";
FROM partial_mapping_sample_data
| KEEP event_duration, unmapped_message
| SORT event_duration
| LIMIT 1

Field unmapped_message is not mapped; it doesn't appear in the mapping of index partial_mapping_sample_data. It appears, however, in the stored _source of all documents in this index.

The LOAD option will load this field from _source and treat it like a keyword type field.

SET unmapped_fields="load";
FROM partial_mapping_sample_data
| KEEP event_duration, unmapped_message
| SORT event_duration
| LIMIT 1

When enabled, a wildcard in FROM also matches registered datasets. Defaults to false, so a wildcard does not match a dataset and a dataset is reached by its exact name. Other abstractions a wildcard matches are unaffected.

The default itself is configurable. If a query does not specify a value, the esql.query.settings.wildcards_match_datasets cluster setting supplies it. If that cluster setting is not configured either, the value is false.

Type: boolean

When enabled, a wildcard in FROM also matches registered views. Defaults to false, so a wildcard does not match a view and a view is reached by its exact name. Other abstractions a wildcard matches are unaffected.

Serverless Unavailable Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Unavailable This functionality is not available in Elastic Cloud Serverless. The default itself is configurable. If a query does not specify a value, the esql.query.settings.wildcards_match_views cluster setting supplies it. If that cluster setting is not configured either, the value is false.

Type: boolean