ES|QL SET directive

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

Syntax

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.

Allowed settings

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.

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)
		

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.

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. Possible values are:

• DEFAULT : Standard ESQL queries fail when referencing unmapped fields.
• NULLIFY : Treats unmapped fields as null values.
• LOAD : Loads unmapped fields from the stored _source with type keyword. Or nullifies them if absent from _source.

An unmapped field is a field referenced in a query that does not exist in the mapping of the index being queried. When querying multiple indices, a field is considered partially unmapped if it exists in the mapping of some indices but not others.

PROMQL queries have their own specific semantics for unmapped fields.

Special notes about the LOAD option:

• FORK, LOOKUP JOIN, subqueries, views, and full-text search functions are not yet supported anywhere in the query.
• Referencing subfields of flattened parents is not supported.
• Referencing partially unmapped non-keyword fields must be inside a cast or a conversion function (e.g. ::TYPE or TO_TYPE), unless referenced in a KEEP or DROP.

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