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.

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.

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(*)
		

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

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.

PROMQL queries have their own specific semantics for unmapped fields.

Special notes about the LOAD option:

• PROMQL is not supported with LOAD.
• Referencing subfields of flattened parents is not supported.
• Full-text search functions are supported, although unmapped fields cannot be loaded without an explicit invocation of to_text.
  • Full-text search functions are not supported anywhere in the query.
• 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.
  • 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.

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.

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