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)
		

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, while other query types (e.g. PromQL) may treat them differently.
• 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.

Special notes about the LOAD option:

• PromQL, 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