---
title: ES|QL SET directive
description: 
url: https://www.elastic.co/docs/reference/query-languages/esql/directives/set
products:
  - Elasticsearch
---

# ES|QL SET directive
<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available since 9.4
  - Elastic Stack: Preview in 9.3
</applies-to>

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

## Syntax

```esql
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


### `approximation`

<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available since 9.5
  - Elastic Stack: Preview in 9.4
</applies-to>

Enables [query approximation](https://www.elastic.co/docs/reference/query-languages/esql/esql-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.
<applies-to>Elastic Cloud Serverless: Unavailable, Elastic Stack: Planned</applies-to> 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.


#### Example

Approximate the sum using default settings.
```esql
SET approximation=true;
FROM many_numbers | STATS sum=SUM(sv)
```


| sum:long | _approximation_confidence_interval(sum):long | _approximation_certified(sum):boolean |
|----------|----------------------------------------------|---------------------------------------|
| 42284250 | [41622402, 43016433]                         | true                                  |


#### Example

Approximate the median based on 10,000 rows.
```esql
SET approximation={"rows":10000};
FROM many_numbers | STATS median=MEDIAN(sv)
```


| median:double | _approximation_confidence_interval(median):double | _approximation_certified(median):boolean |
|---------------|---------------------------------------------------|------------------------------------------|
| 356.0         | [353.73, 358.79]                                  | true                                     |


### `column_metadata`

<applies-to>
  - Elastic Cloud Serverless: Preview
  - Elastic Stack: Preview since 9.5
</applies-to>

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.
<applies-to>Elastic Cloud Serverless: Unavailable, Elastic Stack: Planned</applies-to> 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`

### `project_routing`

<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Unavailable
</applies-to>

Limits the scope of a [cross-project search (CPS)](https://www.elastic.co/docs/reference/query-languages/esql/esql-cross-serverless-projects) to specific projects before query execution, based on a [Lucene query expression](https://docs-demo.elastic.dev/docs/explore-analyze/cross-project-search/cross-project-search-project-routing) evaluated against project tags. Excluded projects are not queried, which can reduce cost and latency.
**Type**: `keyword`

#### Example

Route a query to a specific project by alias:
```esql
SET project_routing="_alias:my-project";
FROM logs*
| STATS COUNT(*)
```


### `time_series_reduce_per_index`

<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available
</applies-to>

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`

### `time_zone`

<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available since 9.4
</applies-to>

The default timezone to be used in the query. Defaults to UTC, and overrides the `time_zone` request parameter. See [timezones](/docs/reference/query-languages/esql/esql-rest#esql-timezones).
<applies-to>Elastic Cloud Serverless: Unavailable, Elastic Stack: Planned</applies-to> 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`

#### Example

```esql
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
```


### `unmapped_fields`

<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available since 9.5
  - Elastic Stack: Generally available
</applies-to>

Determines how unmapped fields are treated.
For a conceptual overview and use cases, including performance considerations, refer to
[Unmapped fields](https://www.elastic.co/docs/reference/query-languages/esql/esql-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`](https://www.elastic.co/docs/reference/elasticsearch/mapping-reference/mapping-source-field) 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.
  <applies-to>Elastic Stack: Generally available since 9.5, Elastic Stack: Preview in 9.4</applies-to>
- `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](/docs/reference/query-languages/esql/esql-unmapped-fields#esql-unmapped-fields-load-all).
  <applies-to>Elastic Stack: Planned</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>

[`PROMQL`](https://www.elastic.co/docs/reference/query-languages/esql/commands/promql) queries have their own specific semantics for unmapped fields.
Special notes about the `LOAD` and `LOAD_ALL` options:
- [`PROMQL`](https://www.elastic.co/docs/reference/query-languages/esql/commands/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`](https://www.elastic.co/docs/reference/query-languages/esql/commands/ts).
  Other commands fail with an error that lists the supported ones.
  <applies-to>Elastic Stack: Planned</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
- [Full-text search functions](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/search-functions) are supported,
  although unmapped fields cannot be loaded without an explicit invocation of `to_text`.
  <applies-to>Elastic Stack: Generally available since 9.5</applies-to>
  - Full-text search functions are not supported anywhere in the query. <applies-to>Elastic Stack: Preview in 9.4</applies-to>
- 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.
  <applies-to>Elastic Stack: Generally available since 9.5</applies-to>
  - 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`. <applies-to>Elastic Stack: Preview in 9.4</applies-to>

<applies-to>Elastic Cloud Serverless: Unavailable, Elastic Stack: Planned</applies-to>
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`

#### Example

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`.
```esql
SET unmapped_fields="nullify";
FROM partial_mapping_sample_data
| KEEP event_duration, unmapped_message
| SORT event_duration
| LIMIT 1
```


| event_duration:long | unmapped_message:null |
|---------------------|-----------------------|
| 725447              | null                  |


#### Example

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.
```esql
SET unmapped_fields="load";
FROM partial_mapping_sample_data
| KEEP event_duration, unmapped_message
| SORT event_duration
| LIMIT 1
```


| event_duration:long | unmapped_message:keyword |
|---------------------|--------------------------|
| 725447              | Disconnection error      |


### `wildcards_match_datasets`

<applies-to>
  - Elastic Cloud Serverless: Unavailable
  - Elastic Stack: Planned
</applies-to>

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`

### `wildcards_match_views`

<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Planned
</applies-to>

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.
<applies-to>Elastic Cloud Serverless: Unavailable</applies-to> 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`