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.

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.

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.

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.

Possible values are:

• DEFAULT : Standard ESQL queries fail when referencing 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 where they are unmapped.

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.

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