references/dsl-to-esql-migration.md
# Query DSL to ES|QL Migration Guide
This guide helps you migrate from Elasticsearch Query DSL (JSON-based queries) to ES|QL (piped query language).
## Table of Contents
- [Overview: Key Differences](#overview-key-differences)
- [Basic Query Structure](#basic-query-structure)
- [Match All Query](#match-all-query)
- [Term Query (Exact Match)](#term-query-exact-match)
- [Match Query (Full-Text Search)](#match-query-full-text-search)
- [Match Phrase Query](#match-phrase-query)
- [Multi-Match Query](#multi-match-query)
- [Query String Query](#query-string-query)
- [Range Query](#range-query)
- [Bool Query](#bool-query)
- [Exists Query](#exists-query)
- [Wildcard / Prefix Query](#wildcard--prefix-query)
- [Regexp Query](#regexp-query)
- [Aggregations](#aggregations)
- [Sorting](#sorting)
- [Field Selection (\_source)](#field-selection-_source)
- [Pagination](#pagination)
- [Script Fields](#script-fields)
- [LOOKUP JOIN (Replaces Enrichment Patterns)](#lookup-join-replaces-enrichment-patterns)
- [Filters Aggregation (Per-Aggregation WHERE)](#filters-aggregation-per-aggregation-where)
- [Pipeline Aggregations (Chained STATS)](#pipeline-aggregations-chained-stats)
- [Highlighting](#highlighting)
- [ES|QL Limitations (vs Query DSL)](#esql-limitations-vs-query-dsl)
- [Migration Checklist](#migration-checklist)
- [Performance Considerations](#performance-considerations)
- [Quick Reference Table](#quick-reference-table)
## Overview: Key Differences
| Aspect | Query DSL | ES\|QL |
| ---------------- | ----------------------- | ------------------------------- |
| Format | JSON | Piped text |
| Execution | Translated to Lucene | Native execution engine |
| Default results | 10 | 1,000 |
| Max results | 10,000 (configurable) | 10,000 (configurable) |
| Aggregations | Nested JSON structure | `STATS ... BY` command |
| Full-text search | `match`, `query_string` | `MATCH()`, `QSTR()`, `KQL()` |
| Scoring | Automatic with queries | Explicit with `METADATA _score` |
### When to Use ES|QL vs Query DSL
**Use ES|QL for:**
- Log exploration and ad-hoc analysis
- Time-series data analysis
- Simple to moderate aggregations
- Data transformation pipelines
- Interactive troubleshooting
**Use Query DSL for:**
- Complex nested aggregations
- Advanced scoring and boosting
- Nested/parent-child document queries
- Features not yet in ES|QL (see Limitations)
---
## Basic Query Structure
### Query DSL
```json
POST /my-index/_search
{
"query": { ... },
"aggs": { ... },
"sort": [ ... ],
"size": 100,
"_source": ["field1", "field2"]
}
```
### ES|QL
```esql
FROM my-index
| WHERE <conditions>
| STATS <aggregations> BY <groupings>
| SORT <field> DESC
| KEEP field1, field2
| LIMIT 100
```
---
## Match All Query
### Query DSL
```json
{
"query": {
"match_all": {}
},
"size": 100
}
```
### ES|QL
```esql
FROM my-index
| LIMIT 100
```
---
## Term Query (Exact Match)
### Query DSL
```json
{
"query": {
"term": {
"status": "published"
}
}
}
```
### ES|QL
```esql
FROM my-index
| WHERE status == "published"
```
### Multiple Terms (terms query)
#### Query DSL
```json
{
"query": {
"terms": {
"status": ["published", "draft"]
}
}
}
```
#### ES|QL
```esql
FROM my-index
| WHERE status IN ("published", "draft")
```
---
## Match Query (Full-Text Search)
### Query DSL
```json
{
"query": {
"match": {
"title": "elasticsearch guide"
}
}
}
```
### ES|QL (8.17+)
```esql
FROM my-index
| WHERE MATCH(title, "elasticsearch guide")
```
Or using the match operator:
```esql
FROM my-index
| WHERE title : "elasticsearch guide"
```
### With Relevance Scoring
```esql
FROM my-index METADATA _score
| WHERE MATCH(title, "elasticsearch guide")
| SORT _score DESC
| LIMIT 10
```
---
## Match Phrase Query
### Query DSL
```json
{
"query": {
"match_phrase": {
"title": "quick brown fox"
}
}
}
```
### ES|QL (8.19+)
```esql
FROM my-index
| WHERE MATCH_PHRASE(title, "quick brown fox")
```
---
## Multi-Match Query
### Query DSL
```json
{
"query": {
"multi_match": {
"query": "elasticsearch",
"fields": ["title", "content", "tags"]
}
}
}
```
### ES|QL
```esql
FROM my-index
| WHERE MATCH(title, "elasticsearch")
OR MATCH(content, "elasticsearch")
OR MATCH(tags, "elasticsearch")
```
Or use QSTR for more flexibility:
```esql
FROM my-index
| WHERE QSTR("title:elasticsearch OR content:elasticsearch OR tags:elasticsearch")
```
---
## Query String Query
### Query DSL
```json
{
"query": {
"query_string": {
"query": "status:active AND (type:blog OR type:article)"
}
}
}
```
### ES|QL
```esql
FROM my-index
| WHERE QSTR("status:active AND (type:blog OR type:article)")
```
---
## Range Query
### Query DSL
```json
{
"query": {
"range": {
"price": {
"gte": 10,
"lte": 100
}
}
}
}
```
### ES|QL
```esql
FROM my-index
| WHERE price >= 10 AND price <= 100
```
### Date Range
#### Query DSL
```json
{
"query": {
"range": {
"@timestamp": {
"gte": "now-24h",
"lte": "now"
}
}
}
}
```
#### ES|QL
```esql
FROM my-index
| WHERE @timestamp >= NOW() - 24 hours AND @timestamp <= NOW()
```
Or simply:
```esql
FROM my-index
| WHERE @timestamp > NOW() - 24 hours
```
---
## Bool Query
The bool query is one of the most complex DSL structures to migrate.
### Query DSL
```json
{
"query": {
"bool": {
"must": [{ "match": { "title": "elasticsearch" } }],
"filter": [{ "term": { "status": "published" } }, { "range": { "date": { "gte": "2024-01-01" } } }],
"should": [{ "term": { "featured": true } }],
"must_not": [{ "term": { "draft": true } }]
}
}
}
```
### ES|QL
**Note:** ES|QL handles `must`, `filter`, and `must_not` directly with WHERE conditions. The `should` clause (optional
boosting) has no direct equivalent -- ES|QL cannot boost scores conditionally.
```esql
FROM my-index METADATA _score
| WHERE MATCH(title, "elasticsearch") // must
AND status == "published" // filter
AND date >= "2024-01-01" // filter
AND (draft != true OR draft IS NULL) // must_not
| EVAL featured_boost = CASE(featured == true, 100.0, 0.0)
| EVAL combined_score = _score + featured_boost // approximate should boost
| SORT combined_score DESC
```
> **Three-valued logic warning:** `draft != true` alone excludes rows where `draft` is NULL. In Query DSL,
> `must_not: { term: { draft: true } }` keeps documents where `draft` is missing. To match that behavior in ES|QL, add
> `OR draft IS NULL`.
>
> **Should clause:** ES|QL cannot natively replicate DSL `should` boosting. The `EVAL` approach above is a rough
> approximation. If precise relevance scoring is critical, consider using Query DSL instead.
---
## Exists Query
### Query DSL
```json
{
"query": {
"exists": {
"field": "user"
}
}
}
```
### ES|QL
```esql
FROM my-index
| WHERE user IS NOT NULL
```
### Does Not Exist
#### Query DSL
```json
{
"query": {
"bool": {
"must_not": {
"exists": { "field": "user" }
}
}
}
}
```
#### ES|QL
```esql
FROM my-index
| WHERE user IS NULL
```
---
## Wildcard / Prefix Query
### Query DSL
```json
{
"query": {
"wildcard": {
"name": "john*"
}
}
}
```
### ES|QL
```esql
FROM my-index
| WHERE name LIKE "john*"
```
Or using STARTS_WITH:
```esql
FROM my-index
| WHERE STARTS_WITH(name, "john")
```
---
## Regexp Query
### Query DSL
```json
{
"query": {
"regexp": {
"name": "joh?n.*"
}
}
}
```
### ES|QL
```esql
FROM my-index
| WHERE name RLIKE "joh.n.*"
```
**Note:** ES|QL uses standard regex syntax, not Lucene regex.
---
## Aggregations
### Terms Aggregation (Group By Count)
#### Query DSL
```json
{
"size": 0,
"aggs": {
"status_counts": {
"terms": {
"field": "status",
"size": 10
}
}
}
}
```
#### ES|QL
```esql
FROM my-index
| STATS count = COUNT(*) BY status
| SORT count DESC
| LIMIT 10
```
### Date Histogram Aggregation
#### Query DSL
```json
{
"size": 0,
"aggs": {
"events_over_time": {
"date_histogram": {
"field": "@timestamp",
"calendar_interval": "day"
}
}
}
}
```
#### ES|QL
```esql
FROM my-index
| STATS count = COUNT(*) BY day = DATE_TRUNC(1 day, @timestamp)
| SORT day
```
### Date Histogram with Sub-Aggregation
#### Query DSL
```json
{
"size": 0,
"aggs": {
"events_over_time": {
"date_histogram": {
"field": "@timestamp",
"calendar_interval": "hour"
},
"aggs": {
"avg_response": {
"avg": { "field": "response_time" }
}
}
}
}
}
```
#### ES|QL
```esql
FROM my-index
| STATS
count = COUNT(*),
avg_response = AVG(response_time)
BY hour = DATE_TRUNC(1 hour, @timestamp)
| SORT hour
```
### Multiple Metric Aggregations
#### Query DSL
```json
{
"size": 0,
"aggs": {
"price_stats": {
"stats": { "field": "price" }
}
}
}
```
#### ES|QL
```esql
FROM my-index
| STATS
count = COUNT(price),
min_price = MIN(price),
max_price = MAX(price),
avg_price = AVG(price),
sum_price = SUM(price)
```
### Percentiles Aggregation
#### Query DSL
```json
{
"size": 0,
"aggs": {
"response_percentiles": {
"percentiles": {
"field": "response_time",
"percents": [50, 95, 99]
}
}
}
}
```
#### ES|QL
```esql
FROM my-index
| STATS
p50 = PERCENTILE(response_time, 50),
p95 = PERCENTILE(response_time, 95),
p99 = PERCENTILE(response_time, 99)
```
### Cardinality Aggregation (Distinct Count)
#### Query DSL
```json
{
"size": 0,
"aggs": {
"unique_users": {
"cardinality": { "field": "user_id" }
}
}
}
```
#### ES|QL
```esql
FROM my-index
| STATS unique_users = COUNT_DISTINCT(user_id)
```
### Filter Aggregation
#### Query DSL
```json
{
"size": 0,
"aggs": {
"errors": {
"filter": { "term": { "level": "error" } },
"aggs": {
"count": { "value_count": { "field": "_id" } }
}
}
}
}
```
#### ES|QL
```esql
FROM my-index
| WHERE level == "error"
| STATS count = COUNT(*)
```
Or to get both total and filtered in one query using `CASE`:
```esql
FROM my-index
| STATS
total = COUNT(*),
errors = COUNT(CASE(level == "error", 1, null))
```
With per-aggregation `WHERE` (8.16+), this is simpler:
```esql
FROM my-index
| STATS
total = COUNT(*),
errors = COUNT(*) WHERE level == "error"
```
### Nested Aggregations (Multiple Group By)
#### Query DSL
```json
{
"size": 0,
"aggs": {
"by_country": {
"terms": { "field": "country" },
"aggs": {
"by_city": {
"terms": { "field": "city" }
}
}
}
}
}
```
#### ES|QL
```esql
FROM my-index
| STATS count = COUNT(*) BY country, city
| SORT country, count DESC
```
---
## Sorting
### Query DSL
```json
{
"sort": [{ "@timestamp": { "order": "desc" } }, { "name": { "order": "asc" } }]
}
```
### ES|QL
```esql
FROM my-index
| SORT @timestamp DESC, name ASC
```
---
## Field Selection (\_source)
### Query DSL
```json
{
"_source": ["title", "author", "date"]
}
```
### ES|QL
```esql
FROM my-index
| KEEP title, author, date
```
### Exclude Fields
#### Query DSL
```json
{
"_source": {
"excludes": ["internal_*", "temp"]
}
}
```
#### ES|QL
```esql
FROM my-index
| DROP internal_*, temp
```
---
## Pagination
### Query DSL
```json
{
"from": 20,
"size": 10
}
```
### ES|QL
ES|QL doesn't have direct `from` equivalent. Use filtering or time-based pagination:
```esql
FROM my-index
| SORT @timestamp DESC
| LIMIT 10
```
For subsequent pages, use the last seen value:
```esql
FROM my-index
| WHERE @timestamp < "2024-01-15T10:30:00Z"
| SORT @timestamp DESC
| LIMIT 10
```
---
## Script Fields
### Query DSL
```json
{
"script_fields": {
"price_with_tax": {
"script": {
"source": "doc['price'].value * 1.1"
}
}
}
}
```
### ES|QL
```esql
FROM my-index
| EVAL price_with_tax = price * 1.1
```
---
## LOOKUP JOIN (Replaces Enrichment Patterns)
### Query DSL
```json
{
"query": { "match_all": {} },
"runtime_mappings": {
"region_name": {
"type": "keyword",
"script": "/* typically handled via enrich processor or application-side join */"
}
}
}
```
### ES|QL
Use `LOOKUP JOIN` (8.18/9.0+) to join against a lookup index:
```esql
FROM orders
| LOOKUP JOIN customers_lookup ON customer_id
| KEEP order_id, customer_id, name, email, total
```
> **Note:** The lookup index must use `index.mode: lookup` and is limited to a single shard. Prefer `LOOKUP JOIN` over
> `ENRICH` for new queries.
---
## Filters Aggregation (Per-Aggregation WHERE)
### Query DSL
```json
{
"size": 0,
"aggs": {
"messages": {
"filters": {
"filters": {
"errors": { "match": { "level": "error" } },
"warnings": { "match": { "level": "warning" } }
}
}
}
}
}
```
### ES|QL
With per-aggregation `WHERE` (8.16+):
```esql
FROM logs
| STATS
errors = COUNT(*) WHERE level == "error",
warnings = COUNT(*) WHERE level == "warning",
total = COUNT(*)
```
---
## Pipeline Aggregations (Chained STATS)
### Query DSL
```json
{
"size": 0,
"aggs": {
"sales_per_month": {
"date_histogram": { "field": "date", "calendar_interval": "month" },
"aggs": {
"total_sales": { "sum": { "field": "amount" } },
"cumulative_sales": { "cumulative_sum": { "buckets_path": "total_sales" } }
}
}
}
}
```
### ES|QL
ES|QL doesn't have pipeline aggregations directly. Use chained `STATS` or `INLINE STATS` (9.2+) to compute derived
aggregations:
```esql
FROM sales
| STATS monthly_total = SUM(amount) BY month = DATE_TRUNC(1 month, date)
| SORT month
```
For adding aggregated values back to rows without collapsing (like a window function), use `INLINE STATS`:
```esql
FROM sales
| INLINE STATS avg_amount = AVG(amount) BY category
| EVAL diff_from_avg = amount - avg_amount
```
---
## Highlighting
### Query DSL
```json
{
"query": { "match": { "content": "elasticsearch" } },
"highlight": {
"fields": { "content": {} }
}
}
```
### ES|QL
**Not supported.** ES|QL doesn't have highlighting. Use Query DSL for this feature.
---
## ES|QL Limitations (vs Query DSL)
Features not available in ES|QL as of version 9.3:
| Feature | Query DSL | ES\|QL |
| ---------------------------- | --------- | ---------------------------------------- |
| Highlighting | ✅ | ❌ |
| Nested queries | ✅ | ❌ |
| Parent-child queries | ✅ | ❌ |
| Scroll/pagination beyond 10k | ✅ | ❌ |
| Percolate queries | ✅ | ❌ |
| Complex boosting | ✅ | Limited |
| Geo distance sorting | ✅ | ❌ |
| Runtime fields | ✅ | Use EVAL |
| Suggest API | ✅ | ❌ |
| Collapse (field collapsing) | ✅ | ❌ |
| Inner hits | ✅ | ❌ |
| Timezone support | ✅ | ✅ `SET time_zone` (9.4+ GA; Serverless) |
| JOIN (non-lookup) | N/A | ❌ (only LEFT JOIN on lookup index) |
| Subqueries / UNION ALL | N/A | ✅ `FROM` subqueries (9.4+; Serverless) |
### Unsupported Field Types in ES|QL
- `nested`
- `binary`
- `completion`
- `flattened` (use `METADATA _source` + `JSON_EXTRACT` to access sub-keys)
- Range types (`date_range`, `integer_range`, etc.)
- `rank_feature`, `rank_features`
- `search_as_you_type`
---
## Migration Checklist
When migrating from Query DSL to ES|QL:
1. **Check field type support** - Verify all fields use supported types
2. **Review aggregation complexity** - Deeply nested aggregations may need restructuring
3. **Handle scoring requirements** - Add `METADATA _score` if relevance sorting needed
4. **Adjust result limits** - ES|QL defaults to 1000 rows, max 10000
5. **Test full-text search** - Use `MATCH()`, `QSTR()`, or `KQL()` functions
6. **Validate time ranges** - ES|QL time syntax differs from DSL
7. **Check for unsupported features** - Highlighting, nested docs, etc.
---
## Performance Considerations
| Aspect | Query DSL | ES\|QL |
| ---------------- | ----------------------- | ---------------------- |
| Caching | Filter context cached | No equivalent caching |
| Query planning | Based on Lucene | Dedicated query engine |
| Aggregations | Can be memory-intensive | Block-based processing |
| Full-text search | Native Lucene | Uses same analyzers |
**ES|QL advantages:**
- Concurrent/parallel processing
- Block-based execution (more efficient for large scans)
- No query-to-DSL translation overhead
**Query DSL advantages:**
- More mature caching
- Better for complex scoring scenarios
- More features available
---
## Quick Reference Table
| Query DSL | ES\|QL Equivalent |
| -------------------------- | ------------------------------------------ |
| `match_all` | `FROM index` |
| `term` | `WHERE field == value` |
| `terms` | `WHERE field IN (...)` |
| `match` | `WHERE MATCH(field, query)` |
| `match_phrase` | `WHERE MATCH_PHRASE(field, query)` |
| `query_string` | `WHERE QSTR("...")` |
| `range` | `WHERE field >= x AND field <= y` |
| `bool.must` | `WHERE cond1 AND cond2` |
| `bool.should` | `WHERE cond1 OR cond2` |
| `bool.must_not` | `WHERE (field != val OR field IS NULL)` \* |
| `bool.filter` | `WHERE cond` (no scoring) |
| `exists` | `WHERE field IS NOT NULL` |
| `wildcard` | `WHERE field LIKE "pattern*"` |
| `regexp` | `WHERE field RLIKE "pattern"` |
| `prefix` | `WHERE STARTS_WITH(field, "prefix")` |
| `terms` agg | `STATS count = COUNT(*) BY field` |
| `date_histogram` | `STATS ... BY DATE_TRUNC(interval, field)` |
| `avg`, `sum`, `min`, `max` | `STATS AVG(f), SUM(f), MIN(f), MAX(f)` |
| `cardinality` | `STATS COUNT_DISTINCT(field)` |
| `percentiles` | `STATS PERCENTILE(field, p)` |
| `filter` agg | `COUNT(*) WHERE cond` (8.16+) |
| `top_hits` | `SORT field \| LIMIT n` |
| `_source` | `KEEP field1, field2` |
| `sort` | `SORT field DESC` |
| `size` | `LIMIT n` |
\* ES|QL uses three-valued logic. `field != value` excludes NULLs, unlike DSL `must_not` which keeps documents where the
field is missing. Add `OR field IS NULL` to match DSL behavior.
references/esql-reference.md
# ES|QL Complete Reference
ES|QL (Elasticsearch Query Language) is a piped query language for filtering, transforming, and analyzing data in
Elasticsearch. It uses pipes (`|`) to chain commands together.
> **Serverless vs Stack:** Version annotations in this document (e.g., "9.2+") apply to Elastic Stack (self-managed and
> Cloud-hosted). Detect cluster type via `build_flavor` in the `GET /` response: `"serverless"` means all GA and preview
> features are available — **do not** gate on `version.number` for Serverless (it tracks the next minor from main;
> semver-only checks may treat it as “latest”). For Stack, use `version.number` (strip any `-SNAPSHOT` suffix) for
> feature checks.
## Table of Contents
- [Query Structure](#query-structure)
- [Query Directives](#query-directives)
- [Source Commands](#source-commands)
- [Processing Commands](#processing-commands)
- [Aggregate Functions](#aggregate-functions)
- [Time Series Aggregation Functions](#time-series-aggregation-functions)
- [String Functions](#string-functions)
- [Math Functions](#math-functions)
- [Date/Time Functions](#datetime-functions)
- [Type Conversion Functions](#type-conversion-functions)
- [IP Functions](#ip-functions)
- [Spatial Functions](#spatial-functions)
- [Dense Vector Functions](#dense-vector-functions)
- [Multivalue Functions](#multivalue-functions)
- [Conditional Functions](#conditional-functions)
- [Full-Text Search Functions](#full-text-search-functions)
- [Operators](#operators)
- [Syntax Details](#syntax-details)
- [Metadata Fields](#metadata-fields)
- [Best Practices](#best-practices)
- [Example Queries](#example-queries)
## Query Structure
```esql
source-command
| processing-command1
| processing-command2
| ...
```
An ES|QL query starts with a **source command** followed by zero or more **processing commands** separated by pipes.
---
## Query Directives
Query directives modify the behavior of an ES|QL query. They appear before the source command.
### SET (9.3+, tech preview)
Controls query-level settings. Every `SET` directive must end with a semicolon before the source command.
**Syntax:**
```esql
SET setting = "value"; [SET setting = "value";]
source-command
| processing-commands
```
**`unmapped_fields`** (9.3+ preview) -- controls how unmapped fields are treated:
- `"default"` / `"fail"` -- the query fails if it references unmapped fields
- `"nullify"` -- treats unmapped fields as null values
- `"load"` (9.4+) -- loads unmapped fields dynamically as `keyword`. **Limitation:** `"load"` is incompatible with
subqueries and views. Use `"nullify"` when composing subqueries or querying views.
**`time_zone`** (9.4+ GA; Serverless) -- sets the default timezone for the query, overriding UTC default. Accepts any
IANA timezone string or UTC offset. Applies to all date/time operations: `DATE_TRUNC`, `DATE_FORMAT`, `NOW()`, etc.
**`approximation`** (9.5+ GA; Serverless; preview in 9.4) -- approximates `STATS` aggregations via random sampling and
extrapolation, returning estimates with confidence intervals for far faster results on large datasets:
- `true` -- enable approximation with default settings (1,000,000 sampled rows when grouped, 100,000 otherwise; central
90% confidence interval)
- `false` (default) -- exact execution
- map value -- enable with custom settings: `{"rows":N}` (sampled rows, `N` ≥ 10,000) and/or `{"confidence_level":X}`
(default `0.90`; `null` disables interval computation)
Adds `_approximation_confidence_interval(col)` and `_approximation_certified(col)` columns per estimated quantity.
`COUNT_DISTINCT`, `MIN`, `MAX`, `FIRST`, `LAST`, `TOP`, and several others are **not supported** and fall back to exact
execution — use the [`SAMPLE`](#sample) command for those. See [query-approximation.md](query-approximation.md) for the
full reference.
**Examples:**
```esql
SET unmapped_fields = "nullify";
FROM employees
| KEEP emp_no, foo
| SORT emp_no
| LIMIT 1
SET time_zone = "America/Los_Angeles";
FROM error_triage
| EVAL hour = DATE_TRUNC(1 hour, @timestamp)
| STATS errors = COUNT(*) BY hour, service
| SORT hour DESC
SET time_zone = "+05:00";
TS k8s
| WHERE @timestamp == "2024-05-10T00:04:49.000Z"
| STATS BY @timestamp, bucket = TBUCKET(3 hours)
SET approximation = true;
FROM web_traffic
| WHERE @timestamp >= NOW() - 1 week
| STATS total_hits = COUNT(*), avg_load_time = AVG(page_load_ms) BY country_code
| SORT total_hits DESC
| LIMIT 5
```
> **When to use:** `unmapped_fields` is useful when querying across multiple indices where some indices may not have all
> fields mapped. `time_zone` shifts date functions and display to a non-UTC zone. There is no per-function timezone
> argument — `DATE_TRUNC(1 hour, @timestamp, "America/Los_Angeles")` does **not** work. `approximation` trades exactness
> for speed on large `STATS` summaries — see [query-approximation.md](query-approximation.md).
>
> **Restriction:** `SET` directives cannot be used inside view definitions. The caller must apply `SET` when querying
> the view.
---
## Source Commands
Source commands produce tables, typically from Elasticsearch data.
### FROM
Retrieves data from indices, data streams, or aliases.
**Syntax:**
```esql
FROM index_pattern [METADATA fields]
```
**Examples:**
```esql
// Basic usage
FROM logs-*
// Multiple indices
FROM employees-00001, other-employees-*
// With metadata
FROM logs-* METADATA _id, _index
// Date math
FROM <logs-{now/d}>
// Cross-cluster search
FROM cluster_one:logs-*, cluster_two:logs-*
```
**Subqueries (9.4+; Serverless):** `FROM` supports parenthesized subqueries with UNION ALL semantics. Each branch is a
complete ES|QL pipeline. Columns present in one branch but not another are filled with `null`.
```esql
// Combine logs from different indices with independent pipelines
FROM
(FROM web_logs
| WHERE status_code >= 500
| KEEP @timestamp, message, service.name),
(FROM app_logs
| WHERE level == "error"
| KEEP @timestamp, message, service.name)
| STATS errors = COUNT(*) BY service.name
// Mix bare index patterns and subqueries
FROM raw_index, (FROM other_index | WHERE active == true | KEEP id, name)
```
**Subquery constraints:**
- Non-correlated only — branches cannot reference columns from the outer query
- Columns with the same name must have compatible types across branches
- `FORK` cannot be used inside or after subqueries
- `SET unmapped_fields="load"` is incompatible with subqueries
**Subqueries vs FORK:** Different data sources → subqueries. Same data, different analyses → FORK.
**Note:** Without explicit `LIMIT`, queries default to 1000 rows (or whatever the cluster setting
esql.query.result_truncation_default_size is set to).
### ROW
Creates a row with literal values. Useful for testing.
**Syntax:**
```esql
ROW column1 = value1 [, column2 = value2, ...]
```
**Examples:**
```esql
ROW a = 1, b = "two", c = null
ROW x = [1, 2, 3]
ROW greeting = "hello", pi = 3.14159
```
**Intra-row field references (9.4+; Serverless):** Columns defined earlier in the same `ROW` can be referenced by later
columns:
```esql
ROW a = 5, b = a * 2, c = a + b
```
### Views (9.4+ preview; Stack only — not available on Serverless)
Views are virtual indices backed by ES|QL queries. Query a view with `FROM view_name` like any index. Views are managed
via the `/_query/view` REST API.
**Create / update a view:**
```bash
PUT /_query/view/active_employees
{
"query": "FROM employees | WHERE is_active == true | KEEP emp_no, name, department"
}
```
**Query a view:**
```esql
FROM active_employees
| STATS headcount = COUNT(*) BY department
```
**Constraints:**
- `SET` directives cannot be used inside view definitions; the caller applies `SET` when querying
- `SET unmapped_fields = "load"` is incompatible with views; use `"nullify"` instead
- Views are not yet available on Serverless
### TS
Retrieves data from time series data streams (TSDS). Similar to `FROM` but enables time series aggregation functions in
`STATS` and targets only time series indices. **Preview from 9.2 to 9.3, GA since 9.4**; GA on Elastic Cloud Serverless.
**Syntax:**
```esql
TS index_pattern [METADATA fields]
```
**Key behavior:**
- Enables time series aggregation functions (`RATE`, `AVG_OVER_TIME`, etc.) in the first `STATS` command
- Time series functions are evaluated per time series first, then aggregated by group using an outer function
- If no inner time series function is specified, the default depends on field type: `LAST_OVER_TIME()` for numeric/gauge
fields; histogram merge for `exponential_histogram`/`tdigest` (plain `histogram` needs a cast — see
[Histogram Metrics](time-series-queries.md#histogram-metrics))
- Cannot be combined with `FORK` before `STATS` is applied
- When the query has no `STATS`, `TS` returns rows sorted by `@timestamp` descending by default
- When the first `STATS` after `TS` uses a **bare** time series function (not wrapped in an outer aggregation like
`AVG()` / `SUM()`), results are implicitly grouped by every dimension and include a `_timeseries` JSON column. Use
`BY WITHOUT(dim, ...)` (GA in 9.4) to narrow this grouping. Bare dimension columns in `BY` are rejected; only grouping
functions (`TBUCKET`, `WITHOUT`) are allowed alongside a bare time series function.
**Examples:**
```esql
// Rate of search requests per host per hour
TS metrics
| WHERE @timestamp >= NOW() - 1 hour
| STATS SUM(RATE(search_requests)) BY TBUCKET(1 hour), host
// Gauge — average of last values per time series (implicit LAST_OVER_TIME)
TS metrics
| STATS AVG(memory_usage)
// Average of per-time-series averages (explicit inner function)
TS metrics
| STATS AVG(AVG_OVER_TIME(memory_usage))
// Bare time series function — group by every dimension except `pod` (9.4+ GA)
TS k8s
| STATS total_cost = SUM(network.cost) BY WITHOUT(pod)
| SORT total_cost
```
**Best practices:**
- Add a time range filter on `@timestamp` to limit data volume
- Use `TS` instead of `FROM` for aggregations on time series data
- Avoid aggregating metrics with different dimensional cardinalities in the same query
### PROMQL
Queries time series data streams (TSDS) using **Prometheus Query Language (PromQL)** instead of ES|QL syntax. Like `TS`,
it produces a table that the rest of the ES|QL pipeline can process. Available since **9.4 (preview)** and on Elastic
Cloud Serverless. See [promql-command.md](promql-command.md) for the full reference.
**Syntax:**
```esql
PROMQL [ <option> ... ] [ <result_name> = ] ( <PromQL expression> )
```
**Options:**
- `index` — indices/streams/aliases (default `metrics-*`)
- `step` — query resolution step width
- `buckets` — target bucket count for auto-step (default `100`, mutually exclusive with `step`)
- `start`, `end` — explicit time range (defaults to Kibana date picker, otherwise unrestricted)
- `scrape_interval` — expected metric collection interval (default `1m`); used for the implicit range selector window
- `<result_name>=( ... )` — name the metric output column
**Output columns:**
- The PromQL expression (or `<result_name>`) as `double` — the metric value
- `step` (`date`) — timestamp for each evaluation step
- One `keyword` column per `by`/`without` grouping label, or a single `_timeseries` JSON column when there is no
cross-series aggregation
**Examples:**
```esql
// Fully adaptive Kibana query — date picker drives time range and step
PROMQL index=metrics-* sum by (instance) (rate(http_requests_total))
// Explicit range query with a named result column
PROMQL index=k8s step=1h cost=(max by (cluster) (network.total_bytes_in{cluster!="prod"}))
| SORT cluster
// Post-process with ES|QL after the PROMQL stage
PROMQL index=k8s step=1h bytes=(max by (cluster) (network.bytes_in))
| STATS max_bytes = MAX(bytes) BY cluster
| SORT cluster
// Enrich PromQL results with a lookup index
PROMQL index=metrics-*
http_rate=(sum by (instance) (rate(http_requests_total)))
| LOOKUP JOIN instance_metadata ON instance
```
**Implicit range selectors:** Range vector functions can omit the range selector (`rate(http_requests_total)` instead of
`rate(http_requests_total[5m])`); the engine uses `max(step, scrape_interval)` as the window. This makes the query scale
with the date picker.
**Limitations (9.4 preview):**
- Group modifiers (`on(...) group_left(...)`) are not supported
- Set operators (`or`, `and`, `unless`) are not supported
- Some PromQL functions are not available, including `histogram_quantile`, `predict_linear`, and `label_join`
- Time buckets align to fixed calendar boundaries rather than the query start time, which can cause slight differences
from native Prometheus for short ranges or large step sizes
When any of these are required, use the [`TS` command](#ts) and express the equivalent computation in ES|QL.
### SHOW
Returns information about the deployment.
**Syntax:**
```esql
SHOW INFO
```
---
## Processing Commands
Processing commands transform the input table.
### WHERE
Filters rows based on a boolean condition.
**Syntax:**
```esql
WHERE condition
```
**Examples:**
```esql
FROM employees
| WHERE salary > 50000
FROM logs-*
| WHERE status_code >= 400 AND status_code < 500
FROM events
| WHERE message LIKE "*error*"
FROM users
| WHERE name RLIKE "J.*n"
FROM data
| WHERE field IS NOT NULL
```
**NULL handling (three-valued logic):** ES|QL follows SQL-style three-valued logic. Comparisons involving `NULL`
evaluate to _unknown_, not `true` or `false`. This means `WHERE field != "value"` silently excludes rows where `field`
is `NULL` (missing). This differs from DSL, KQL, EQL, and Splunk, where negation typically includes missing fields.
To include `NULL` rows in negation filters, add an explicit `IS NULL` check:
```esql
// WRONG: silently drops rows where user.name is NULL
FROM logs-*
| WHERE user.name != "admin"
// CORRECT: includes rows where user.name is missing
FROM logs-*
| WHERE user.name != "admin" OR user.name IS NULL
```
### EVAL
Adds or replaces columns with calculated values.
**Syntax:**
```esql
EVAL column1 = expression1 [, column2 = expression2, ...]
```
**Examples:**
```esql
FROM employees
| EVAL annual_salary = monthly_salary * 12
FROM logs
| EVAL duration_ms = end_time - start_time
| EVAL duration_sec = duration_ms / 1000
FROM data
| EVAL full_name = CONCAT(first_name, " ", last_name)
| EVAL is_adult = age >= 18
```
### STATS ... BY
Aggregates data, optionally grouped by columns.
**Syntax:**
```esql
STATS aggregation1 [WHERE filter1] [, aggregation2 [WHERE filter2], ...] [BY grouping1, grouping2, ...]
```
**Examples:**
```esql
// Simple count
FROM logs-*
| STATS count = COUNT(*)
// Multiple aggregations
FROM sales
| STATS
total = SUM(amount),
avg_amount = AVG(amount),
max_amount = MAX(amount)
// Grouped aggregation
FROM logs-*
| STATS count = COUNT(*) BY status_code
// Multiple groupings
FROM sales
| STATS total = SUM(amount) BY region, product_category
// Time-based grouping
FROM logs-*
| STATS count = COUNT(*) BY bucket = DATE_TRUNC(1 hour, @timestamp)
// Per-aggregation WHERE filters (8.16+) — conditional metrics in a single pass
FROM logs-*
| STATS
total = COUNT(*),
errors = COUNT(*) WHERE level == "error",
warnings = COUNT(*) WHERE level == "warning"
BY service.name
// Cluster semi-structured text into categories of similar format (requires Platinum license)
FROM logs-*
| STATS count = COUNT(*) BY category = CATEGORIZE(message)
// Control the clustering threshold: (1-100): Lower -> less clusters, default=70
FROM logs-*
| STATS count = COUNT(*) BY category = CATEGORIZE(message, {"similarity_threshold": 85})
// Use token output format and a custom analyzer
FROM logs-*
| STATS count = COUNT(*) BY category = CATEGORIZE(message, {"output_format": "tokens", "analyzer": "standard"})
```
### INLINE STATS ... BY
Aggregates data like `STATS`, but preserves all original columns and appends the aggregated values as new columns. The
output has the same number of rows as the input. Tech preview in 9.2, GA in 9.3.
**Syntax:**
```esql
INLINE STATS aggregation1 [WHERE filter1] [, aggregation2 [WHERE filter2], ...] [BY grouping1, grouping2, ...]
```
**Key differences from STATS:**
- `STATS` replaces the input table with aggregation results (fewer rows)
- `INLINE STATS` keeps every input row and adds the aggregated values as new columns
**Examples:**
```esql
// Add each employee's group max salary alongside their own salary
FROM employees
| KEEP emp_no, languages, salary
| INLINE STATS max_salary = MAX(salary) BY languages
// Add a global aggregation to every row (no BY clause)
FROM employees
| KEEP emp_no, salary
| INLINE STATS avg_salary = AVG(salary)
| WHERE salary > avg_salary
// Filter rows per aggregation with WHERE
FROM employees
| KEEP emp_no, salary
| INLINE STATS
avg_low = ROUND(AVG(salary)) WHERE salary < 50000,
avg_high = ROUND(AVG(salary)) WHERE salary >= 50000
```
**Use cases:**
- Compare individual values against group averages or totals
- Calculate percentages of group totals without a separate query
- Replaces some patterns that would require subqueries in SQL
**Limitations:**
- Cannot use `FORK` or `LIMIT` before `INLINE STATS`
- `CATEGORIZE` grouping function is not supported
### KEEP
Keeps only specified columns.
**Syntax:**
```esql
KEEP column1 [, column2, ...]
```
**Examples:**
```esql
FROM employees
| KEEP first_name, last_name, salary
// With wildcards
FROM logs-*
| KEEP @timestamp, message, error.*
```
### DROP
Removes specified columns.
**Syntax:**
```esql
DROP column1 [, column2, ...]
```
**Examples:**
```esql
FROM employees
| DROP internal_id, temp_field
// With wildcards
FROM data
| DROP temp_*, debug_*
```
### RENAME
Renames columns.
**Syntax:**
```esql
RENAME old_name AS new_name [, old_name2 AS new_name2, ...]
```
**Examples:**
```esql
FROM employees
| RENAME emp_id AS employee_id
FROM data
| RENAME col1 AS column_one, col2 AS column_two
```
### SORT
Sorts the table.
**Syntax:**
```esql
SORT column1 [ASC/DESC] [NULLS FIRST/LAST] [, column2 ...]
```
**Examples:**
```esql
FROM employees
| SORT salary DESC
FROM logs-*
| SORT @timestamp DESC, severity ASC
FROM data
| SORT value ASC NULLS LAST
```
### LIMIT
Limits the number of rows returned. Supports optional grouped top-N with `BY` since 9.4+ and in Serverless.
**Syntax:**
```esql
LIMIT number
LIMIT number BY field (9.4+; Serverless)
```
**Examples:**
```esql
FROM logs-*
| SORT @timestamp DESC
| LIMIT 100
// Grouped top-N: keep top 3 rows per service after sorting
FROM app_logs
| STATS cnt = COUNT(*) BY service, level
| SORT cnt DESC
| LIMIT 3 BY service
```
> **Note:** In `LIMIT n BY field`, the number comes **before** `BY`. `LIMIT BY field n` does not parse.
### DISSECT
Extracts structured fields from a string using a pattern.
**Syntax:**
```esql
DISSECT field "%{pattern}"
```
**Examples:**
```esql
FROM logs
| DISSECT message "%{clientip} - - [%{timestamp}] \"%{method} %{path}\""
FROM apache_logs
| DISSECT message "%{ip} %{} %{} [%{timestamp}] \"%{request}\" %{status} %{bytes}"
```
**Cookbook — Common DISSECT Patterns:**
```esql
// Extract email domain
FROM customers
| DISSECT email "%{local}@%{domain}"
| STATS count = COUNT(*) BY domain
// Parse HTTP method and path from log messages like "GET /api/users HTTP/1.1"
FROM logs
| DISSECT message "%{method} %{path} %{protocol}"
| WHERE method IS NOT NULL
| KEEP @timestamp, method, path
// Extract key-value pairs from structured strings like "user=admin action=login"
FROM audit_logs
| DISSECT message "%{key1}=%{val1} %{key2}=%{val2}"
```
**Limitations:** DISSECT does not support
[reference keys](https://www.elastic.co/docs/reference/query-languages/esql/esql-process-data-with-dissect-grok#esql-dissect-limitations)
(e.g., `%{*key}` / `%{&key}` for dynamic key-value extraction).
### GROK
Extracts fields using grok patterns (regex-based).
**Syntax:**
```esql
GROK field "%{PATTERN:field_name}"
```
**Common Patterns:**
- `%{IP:ip}` - IP address
- `%{NUMBER:num}` - Number
- `%{WORD:word}` - Word
- `%{DATA:data}` - Any data (non-greedy)
- `%{GREEDYDATA:text}` - Any data (greedy)
- `%{TIMESTAMP_ISO8601:ts}` - ISO timestamp
**Examples:**
```esql
FROM logs
| GROK message "%{IP:client_ip} %{WORD:method} %{NUMBER:status:int}"
FROM web_logs
| GROK agent "%{WORD:browser}/%{NUMBER:version}"
```
**Limitations:** ES|QL GROK does not support
[custom patterns](https://www.elastic.co/docs/reference/query-languages/esql/esql-process-data-with-dissect-grok#esql-custom-patterns)
or [multiple pattern matching](https://www.elastic.co/docs/reference/enrich-processor/grok-processor#trace-match). Only
built-in grok patterns are available.
### LOOKUP JOIN
Joins data from a lookup index onto the current results. The preferred way to enrich query results with data from
another index. GA in 8.19/9.1.
**Syntax:**
```esql
LOOKUP JOIN lookup_index ON join_field
```
**Key behavior:**
- Performs a LEFT OUTER JOIN — all rows from the source are preserved; unmatched rows get `NULL` for lookup fields
- The lookup index must use `index.mode: lookup` (single shard, max 2B docs)
- Supports multi-field joins (9.2+) and mixed numeric types
- Updates to the lookup index are reflected immediately in subsequent queries
- **Name collisions:** If a lookup field has the same name as an existing source column, the lookup value overwrites it.
Use `RENAME` before the join to preserve the original column when needed.
**Examples:**
```esql
// Enrich logs with user metadata
FROM logs-*
| LOOKUP JOIN users ON user.id
// Add product details to order data
FROM orders
| LOOKUP JOIN products ON product_id
| STATS revenue = SUM(price * quantity) BY product_name
// Enrich security events with threat intelligence
FROM security-events
| LOOKUP JOIN threat_intel ON source.ip
| WHERE threat_level == "high"
```
**Multi-field joins (9.2+):**
```esql
// Join on multiple fields — match service, environment, and version
FROM application_logs
| LOOKUP JOIN service_registry ON service_name, environment, version
```
**Complex join predicates with expressions (9.2+ tech preview):**
```esql
// Range-based join — find the SLA threshold for each service's response time
FROM app_metrics
| LOOKUP JOIN sla_thresholds ON service == service_name AND response_time_ms >= threshold_min
// Date-range join — find the pricing policy active at measurement time
FROM meter_readings
| LOOKUP JOIN customers ON customer_id
| LOOKUP JOIN pricing_policies ON region_id == region AND measurement_date >= valid_from AND measurement_date < valid_to
| EVAL due_amount = usage * price_per_unit
```
**Lucene-pushable predicates in joins (9.3+ tech preview):**
Full-text functions and other Lucene-pushable predicates (`MATCH`, `QSTR`, `KQL`, `LIKE`, `STARTS_WITH`) can be applied
to lookup index fields in the `ON` clause, enabling search-style joins.
```esql
// Full-text search against lookup index fields
FROM support_tickets
| LOOKUP JOIN knowledge_base ON MATCH(article_content, issue_description) AND product == product_name
// Combine text search with equality join
FROM error_logs
| LOOKUP JOIN runbooks ON QSTR("title:timeout OR title:connection") AND service == service_name
```
### ENRICH
Enriches data using a pre-configured enrich policy. On clusters with 8.18+, prefer `LOOKUP JOIN` — it requires no policy
setup and reflects changes immediately. On clusters **before 8.18**, `ENRICH` is the only option for data enrichment. If
no enrich policy exists, suggest the user create one (see [Generation Tips](generation-tips.md#lookup-join-and-enrich)
for setup steps).
**Syntax:**
```esql
ENRICH policy_name ON match_field [WITH new_field1, new_field2, ...]
```
**Examples:**
```esql
FROM logs
| ENRICH geo_policy ON client_ip WITH country, city
FROM sales
| ENRICH products_policy ON product_id WITH product_name, category
```
### CHANGE_POINT
Detects spikes, dips, and change points in a metric. Requires a Platinum license. Tech preview in 8.19/9.1, GA in 9.2.
**Syntax:**
```esql
CHANGE_POINT value ON key [AS type_name, pvalue_name]
```
- `value` -- the metric field to analyze for change points
- `key` -- the field to order by (typically a date or sequence)
- `type_name` -- output column for the type of change (`step_change`, `distribution_change`, `trend_change`, `dip`,
`spike`, `non_stationary`, `stationary`, `no_change`)
- `pvalue_name` -- output column for the p-value (statistical significance)
**Examples:**
```esql
// Detect change points in error rates over time
FROM logs-*
| STATS error_count = COUNT(*) WHERE level == "error" BY hour = DATE_TRUNC(1 hour, @timestamp)
| SORT hour
| CHANGE_POINT error_count ON hour AS change_type, p_value
// Find significant changes in response times
FROM metrics
| STATS avg_latency = AVG(response_time) BY minute = DATE_TRUNC(1 minute, @timestamp)
| SORT minute
| CHANGE_POINT avg_latency ON minute
```
### FORK
Creates multiple execution branches that operate on the same input data and combines results into a single output table.
A `_fork` column identifies which branch each row came from. Tech preview in 9.1.
**Syntax:**
```esql
FORK ( processing_commands ) ( processing_commands ) [... ( processing_commands )]
```
**Constraints:**
- Maximum 8 branches
- Each branch defaults to `LIMIT 1000` if no LIMIT is specified
- Columns with the same name must have the same type across branches; missing columns are filled with null
- Cannot use remote cluster references with FORK
- Only one FORK per query
**Examples:**
```esql
// Run different aggregations on the same data
FROM logs-*
| FORK
( WHERE level == "error" | STATS errors = COUNT(*) BY service.name )
( WHERE level == "warning" | STATS warnings = COUNT(*) BY service.name )
// Compare different time windows
FROM metrics
| FORK
( WHERE @timestamp > NOW() - 1 hour | STATS recent_avg = AVG(cpu) )
( WHERE @timestamp > NOW() - 24 hours | STATS daily_avg = AVG(cpu) )
| SORT _fork
// Search with multiple strategies — combine full-text and keyword matches
FROM articles METADATA _score
| FORK
( WHERE MATCH(content, "elasticsearch performance") | SORT _score DESC | LIMIT 10 )
( WHERE MATCH_PHRASE(title, "search optimization") | SORT _score DESC | LIMIT 10 )
( WHERE category == "guides" AND tags : "elasticsearch" | SORT published_date DESC | LIMIT 10 )
| KEEP _fork, title, _score, published_date
```
### FUSE
Merges rows from multiple result sets (typically from FORK branches) and assigns new relevance scores. Tech preview in
9.2.
**Syntax:**
```esql
FUSE method SCORE BY score_column GROUP BY group_column KEY BY key_columns [WITH options]
```
**Methods:**
- `rrf` — Reciprocal Rank Fusion. Combines ranked lists by reciprocal rank; no score normalization needed.
- `linear` — Linear combination of scores. Supports `normalizer` and per-branch `weights`.
**LINEAR options:**
| Option | Type | Default | Description |
| ------------ | ------- | ------- | -------------------------------------------------------- |
| `normalizer` | keyword | — | Score normalization method; `minmax` maps scores to 0–1 |
| `weights` | object | equal | Per-branch weights (e.g. `{"fork1": 0.7, "fork2": 0.3}`) |
**Examples:**
```esql
// RRF fusion (default)
FROM articles METADATA _score
| FORK
( WHERE MATCH(content, "elasticsearch") | SORT _score DESC | LIMIT 50 )
( WHERE MATCH(title, "search guide") | SORT _score DESC | LIMIT 50 )
| FUSE rrf SCORE BY _score KEY BY _id
| LIMIT 10
// LINEAR fusion with minmax normalization and custom weights
FROM articles METADATA _id, _index, _score
| FORK
( WHERE MATCH(content, "elasticsearch") | SORT _score DESC | LIMIT 50 )
( WHERE semantic_content : "how does elasticsearch work" | SORT _score DESC | LIMIT 50 )
| FUSE linear WITH { "normalizer": "minmax", "weights": { "fork1": 0.7, "fork2": 0.3 } }
| SORT _score DESC
| LIMIT 10
```
### RERANK
Uses an inference model to re-score an initial set of documents. GA in 9.4 (Serverless). Since 9.3, defaults to 1000
rows; configurable via `esql.command.rerank.limit` and `esql.command.rerank.enabled` cluster settings.
**Syntax:**
```esql
RERANK [column =] query ON field [, field, ...] [WITH { "inference_id": "endpoint" }]
```
**Example:**
```esql
FROM articles METADATA _score
| WHERE MATCH(content, "elasticsearch performance")
| SORT _score DESC
| LIMIT 100
| RERANK "how to improve elasticsearch performance" ON content WITH { "inference_id": "my-rerank-model" }
| LIMIT 10
```
### COMPLETION
Sends prompts and context to a Large Language Model (LLM) using a `completion` inference endpoint. Tech preview in
8.19/9.1, requires Platinum license.
**Syntax:**
```esql
[column =] COMPLETION prompt WITH inference_endpoint
```
**Example:**
```esql
FROM support_tickets
| WHERE status == "open"
| EVAL prompt = CONCAT("Summarize this ticket: ", description)
| COMPLETION summary = prompt WITH my_llm_endpoint
| KEEP ticket_id, summary
```
### SAMPLE
Samples a random fraction of rows from the input table. Tech preview in 8.19/9.1.
**Syntax:**
```esql
SAMPLE probability
```
- `probability` -- value between 0 and 1 (exclusive), the chance each row is included
**Example:**
```esql
// Sample ~10% of rows for exploratory analysis
FROM logs-*
| SAMPLE 0.1
| STATS avg_duration = AVG(duration) BY service.name
```
`SAMPLE` can be used as a manual alternative to automatic [query approximation](query-approximation.md) when you need
control over sampling or an aggregation that approximation does not support (e.g. `COUNT_DISTINCT`). Unlike
`SET approximation`, it performs **no** extrapolation or confidence interval computation — accounting for sampling bias
is your responsibility.
### MV_EXPAND
Expands multivalued fields into separate rows.
**Syntax:**
```esql
MV_EXPAND field
```
**Examples:**
```esql
FROM data
| MV_EXPAND tags
| STATS count = COUNT(*) BY tags
```
### METRICS_INFO
Returns one row per distinct metric available in the targeted time series data stream(s), with applicable dimensions and
metadata. Use it to discover the metric catalogue without inspecting index mappings or calling the field capabilities
API. **GA since 9.4** (and on Elastic Cloud Serverless).
**Syntax:**
```esql
METRICS_INFO
```
Takes no parameters.
**Output columns** (all `keyword`):
- `metric_name` — the metric field name (single-valued)
- `data_stream` — data stream(s) containing this metric (multi-valued when several streams align on
unit/metric_type/field_type)
- `unit` — declared unit from field mapping (e.g., `bytes`, `packets`); may be `null` or multi-valued
- `metric_type` — `counter`, `gauge`, or `histogram` (multi-valued when definitions differ across backing indices)
- `field_type` — Elasticsearch field type (e.g., `long`, `double`, `histogram`, `exponential_histogram`, `tdigest`)
- `dimension_fields` — union of dimension field names across all time series for that metric
**Restrictions:**
- Can only be used after a `TS` source command — `FROM | METRICS_INFO` is rejected.
- Must appear before pipeline-breaking commands (`STATS`, `SORT`, `LIMIT`).
- The output replaces the original table — downstream commands operate on the metadata rows, not the raw documents.
**Examples** (`TRANGE` is omitted below for brevity — always add `| WHERE TRANGE(...)` after `TS` so discovery scans a
bounded window):
```esql
// List every metric in a TSDS, alphabetically
TS k8s
| METRICS_INFO
| SORT metric_name
// Narrow to metrics that have data matching a filter, then keep only key columns
TS k8s
| WHERE cluster == "prod"
| METRICS_INFO
| KEEP metric_name, metric_type
| SORT metric_name
// Count metrics by type
TS k8s
| METRICS_INFO
| STATS metric_count = COUNT(*) BY metric_type
| SORT metric_type
// Find metrics matching a name pattern
TS k8s
| METRICS_INFO
| WHERE metric_name LIKE "network.eth0*"
| SORT metric_name
```
### TS_INFO
Returns one row per (metric, time series) combination in the targeted TSDS, including the dimension key/value pairs that
identify each series. Use it to enumerate the actual time series — and their labels — that exist for each metric. **GA
since 9.4** (and on Elastic Cloud Serverless).
**Syntax:**
```esql
TS_INFO
```
Takes no parameters.
**Output columns** (all `keyword`):
- All columns from `METRICS_INFO` (`metric_name`, `data_stream`, `unit`, `metric_type`, `field_type`,
`dimension_fields`)
- `dimensions` — JSON-encoded object with the dimension key/value pairs identifying the time series, e.g.
`{"job":"elasticsearch","instance":"instance_1"}`. Single-valued.
**Restrictions:**
- Can only be used after a `TS` source command — `FROM | TS_INFO` is rejected.
- Must appear before pipeline-breaking commands (`STATS`, `SORT`, `LIMIT`).
- The output replaces the original table — downstream commands operate on the metadata rows, not the raw documents.
**Examples** (same as `METRICS_INFO`: always precede with `WHERE TRANGE(...)` in real queries):
```esql
// Every (metric, time series) pair in a TSDS
TS k8s
| TS_INFO
| SORT metric_name, dimensions
// Restrict to series with data matching a filter, keep only key columns
TS k8s
| WHERE cluster == "prod"
| TS_INFO
| KEEP metric_name, dimensions
| SORT metric_name, dimensions
// Filter by metadata after TS_INFO
TS k8s
| TS_INFO
| WHERE metric_type == "gauge"
| SORT metric_name, dimensions
// Count distinct time series per metric
TS k8s
| TS_INFO
| STATS series_count = COUNT(*) BY metric_name
| SORT metric_name
// Count distinct metrics per time series — useful to spot under- or over-reporting series
TS k8s
| TS_INFO
| STATS metric_count = COUNT_DISTINCT(metric_name) BY dimensions
| SORT dimensions
```
> **`METRICS_INFO` vs `TS_INFO`:** `METRICS_INFO` returns one row **per distinct metric**; `TS_INFO` returns one row
> **per (metric, time series) combination** and adds a `dimensions` column with the labels identifying each series. Use
> `METRICS_INFO` to enumerate _what_ is being measured, and `TS_INFO` to enumerate _which_ time series exist.
### URI_PARTS (9.4+; Serverless)
Pipe command that parses a URI string into structured columns. A target prefix is **required**.
**Syntax:**
```esql
URI_PARTS target = field
```
**Output columns:** `target.domain`, `target.path`, `target.scheme`, `target.extension`, `target.port`, `target.query`,
`target.fragment`, `target.user_info`, `target.username`, `target.password`.
**Example:**
```esql
FROM web_logs
| WHERE http.response.status_code >= 400
| URI_PARTS parts = url.full
| STATS errors = COUNT(*) BY parts.domain, parts.path
| SORT errors DESC
```
### USER_AGENT (9.4+; Serverless)
Pipe command that parses a user agent string into structured columns. A target prefix is **required**.
**Syntax:**
```esql
USER_AGENT target = field
```
**Output columns:** `target.name`, `target.version`, `target.os.name`, `target.os.version`, `target.os.full`,
`target.device.name`.
**Example:**
```esql
FROM web_logs
| USER_AGENT ua = user_agent.original
| STATS cnt = COUNT(*) BY ua.name, ua.version
```
### REGISTERED_DOMAIN (9.4+; Serverless)
Pipe command that extracts the registered domain, top-level domain, and subdomain from a hostname. A target prefix is
**required**.
**Syntax:**
```esql
REGISTERED_DOMAIN target = field
```
**Output columns:** `target.domain` (full input), `target.registered_domain`, `target.top_level_domain`,
`target.subdomain`.
**Example:**
```esql
FROM dns_logs
| REGISTERED_DOMAIN rd = dns.question.name
| STATS queries = COUNT(*) BY rd.registered_domain
| SORT queries DESC
```
> **Note:** `URI_PARTS`, `USER_AGENT`, and `REGISTERED_DOMAIN` are **pipe commands** (like `DISSECT`/`GROK`), not scalar
> functions. The syntax `URI_PARTS(field)` does not work — use `| URI_PARTS target = field`.
### MMR (9.4+ preview; Serverless)
Maximal Marginal Relevance — diversifies search results by reducing redundancy among top hits. Requires a dense vector
field and a `LIMIT` before `MMR` to constrain the candidate set.
**Syntax:**
```esql
MMR query ON vector_field LIMIT n
```
**Example:**
```esql
FROM articles
| WHERE MATCH(title, "elasticsearch tuning")
| LIMIT 100
| MMR "elasticsearch performance tuning" ON content_embedding LIMIT 10
```
---
## Aggregate Functions
Used with STATS command.
| Function | Description | Example |
| ---------------------------------- | ----------------------------------------------- | ------------------------------------------------ |
| `COUNT(*)` | Count all rows | `STATS n = COUNT(*)` |
| `COUNT(field)` | Count non-null values | `STATS n = COUNT(status)` |
| `COUNT_DISTINCT(field)` | Count unique values | `STATS unique = COUNT_DISTINCT(user_id)` |
| `SUM(field)` | Sum of values | `STATS total = SUM(amount)` |
| `AVG(field)` | Average | `STATS avg_price = AVG(price)` |
| `MIN(field)` | Minimum value | `STATS min_temp = MIN(temperature)` |
| `MAX(field)` | Maximum value | `STATS max_score = MAX(score)` |
| `MEDIAN(field)` | Median value | `STATS med = MEDIAN(response_time)` |
| `PERCENTILE(field, p)` | Percentile | `STATS p95 = PERCENTILE(latency, 95)` |
| `STD_DEV(field)` | Standard deviation | `STATS sd = STD_DEV(values)` |
| `VARIANCE(field)` | Variance | `STATS var = VARIANCE(values)` |
| `VALUES(field)` | Collect all values (GA) | `STATS all_tags = VALUES(tag)` |
| `TOP(field, n, order)` | Top N values | `STATS top3 = TOP(score, 3, "desc")` |
| `WEIGHTED_AVG(val, weight)` | Weighted average | `STATS wavg = WEIGHTED_AVG(score, weight)` |
| `MEDIAN_ABSOLUTE_DEVIATION(field)` | Robust variability measure | `STATS mad = MEDIAN_ABSOLUTE_DEVIATION(latency)` |
| `ABSENT(field)` | True if no non-null values (9.2+) | `STATS is_absent = ABSENT(error_code)` |
| `PRESENT(field)` | True if any non-null values (9.2+) | `STATS has_data = PRESENT(metric)` |
| `SAMPLE(field, n)` | Collect n sample values (8.19/9.1+) | `STATS examples = SAMPLE(message, 5)` |
| `FIRST(field, sort_field)` | Earliest value by sort field (9.4+; Serverless) | `STATS earliest = FIRST(message, @timestamp)` |
| `LAST(field, sort_field)` | Latest value by sort field (9.4+; Serverless) | `STATS latest = LAST(message, @timestamp)` |
| `EARLIEST(field)` | Min `@timestamp` shorthand (9.4+; Serverless) | `STATS e = EARLIEST(@timestamp)` |
| `LATEST(field)` | Max `@timestamp` shorthand (9.4+; Serverless) | `STATS l = LATEST(@timestamp)` |
| `ST_CENTROID_AGG(field)` | Spatial centroid of points | `STATS center = ST_CENTROID_AGG(location)` |
| `ST_EXTENT_AGG(field)` | Bounding box of geometries (8.18/9.0+, preview) | `STATS bbox = ST_EXTENT_AGG(location)` |
### Grouping Functions
Used in the `BY` clause of `STATS` and `INLINE STATS` to create dynamic groups.
| Function | Description | Example |
| --------------------- | -------------------------------------------------------------------- | ----------------------------------------------------- |
| `BUCKET(field, size)` | Create fixed-size buckets for numbers or dates | `STATS count = COUNT(*) BY b = BUCKET(price, 10)` |
| `TBUCKET(interval)` | Time-based bucketing (preview 9.2-9.3, GA in 9.4) | `STATS SUM(RATE(reqs)) BY TBUCKET(1 hour)` |
| `WITHOUT(dim, ...)` | Group time series by every dimension except those listed (GA in 9.4) | `STATS total = SUM(network.cost) BY WITHOUT(pod)` |
| `CATEGORIZE(field)` | Auto-categorize text values (8.18/9.0+, Platinum) | `STATS count = COUNT(*) BY cat = CATEGORIZE(message)` |
**CATEGORIZE options (9.2+):**
| Option | Type | Default | Description |
| ---------------------- | ------- | ------- | ----------------------------------------------------------------- |
| `similarity_threshold` | integer | `70` | Clustering sensitivity (1–100); lower = fewer clusters |
| `output_format` | keyword | `regex` | Output as `regex` patterns or space-separated `tokens` |
| `analyzer` | keyword | field's | Override the analyzer used to tokenize text before categorization |
**BUCKET examples:**
```esql
// Numeric buckets — group prices into ranges of 50
FROM products
| STATS count = COUNT(*) BY price_range = BUCKET(price, 50)
| SORT price_range
// Date buckets — group events into 1-hour intervals
FROM logs-*
| WHERE @timestamp > NOW() - 24 hours
| STATS count = COUNT(*) BY hour = BUCKET(@timestamp, 1 hour)
| SORT hour
// Auto-sized buckets — let ES pick bucket size (target ~20 buckets)
FROM logs-*
| WHERE @timestamp > NOW() - 7 days
| STATS count = COUNT(*) BY bucket = BUCKET(@timestamp, 20, "2025-01-01", "2025-01-08")
```
---
## Time Series Aggregation Functions
Used with the `STATS` command after a `TS` source command. These functions evaluate per time series first, then
aggregate by group using an outer function (e.g., `SUM`, `AVG`). An optional second argument specifies a sliding time
window.
**Availability:** **All** time series aggregation functions are **GA since 9.4** — both the 9.2-introduced set (`RATE`,
`IRATE`, `INCREASE`, `DELTA`, `IDELTA`, `AVG_OVER_TIME`, `SUM_OVER_TIME`, `MIN_OVER_TIME`, `MAX_OVER_TIME`,
`FIRST_OVER_TIME`, `LAST_OVER_TIME`, `COUNT_OVER_TIME`, `COUNT_DISTINCT_OVER_TIME`, `PRESENT_OVER_TIME`,
`ABSENT_OVER_TIME`) and the 9.3-introduced set (`DERIV`, `PERCENTILE_OVER_TIME`, `STDDEV_OVER_TIME`,
`VARIANCE_OVER_TIME`). On clusters in 9.2-9.3 these functions are still in tech preview.
**Sliding window parameter (second argument):** in 9.2-9.3 (preview) the window must be a multiple of the `TBUCKET`
interval; **9.4+ (GA)** accepts arbitrary durations, with performance optimizations when the window is a multiple of the
bucket interval. Within a single query, you cannot mix windows smaller than the bucket interval for one metric with
windows larger than the bucket interval for another metric.
| Function | Description | Metric Types |
| ----------------------------------- | ------------------------------ | -------------- |
| `RATE(field [, window])` | Per-second rate of change | counter |
| `IRATE(field [, window])` | Instantaneous rate of change | counter |
| `INCREASE(field [, window])` | Total increase | counter |
| `AVG_OVER_TIME(field [, window])` | Average over time | gauge, counter |
| `SUM_OVER_TIME(field [, window])` | Sum over time | gauge |
| `MIN_OVER_TIME(field [, window])` | Minimum over time | gauge |
| `MAX_OVER_TIME(field [, window])` | Maximum over time | gauge |
| `LAST_OVER_TIME(field [, window])` | Last value over time | gauge, counter |
| `FIRST_OVER_TIME(field [, window])` | First value over time | gauge, counter |
| `COUNT_OVER_TIME(field [, window])` | Count of values over time | gauge, counter |
| `COUNT_DISTINCT_OVER_TIME(field)` | Distinct count over time | gauge, counter |
| `PERCENTILE_OVER_TIME(field, p)` | Percentile over time | gauge |
| `VARIANCE_OVER_TIME(field)` | Variance over time | gauge |
| `STDDEV_OVER_TIME(field)` | Standard deviation over time | gauge |
| `DELTA(field [, window])` | Change in value | gauge |
| `IDELTA(field [, window])` | Instantaneous change | gauge |
| `DERIV(field [, window])` | Rate of change for gauges | gauge |
| `PRESENT_OVER_TIME(field)` | Whether time series has data | gauge, counter |
| `ABSENT_OVER_TIME(field)` | Whether time series lacks data | gauge, counter |
**Grouping helpers for time series:**
- `TBUCKET(interval)` — groups results into time buckets (used in `BY` clause)
- `TRANGE(duration)` — filters to a time range (used in `WHERE` clause)
**Examples:**
```esql
// Sum of per-time-series rates, grouped by host and hour
TS metrics
| WHERE @timestamp >= NOW() - 1 hour
| STATS SUM(RATE(search_requests)) BY TBUCKET(1 hour), host
// Average rate with a 10-minute sliding window, bucketed per minute
TS metrics
| WHERE TRANGE(1 hour)
| STATS AVG(RATE(requests, 10 minutes)) BY TBUCKET(1 minute), host
```
---
## String Functions
| Function | Description | Example |
| --------------------------- | --------------------------------------------------------- | -------------------------------------------------------------------- |
| `LENGTH(s)` | String length | `EVAL len = LENGTH(name)` |
| `CONCAT(s1, s2, ...)` | Concatenate strings | `EVAL full = CONCAT(first, " ", last)` |
| `SUBSTRING(s, start, len)` | Extract substring | `EVAL sub = SUBSTRING(text, 1, 10)` |
| `LEFT(s, n)` | Left n characters | `EVAL l = LEFT(text, 5)` |
| `RIGHT(s, n)` | Right n characters | `EVAL r = RIGHT(text, 5)` |
| `TRIM(s)` | Remove whitespace | `EVAL clean = TRIM(input)` |
| `LTRIM(s)` | Trim left | `EVAL clean = LTRIM(input)` |
| `RTRIM(s)` | Trim right | `EVAL clean = RTRIM(input)` |
| `TO_UPPER(s)` | Uppercase | `EVAL upper = TO_UPPER(name)` |
| `TO_LOWER(s)` | Lowercase | `EVAL lower = TO_LOWER(name)` |
| `REPLACE(s, old, new)` | Replace text | `EVAL fixed = REPLACE(msg, "err", "error")` |
| `SPLIT(s, delim)` | Split into array | `EVAL parts = SPLIT(path, "/")` |
| `STARTS_WITH(s, prefix)` | Check prefix | `WHERE STARTS_WITH(url, "https")` |
| `ENDS_WITH(s, suffix)` | Check suffix | `WHERE ENDS_WITH(file, ".log")` |
| `CONTAINS(s, substr)` | Check contains | `WHERE CONTAINS(message, "error")` |
| `LOCATE(substr, s)` | Find position | `EVAL pos = LOCATE("@", email)` |
| `REVERSE(s)` | Reverse string | `EVAL rev = REVERSE(text)` |
| `REPEAT(s, n)` | Repeat string | `EVAL sep = REPEAT("-", 10)` |
| `SPACE(n)` | N spaces | `EVAL spaces = SPACE(5)` |
| `BIT_LENGTH(s)` | Bit length (8.17+) | `EVAL bits = BIT_LENGTH(name)` |
| `BYTE_LENGTH(s)` | Byte length (8.17+) | `EVAL bytes = BYTE_LENGTH(name)` |
| `CHUNK(field, settings)` | Split text into chunks (9.3+, preview) | `EVAL chunks = CHUNK(body, {"strategy":"word","max_chunk_size":50})` |
| `HASH(alg, s)` | Hash string (8.18/9.0+) | `EVAL h = HASH("SHA-256", msg)` |
| `MD5(s)` | MD5 hash (8.18/9.0+) | `EVAL h = MD5(content)` |
| `SHA1(s)` | SHA-1 hash (8.18/9.0+) | `EVAL h = SHA1(content)` |
| `SHA256(s)` | SHA-256 hash (8.18/9.0+) | `EVAL h = SHA256(content)` |
| `FROM_BASE64(s)` | Decode base64 | `EVAL decoded = FROM_BASE64(encoded)` |
| `TO_BASE64(s)` | Encode to base64 | `EVAL encoded = TO_BASE64(data)` |
| `URL_DECODE(s)` | URL-decode (9.2+) | `EVAL decoded = URL_DECODE(url)` |
| `URL_ENCODE(s)` | URL-encode (9.2+) | `EVAL encoded = URL_ENCODE(text)` |
| `URL_ENCODE_COMPONENT(s)` | URL-encode for URI components (9.2+) | `EVAL encoded = URL_ENCODE_COMPONENT(text)` |
| `JSON_EXTRACT(field, path)` | Extract value from JSON string (9.4+ preview; Serverless) | `EVAL name = JSON_EXTRACT(raw, "$.user.name")` |
**JSON_EXTRACT with \_source — flattened field workaround:**
ES|QL does not natively access `flattened` field sub-keys. Use `METADATA _source` with `JSON_EXTRACT` to reach inside
flattened objects. `_source` can be passed directly to `JSON_EXTRACT` — do not wrap it with `TO_STRING()`.
```esql
FROM logs-* METADATA _source
| EVAL provider = JSON_EXTRACT(_source, "$.cloud.provider")
| STATS count = COUNT(*) BY provider
```
This also works for any field that exists in the raw document but has no explicit mapping.
---
## Math Functions
| Function | Description | Example |
| ------------------------------- | --------------------------------- | ---------------------------------------- |
| `ABS(n)` | Absolute value | `EVAL abs_val = ABS(diff)` |
| `ROUND(n, decimals)` | Round | `EVAL rounded = ROUND(price, 2)` |
| `FLOOR(n)` | Round down | `EVAL floored = FLOOR(value)` |
| `CEIL(n)` | Round up | `EVAL ceiled = CEIL(value)` |
| `SQRT(n)` | Square root | `EVAL root = SQRT(area)` |
| `POW(base, exp)` | Power | `EVAL squared = POW(x, 2)` |
| `EXP(n)` | e^n | `EVAL e_power = EXP(x)` |
| `LOG(n)` | Natural log | `EVAL ln = LOG(value)` |
| `LOG10(n)` | Log base 10 | `EVAL log = LOG10(value)` |
| `SIN(n)`, `COS(n)`, `TAN(n)` | Trig functions | `EVAL sine = SIN(angle)` |
| `ASIN(n)`, `ACOS(n)`, `ATAN(n)` | Inverse trig | `EVAL angle = ASIN(ratio)` |
| `PI()` | Pi constant | `EVAL circumference = 2 * PI() * radius` |
| `E()` | Euler's number | `EVAL e = E()` |
| `SIGNUM(n)` | Sign (-1, 0, 1) | `EVAL sign = SIGNUM(value)` |
| `GREATEST(a, b, ...)` | Maximum of values | `EVAL max = GREATEST(a, b, c)` |
| `LEAST(a, b, ...)` | Minimum of values | `EVAL min = LEAST(a, b, c)` |
| `ATAN2(y, x)` | Two-argument arctangent | `EVAL angle = ATAN2(y, x)` |
| `CBRT(n)` | Cube root | `EVAL root = CBRT(volume)` |
| `COSH(n)` | Hyperbolic cosine | `EVAL ch = COSH(x)` |
| `SINH(n)` | Hyperbolic sine | `EVAL sh = SINH(x)` |
| `TANH(n)` | Hyperbolic tangent | `EVAL th = TANH(x)` |
| `HYPOT(a, b)` | Hypotenuse | `EVAL h = HYPOT(x, y)` |
| `TAU()` | Tau (2\*Pi) | `EVAL t = TAU()` |
| `COPY_SIGN(mag, sign)` | Copy sign (8.19/9.1+) | `EVAL v = COPY_SIGN(mag, sign)` |
| `SCALB(d, scaleFactor)` | Scale by power of 2 (8.19/9.1+) | `EVAL v = SCALB(d, 3)` |
| `ROUND_TO(n, v1, v2, ...)` | Round to fixed points (8.19/9.1+) | `EVAL r = ROUND_TO(val, 0, 10, 50, 100)` |
---
## Date/Time Functions
| Function | Description | Example |
| ---------------------------- | ------------------------ | -------------------------------------------- |
| `NOW()` | Current timestamp | `WHERE @timestamp > NOW() - 1 hour` |
| `DATE_TRUNC(interval, date)` | Truncate to interval | `EVAL hour = DATE_TRUNC(1 hour, @timestamp)` |
| `DATE_EXTRACT(part, date)` | Extract part | `EVAL month = DATE_EXTRACT(month, date)` |
| `DATE_FORMAT(pattern, date)` | Format date | `EVAL str = DATE_FORMAT("yyyy-MM-dd", date)` |
| `DATE_PARSE(pattern, str)` | Parse date string | `EVAL dt = DATE_PARSE("yyyy-MM-dd", str)` |
| `DATE_DIFF(unit, d1, d2)` | Difference | `EVAL days = DATE_DIFF("day", start, end)` |
| `DAY_NAME(date)` | Weekday name (9.2+) | `EVAL day = DAY_NAME(@timestamp)` |
| `MONTH_NAME(date)` | Month name (9.2+) | `EVAL month = MONTH_NAME(@timestamp)` |
| `TRANGE(duration)` | Time range filter (9.3+) | `WHERE TRANGE(1 hour)` |
**Time units:** `millisecond`, `second`, `minute`, `hour`, `day`, `week`, `month`, `year`
**Timespan literals:** `1 day`, `2 hours`, `30 minutes`, `1 week`
---
## Type Conversion Functions
| Function | Description | Example |
| ------------------------------- | -------------------------------------------------- | -------------------------------------------- |
| `TO_STRING(v)` | Convert to string | `EVAL str = TO_STRING(num)` |
| `TO_INTEGER(v)` | Convert to integer | `EVAL int = TO_INTEGER(str)` |
| `TO_LONG(v)` | Convert to long | `EVAL lng = TO_LONG(str)` |
| `TO_DOUBLE(v)` | Convert to double | `EVAL dbl = TO_DOUBLE(str)` |
| `TO_BOOLEAN(v)` | Convert to boolean | `EVAL bool = TO_BOOLEAN(str)` |
| `TO_DATETIME(v)` | Convert to datetime | `EVAL dt = TO_DATETIME(str)` |
| `TO_IP(v)` | Convert to IP | `EVAL ip = TO_IP(str)` |
| `TO_VERSION(v)` | Convert to version | `EVAL ver = TO_VERSION(str)` |
| `TO_UNSIGNED_LONG(v)` | Convert to unsigned long | `EVAL ul = TO_UNSIGNED_LONG(str)` |
| `TO_DATEPERIOD(v)` | Convert to date period (8.16+) | `EVAL dp = TO_DATEPERIOD("1 day")` |
| `TO_TIMEDURATION(v)` | Convert to time duration (8.16+) | `EVAL td = TO_TIMEDURATION("1h")` |
| `TO_DATE_NANOS(v)` | Convert to nanosecond date (8.18/9.0+) | `EVAL ns = TO_DATE_NANOS(str)` |
| `TO_DEGREES(n)` | Radians to degrees | `EVAL deg = TO_DEGREES(rad)` |
| `TO_RADIANS(n)` | Degrees to radians | `EVAL rad = TO_RADIANS(deg)` |
| `TO_GEOPOINT(v)` | Convert to geo_point | `EVAL pt = TO_GEOPOINT(str)` |
| `TO_GEOSHAPE(v)` | Convert to geo_shape | `EVAL shape = TO_GEOSHAPE(wkt)` |
| `TO_CARTESIANPOINT(v)` | Convert to cartesian_point | `EVAL pt = TO_CARTESIANPOINT(str)` |
| `TO_CARTESIANSHAPE(v)` | Convert to cartesian_shape | `EVAL shape = TO_CARTESIANSHAPE(str)` |
| `TO_AGGREGATE_METRIC_DOUBLE(v)` | Convert to aggregate_metric_double (9.2+, preview) | `EVAL amd = TO_AGGREGATE_METRIC_DOUBLE(val)` |
| `TO_DENSE_VECTOR(v)` | Convert to dense_vector (9.2+, preview) | `EVAL vec = TO_DENSE_VECTOR(arr)` |
| `TO_GEOHASH(v)` | Convert to geohash (9.2+, preview) | `EVAL hash = TO_GEOHASH(str)` |
| `TO_GEOHEX(v)` | Convert to geohex (9.2+, preview) | `EVAL hex = TO_GEOHEX(str)` |
| `TO_GEOTILE(v)` | Convert to geotile (9.2+, preview) | `EVAL tile = TO_GEOTILE(str)` |
---
## IP Functions
| Function | Description | Example |
| ----------------------------- | ---------------------------------- | ------------------------------------------- |
| `CIDR_MATCH(ip, block1, ...)` | Test if IP is in one or more CIDRs | `WHERE CIDR_MATCH(source.ip, "10.0.0.0/8")` |
| `IP_PREFIX(ip, v4len, v6len)` | Get the network prefix of an IP | `EVAL prefix = IP_PREFIX(ip, 24, 64)` |
| `TO_IP(v)` | Convert to IP type | `EVAL ip = TO_IP(ip_string)` |
**Examples:**
```esql
// Filter to private network ranges
FROM logs-*
| WHERE CIDR_MATCH(source.ip, "10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16")
// Group traffic by /24 subnet
FROM network_logs
| STATS bytes = SUM(bytes_transferred) BY subnet = IP_PREFIX(source.ip, 24, 64)
| SORT bytes DESC
```
---
## Spatial Functions
| Function | Description | Example |
| --------------------------------------- | ----------------------------------- | --------------------------------------------------------- |
| `ST_DISTANCE(p1, p2)` | Distance between points | `EVAL dist = ST_DISTANCE(loc, TO_GEOPOINT("POINT(0 0)"))` |
| `ST_INTERSECTS(g1, g2)` | Geometries intersect | `WHERE ST_INTERSECTS(geo, boundary)` |
| `ST_DISJOINT(g1, g2)` | Geometries don't intersect | `WHERE ST_DISJOINT(geo, zone)` |
| `ST_CONTAINS(g1, g2)` | g1 contains g2 | `WHERE ST_CONTAINS(region, point)` |
| `ST_WITHIN(g1, g2)` | g1 within g2 | `WHERE ST_WITHIN(point, region)` |
| `ST_X(point)` | X coordinate / longitude | `EVAL lon = ST_X(location)` |
| `ST_Y(point)` | Y coordinate / latitude | `EVAL lat = ST_Y(location)` |
| `ST_ENVELOPE(geo)` | Bounding box (8.18/9.0+) | `EVAL bbox = ST_ENVELOPE(shape)` |
| `ST_XMAX(geo)` | Max X / longitude (8.18/9.0+) | `EVAL max_lon = ST_XMAX(shape)` |
| `ST_XMIN(geo)` | Min X / longitude (8.18/9.0+) | `EVAL min_lon = ST_XMIN(shape)` |
| `ST_YMAX(geo)` | Max Y / latitude (8.18/9.0+) | `EVAL max_lat = ST_YMAX(shape)` |
| `ST_YMIN(geo)` | Min Y / latitude (8.18/9.0+) | `EVAL min_lat = ST_YMIN(shape)` |
| `ST_GEOHASH(point, prec)` | Encode as geohash (9.2+) | `EVAL hash = ST_GEOHASH(location, 5)` |
| `ST_GEOHEX(point, prec)` | Encode as geohex (9.2+) | `EVAL hex = ST_GEOHEX(location, 5)` |
| `ST_GEOTILE(point, prec)` | Encode as geotile (9.2+) | `EVAL tile = ST_GEOTILE(location, 10)` |
| `ST_NPOINTS(geo)` | Number of points | `EVAL n = ST_NPOINTS(shape)` |
| `ST_SIMPLIFY(geo, tol)` | Simplify geometry | `EVAL simple = ST_SIMPLIFY(shape, 100)` |
| `ST_DIMENSION(geo)` | Dimension (0/1/2) (9.4+) | `EVAL dim = ST_DIMENSION(shape)` |
| `ST_GEOMETRYTYPE(geo)` | Geometry type string (9.4+) | `EVAL gtype = ST_GEOMETRYTYPE(shape)` |
| `ST_ISEMPTY(geo)` | True if empty (9.4+) | `WHERE NOT ST_ISEMPTY(shape)` |
| `ST_BUFFER(geo, dist)` | Buffer around geometry (9.4+) | `EVAL area = ST_BUFFER(point, 1000)` |
| `ST_SIMPLIFYPRESERVETOPOLOGY(geo, tol)` | Simplify preserving topology (9.4+) | `EVAL s = ST_SIMPLIFYPRESERVETOPOLOGY(shape, 100)` |
---
## Dense Vector Functions
For vector search and similarity operations on `dense_vector` and `semantic_text` fields.
| Function | Description | Example |
| -------------------------------- | -------------------------------- | --------------------------------------------- |
| `KNN(field, k, query_vec)` | K-nearest neighbor search (9.2+) | `WHERE KNN(embedding, 10, query_vector)` |
| `TEXT_EMBEDDING(endpoint, text)` | Generate embeddings (9.3+) | `EVAL vec = TEXT_EMBEDDING("my-model", text)` |
| `V_COSINE(v1, v2)` | Cosine similarity (9.3+) | `EVAL sim = V_COSINE(vec1, vec2)` |
| `V_DOT_PRODUCT(v1, v2)` | Dot product (9.3+) | `EVAL dot = V_DOT_PRODUCT(vec1, vec2)` |
| `V_L1_NORM(v1, v2)` | L1 / Manhattan distance (9.3+) | `EVAL l1 = V_L1_NORM(vec1, vec2)` |
| `V_L2_NORM(v1, v2)` | L2 / Euclidean distance (9.3+) | `EVAL l2 = V_L2_NORM(vec1, vec2)` |
| `V_HAMMING(v1, v2)` | Hamming distance (9.3+) | `EVAL h = V_HAMMING(vec1, vec2)` |
---
## Multivalue Functions
For handling fields with multiple values.
| Function | Description | Example |
| ------------------------------------- | ------------------------------------------------ | ----------------------------------------------- |
| `MV_COUNT(field)` | Count values | `EVAL n = MV_COUNT(tags)` |
| `MV_FIRST(field)` | First value | `EVAL first_val = MV_FIRST(values)` |
| `MV_LAST(field)` | Last value | `EVAL last_val = MV_LAST(values)` |
| `MV_MIN(field)` | Minimum | `EVAL min = MV_MIN(scores)` |
| `MV_MAX(field)` | Maximum | `EVAL max = MV_MAX(scores)` |
| `MV_SUM(field)` | Sum | `EVAL total = MV_SUM(amounts)` |
| `MV_AVG(field)` | Average | `EVAL avg = MV_AVG(scores)` |
| `MV_MEDIAN(field)` | Median | `EVAL med = MV_MEDIAN(values)` |
| `MV_CONCAT(field, delim)` | Join to string | `EVAL str = MV_CONCAT(tags, ", ")` |
| `MV_DEDUPE(field)` | Remove duplicates | `EVAL unique = MV_DEDUPE(tags)` |
| `MV_SORT(field)` | Sort values | `EVAL sorted = MV_SORT(values)` |
| `MV_SLICE(field, start, end)` | Slice array | `EVAL slice = MV_SLICE(arr, 0, 3)` |
| `MV_ZIP(f1, f2)` | Zip arrays (both must be keyword/text) | `EVAL zipped = MV_ZIP(keys, values)` |
| `MV_APPEND(f1, f2)` | Concatenate MVs | `EVAL all = MV_APPEND(tags1, tags2)` |
| `MV_CONTAINS(f1, f2)` | All values in f2 present in f1 (9.2+) | `EVAL has = MV_CONTAINS(perms, required)` |
| `MV_INTERSECTION(f1, f2)` | Values present in both (9.3+) | `EVAL common = MV_INTERSECTION(a, b)` |
| `MV_INTERSECTS(f1, f2)` | Any value in f2 present in f1 (9.4+; Serverless) | `EVAL overlap = MV_INTERSECTS(a, b)` |
| `MV_UNION(f1, f2)` | Deduplicated union (9.4+; Serverless) | `EVAL merged = MV_UNION(a, b)` |
| `MV_DIFFERENCE(f1, f2)` | Values in f1 not in f2 (9.4+; Serverless) | `EVAL diff = MV_DIFFERENCE(a, b)` |
| `MV_PERCENTILE(field, p)` | Percentile of MV | `EVAL p95 = MV_PERCENTILE(vals, 95)` |
| `MV_PSERIES_WEIGHTED_SUM(field, p)` | P-series weighted sum (both args must be double) | `EVAL ws = MV_PSERIES_WEIGHTED_SUM(vals, 2.0)` |
| `MV_MEDIAN_ABSOLUTE_DEVIATION(field)` | MAD of MV | `EVAL mad = MV_MEDIAN_ABSOLUTE_DEVIATION(vals)` |
---
## Conditional Functions
| Function | Description | Example |
| --------------------------------- | ------------------------ | ---------------------------------------------------------- |
| `CASE(cond1, val1, ..., default)` | Conditional | `EVAL level = CASE(score > 90, "A", score > 80, "B", "C")` |
| `COALESCE(v1, v2, ...)` | First non-null | `EVAL name = COALESCE(nickname, full_name, "Unknown")` |
| `field IS NULL` | Check null | `WHERE error IS NULL` |
| `field IS NOT NULL` | Check not null | `WHERE response IS NOT NULL` |
| `CLAMP(val, min, max)` | Clamp to range (9.3+) | `EVAL clamped = CLAMP(score, 0, 100)` |
| `CLAMP_MIN(val, min)` | Clamp lower bound (9.3+) | `EVAL v = CLAMP_MIN(score, 0)` |
| `CLAMP_MAX(val, max)` | Clamp upper bound (9.3+) | `EVAL v = CLAMP_MAX(score, 100)` |
---
## Full-Text Search Functions
For text search with analyzer support (available since 8.17+).
### MATCH
Basic text search.
```esql
FROM articles
| WHERE MATCH(content, "elasticsearch query")
// With options
FROM docs
| WHERE MATCH(title, "search", {"operator": "AND"})
```
### MATCH (colon operator)
Shorthand for MATCH.
```esql
FROM logs
| WHERE message : "error"
```
### MATCH_PHRASE
Exact phrase matching. Returns documents where the field contains the exact phrase in order. GA in 8.19/9.1.
```esql
FROM articles
| WHERE MATCH_PHRASE(title, "quick brown fox")
// With slop to allow words between phrase terms
FROM articles
| WHERE MATCH_PHRASE(content, "elasticsearch query", slop=2)
```
### QSTR (Query String)
Complex queries using query string syntax.
```esql
FROM logs
| WHERE QSTR("status:error AND (type:critical OR type:warning)")
```
### KQL
Kibana Query Language support.
```esql
FROM logs
| WHERE KQL("message: error and host.name: server*")
```
### DECAY
Distance-based scoring that decays from an origin point. Works with numeric, date, and geo fields (9.2+).
```esql
FROM events METADATA _score
| EVAL decay_score = DECAY("gauss", @timestamp, origin=NOW(), scale="7 days")
```
### SCORE
Returns the relevance score for a row (9.3+).
```esql
FROM articles
| WHERE MATCH(content, "elasticsearch")
| EVAL relevance = SCORE()
| SORT relevance DESC
```
### TOP_SNIPPETS
Extracts best matching snippets from text fields (9.3+).
```esql
FROM articles
| WHERE MATCH(content, "elasticsearch query")
| EVAL snippet = TOP_SNIPPETS(content, "elasticsearch query")
```
### Relevance Scoring
```esql
FROM articles METADATA _score
| WHERE MATCH(content, "elasticsearch")
| SORT _score DESC
| LIMIT 10
```
---
## Operators
### Comparison Operators
- `==` Equal
- `!=` Not equal
- `<`, `<=`, `>`, `>=` Comparison
- `IS NULL`, `IS NOT NULL` Null checks
### Logical Operators
- `AND` Logical AND
- `OR` Logical OR
- `NOT` Logical NOT
### Pattern Matching
- `LIKE` Wildcard pattern (`*` zero or more chars, `?` single char)
- `RLIKE` Regular expression
- `IN` Value in list
**Examples:**
```esql
WHERE name LIKE "John*"
WHERE email RLIKE ".*@example\\.com"
WHERE status IN ("active", "pending")
WHERE NOT (status == "deleted")
```
### Arithmetic Operators
- `+`, `-`, `*`, `/`, `%` (modulo)
---
## Syntax Details
### Comments
```esql
// Single line comment
/* Multi-line
comment */
FROM logs // inline comment
| WHERE status == 200
```
### String Literals
```esql
// Standard strings — use backslash escapes
ROW msg = "line1\nline2", path = "C:\\Users\\data"
// Triple-quoted strings — no escaping needed, can contain single quotes
ROW pattern = """field "with quotes" and \backslashes"""
```
### Numeric Literals
```esql
// Integer, decimal, scientific notation
ROW a = 123, b = 0.23, c = 2E3, d = 1.2e-3
```
### Identifiers and Escaping
Field names that don't start with a letter, `_`, or `@` must be enclosed in backticks. A literal backtick inside a
backtick-quoted identifier is escaped by doubling it.
```esql
// Backtick-quoted identifiers for special field names
FROM index | EVAL val = `1.field`
// Escaping backticks within identifiers
FROM index | EVAL val = `field``name`
```
### Timespan Literals
Supported units: `millisecond` (`ms`), `second` (`s`), `minute` (`min`), `hour` (`h`), `day` (`d`), `week` (`w`),
`month` (`mo`), `quarter` (`q`), `year` (`yr`). Plural `s` is always accepted. Whitespace between number and unit is
optional.
```esql
// Timespans are used in expressions, not as standalone values
FROM logs-*
| WHERE @timestamp > NOW() - 1 day
| STATS hourly = COUNT(*) BY bucket = DATE_TRUNC(30 minutes, @timestamp)
```
---
## Metadata Fields
Access document metadata with the `METADATA` directive on the `FROM` command. Once enabled, metadata fields behave like
regular index fields.
| Field | Type | Description |
| ------------- | ------- | -------------------------------------------------------------------------------------- |
| `_id` | keyword | Unique document ID |
| `_index` | keyword | Index name |
| `_version` | long | Document version number |
| `_score` | float | Query relevance score (updated by full-text search functions) |
| `_ignored` | keyword | Fields that were ignored when the document was indexed |
| `_index_mode` | keyword | Index mode (`standard`, `lookup`, `logsdb`, `time_series` etc.) |
| `_source` | special | Original JSON document body. Use `JSON_EXTRACT` to access flattened or unmapped fields |
| `_size` | integer | Document size in bytes (9.4+; requires `mapper-size` plugin) |
```esql
FROM logs METADATA _id, _index, _version
| KEEP _id, message
// Use _score for relevance-ranked search
FROM articles METADATA _score
| WHERE MATCH(content, "elasticsearch")
| SORT _score DESC
| LIMIT 10
```
---
## Best Practices
1. **Always use LIMIT** to avoid returning too many rows
2. **Filter early** with WHERE to reduce data processed
3. **Use KEEP** to select only needed columns
4. **Use appropriate data types** for comparisons
5. **Use STATS for aggregations** instead of returning all rows
6. **Use DATE_TRUNC for time-based grouping**
7. **Leverage full-text functions** (MATCH, QSTR) for text search - much faster than LIKE/RLIKE
---
## Example Queries
### Log Analysis
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 24 hours
| WHERE status_code >= 400
| STATS error_count = COUNT(*) BY status_code, host.name
| SORT error_count DESC
| LIMIT 20
```
### User Activity
```esql
FROM user_events
| WHERE event_type == "login"
| EVAL hour = DATE_TRUNC(1 hour, @timestamp)
| STATS logins = COUNT(*), unique_users = COUNT_DISTINCT(user_id) BY hour
| SORT hour DESC
```
### Performance Metrics
```esql
FROM metrics-*
| WHERE @timestamp > NOW() - 1 hour
| STATS
avg_response = AVG(response_time),
p95_response = PERCENTILE(response_time, 95),
max_response = MAX(response_time)
BY service.name
| SORT avg_response DESC
```
**Time series version (9.2+):** For TSDS indices, use `TS` to access time series aggregation functions:
```esql
TS metrics-*
| WHERE @timestamp > NOW() - 1 hour
| STATS
SUM(RATE(request_count)) BY service.name, TBUCKET(5 minutes)
| SORT service.name
```
### Text Search with Scoring
```esql
FROM articles METADATA _score
| WHERE MATCH(content, "machine learning")
| KEEP title, author, _score
| SORT _score DESC
| LIMIT 10
```
### Data Transformation
```esql
FROM raw_logs
| GROK message "%{IP:client_ip} - %{WORD:method} %{URIPATHPARAM:path} %{NUMBER:status:int}"
| EVAL is_error = status >= 400
| STATS
total = COUNT(*),
errors = COUNT(CASE(is_error, 1, null))
BY client_ip
| EVAL error_rate = ROUND(errors * 100.0 / total, 2)
| SORT error_rate DESC
```
references/esql-search-strategy.md
# ES|QL Relevance Search Strategy
This guide teaches an agent how to perform high-quality relevance search on content indices using ES|QL.
## Table of Contents
- [Scope](#scope)
- [Quick Strategy Rules](#quick-strategy-rules)
- [Standard Workflow](#standard-workflow)
- [Multi-Stage Retrieval Pattern](#multi-stage-retrieval-pattern)
- [Retrieval Strategies](#retrieval-strategies)
- [Lexical Retrieval](#lexical-retrieval)
- [Semantic Retrieval](#semantic-retrieval)
- [Vector Retrieval](#vector-retrieval)
- [Hybrid Retrieval](#hybrid-retrieval)
- [Semantic Intent Without Semantic Field](#semantic-intent-without-semantic-field)
- [Reranking Stage](#reranking-stage)
- [Semantic Reranking](#semantic-reranking)
- [Embedding Similarity Rescore](#embedding-similarity-rescore)
- [Phrase Search](#phrase-search)
- [Multi-Index Search](#multi-index-search)
- [Weak Result Recovery](#weak-result-recovery)
- [Mandatory Rules](#mandatory-rules)
- [Final Decision Process](#final-decision-process)
## Scope
This guidance is designed for:
- document search
- knowledge bases
- articles
- documentation
- product content
It is **not** intended for logs or observability datasets.
Agents should always follow a **multi-stage retrieval pattern**:
```text
retrieve → (optional fuse) → rerank
```
Start with inexpensive retrieval and apply expensive ranking only to a limited candidate set.
## Quick Strategy Rules
| Situation | Strategy |
| ------------------------------------- | ----------------------------------- |
| `semantic_text` field exists | semantic `MATCH` |
| only `text` fields exist | lexical `MATCH` |
| `dense_vector` field exists | `KNN` retrieval |
| lexical + semantic fields exist | hybrid retrieval |
| semantic intent but no semantic field | lexical → `RERANK` |
| exact wording required | `MATCH_PHRASE` |
| multiple indices | branch search using `FORK` + `FUSE` |
## Standard Workflow
### 1. Identify Indices
Use the narrowest index pattern that satisfies the request.
```esql
// Single index
FROM knowledge-base
// Multiple indices
FROM docs-*,articles-*
```
### 2. Inspect Mappings
Before writing queries, identify searchable fields.
Preferred fields for content search:
```text
title
name
subject
summary
body
content
description
text
```
### Field Types
| Field type | Purpose |
| --------------- | ------------------ |
| `semantic_text` | semantic retrieval |
| `text` | lexical retrieval |
| `dense_vector` | vector retrieval |
| `keyword` | filtering only |
**Never** use `keyword` fields for natural language search.
### Preferred Field Ranking
1. `title`
2. `summary`
3. `body` / `content`
4. `description`
5. `text`
Short fields provide **precision**. Long fields provide **recall**.
### 3. Choose Retrieval Strategy
Use the [Quick Strategy Rules](#quick-strategy-rules) table to select the right approach based on the available field
types. Then follow the matching retrieval pattern below.
## Multi-Stage Retrieval Pattern
Always follow this structure:
1. retrieve candidate documents
2. optionally combine retrieval strategies
3. rerank candidates
Typical candidate size: **50–200 documents**. Use smaller sets when fields are strong. Use larger sets when recall is
important.
## Retrieval Strategies
### Lexical Retrieval
Use when only `text` fields exist.
```esql
FROM my-index METADATA _score
| WHERE MATCH(title, ?query) OR MATCH(body, ?query)
| SORT _score DESC
| LIMIT 100
```
Guidelines:
- **one field per `MATCH`** — the first argument is a **single mapped field** (`title`, `content`), not a quoted
pseudo-field like `"title,content"` and not two identifiers before the query (`MATCH(title, body, "q")` is invalid);
use `MATCH(a, ?query) OR MATCH(b, ?query)` or `QSTR` / `KQL` for multi-field search
- a single `MATCH` on the main content field is often sufficient
- add additional fields (title, summary) only when recall is poor or the user explicitly asks for broader search
- title fields provide precision, body fields provide recall
- prefer `MATCH` over `LIKE` or `RLIKE`
### Semantic Retrieval
Use when a `semantic_text` field exists. `MATCH` is **required** for `semantic_text` fields — it automatically performs
vector-based semantic search. No separate function is needed.
```esql
FROM my-index METADATA _score
| WHERE MATCH(semantic_body, ?query)
| SORT _score DESC
| LIMIT 100
```
Prefer the semantic field representing the **main document body**.
### Vector Retrieval
> **Version:** `KNN` is 9.2+ (preview). `TEXT_EMBEDDING` is 9.3+. Verify cluster version via `esql-version-history.md`
> before using these functions. For clusters below 9.2, use semantic retrieval with `semantic_text` + `MATCH` instead.
Use when embeddings are stored as `dense_vector`. `KNN` can also target `semantic_text` fields.
```esql
FROM my-index METADATA _score
| WHERE KNN(content_embedding, TEXT_EMBEDDING(?query, "embedding_endpoint"))
| SORT _score DESC
| LIMIT 100
```
Rules:
- the query embedding model must match the document embeddings
- always retrieve a bounded candidate set
- avoid embedding operations across the full index
### Hybrid Retrieval
> **Version:** `FORK` is 8.19/9.1+ (preview). `FUSE` is 9.2+ (preview). On clusters below 9.2, use lexical retrieval
> followed by `RERANK` as a fallback. On clusters below 8.19/9.1, use a single-branch `MATCH` with `RERANK`.
Use when both lexical and semantic fields exist.
```esql
FROM my-index METADATA _id, _index, _score
| FORK
(
WHERE MATCH(title, ?query) OR MATCH(body, ?query)
| SORT _score DESC
| LIMIT 100
)
(
WHERE MATCH(semantic_body, ?query)
| SORT _score DESC
| LIMIT 100
)
| FUSE
| SORT _score DESC
| LIMIT 100
```
Hybrid retrieval improves both **recall and precision**.
Pipeline:
```text
retrieve lexically
retrieve semantically
fuse
rerank
```
## Semantic Intent Without Semantic Field
If semantic search is requested but the index lacks `semantic_text`:
1. retrieve candidates lexically
2. rerank results semantically
```esql
FROM my-index METADATA _score
| WHERE MATCH(title, ?query) OR MATCH(body, ?query) OR MATCH(summary, ?query)
| SORT _score DESC
| LIMIT 100
```
**Never** stop at "semantic search unavailable" without attempting lexical retrieval.
## Reranking Stage
> **Version:** `RERANK` is 9.2+ (preview). On clusters below 9.2, skip the reranking stage and rely on initial retrieval
> scoring. For clusters below 9.2, sorting by `_score DESC` after `MATCH` provides BM25 or vector-based ordering.
Always rerank a bounded candidate set.
### Semantic Reranking
```esql
FROM my-index METADATA _score
| WHERE MATCH(body, ?query)
| SORT _score DESC
| LIMIT 100
| RERANK body ON ?query
| SORT _score DESC
| LIMIT 20
```
Use when:
- lexical retrieval produced good candidates
- semantic ranking improves ordering
### Embedding Similarity Rescore
> **Version:** `V_COSINE` and other vector similarity functions are 9.3+ (preview). Verify cluster version via
> `esql-version-history.md` before using these functions.
Use when document embeddings exist.
```esql
FROM my-index METADATA _score
| WHERE MATCH(body, ?query)
| SORT _score DESC
| LIMIT 100
| EVAL q = TEXT_EMBEDDING(?query, "embedding_endpoint")
| EVAL semantic_score = V_COSINE(content_embedding, q)
| SORT semantic_score DESC
| LIMIT 20
```
Rules:
- compute the query embedding once
- compare only against candidate documents
- avoid vector scans across entire indices
## Phrase Search
Use when exact wording matters.
```esql
FROM my-index METADATA _score
| WHERE MATCH_PHRASE(body, ?phrase)
| SORT _score DESC
| LIMIT 20
```
Typical cases:
- product names
- quoted text
- error messages
- legal phrases
## Multi-Index Search
When querying multiple indices:
1. inspect mappings
2. confirm compatible fields
3. branch queries when schemas differ
4. fuse results
5. rerank candidates
> **`_index` is not implicit:** You may use `_index` in queries (for example `WHERE _index LIKE "docs-%"`) **only** if
> the `FROM` clause requests it via `METADATA _index` (typically `METADATA _id, _index, _score` for `FORK`/`FUSE`). If
> `_index` is omitted from `METADATA`, the column does not exist in the pipeline — the query fails with an unknown-field
> error. Prefer the [compatible-schemas](#compatible-schemas) pattern when you do not need per-index branching.
### Compatible Schemas
```esql
FROM docs-*,articles-* METADATA _score
| WHERE MATCH(title, ?query) OR MATCH(body, ?query)
| SORT _score DESC
| LIMIT 20
```
### Different Schemas
```esql
FROM docs-*,support-* METADATA _id, _index, _score
| FORK
(
WHERE _index LIKE "docs-%"
| WHERE MATCH(body, ?query)
| SORT _score DESC
| LIMIT 100
)
(
WHERE _index LIKE "support-%"
| WHERE MATCH(content, ?query)
| SORT _score DESC
| LIMIT 100
)
| FUSE
| SORT _score DESC
| LIMIT 20
```
Always keep the pattern simple:
```text
retrieve per index family → fuse → rerank
```
## Weak Result Recovery
If results are weak:
1. search additional content fields
2. increase candidate size
3. apply semantic reranking
4. switch to hybrid retrieval
5. inspect mappings for stronger fields
6. split multi-index search by index family
Avoid jumping directly to expensive ranking.
## Mandatory Rules
**Always:**
- inspect mappings first
- retrieve candidates before reranking
- limit candidate sets to ~50–200
- use `_score` for ranking
- prefer content fields over metadata
- follow retrieve → fuse → rerank
**Never:**
- search natural language in `keyword` fields
- run embedding operations across entire indices
- skip the retrieval stage
- use `LIKE` for relevance search
- dump full mappings unless necessary
## Default Query Patterns
Use the examples from [Retrieval Strategies](#retrieval-strategies) as starting templates:
- **Lexical** — see [Lexical Retrieval](#lexical-retrieval)
- **Semantic** — see [Semantic Retrieval](#semantic-retrieval)
- **Hybrid** — see [Hybrid Retrieval](#hybrid-retrieval), then add a [Reranking Stage](#reranking-stage)
Use the hybrid pattern when both lexical and semantic fields exist.
## Final Decision Process
Follow this order:
1. inspect mappings
2. choose best retrieval field family
3. retrieve candidates
4. fuse if necessary
5. rerank
6. return results
Mental model:
```text
retrieve → fuse → rerank
```
references/esql-search.md
# ES|QL Full-Text Search Reference
Full-text search in ES|QL uses analyzer-aware functions for fast, relevance-ranked text retrieval. Use these instead of
`LIKE`/`RLIKE` for searching natural language content — they are significantly faster on large datasets.
> **Version:** `MATCH` and `QSTR` were introduced in 8.17 (preview) and became GA in 8.19/9.1. `KQL` and scoring via
> `METADATA _score` were added in 8.18/9.0 (GA in 8.19/9.1). `MATCH_PHRASE` is 8.19/9.1+. See the version column in the
> [Functions Overview](#functions-overview) table and [esql-version-history.md](esql-version-history.md) for
> per-function availability.
## Table of Contents
- [When to Use Full-Text Search](#when-to-use-full-text-search)
- [Functions Overview](#functions-overview)
- [MATCH](#match)
- [Colon Operator (`:`)](#colon-operator-)
- [MATCH_PHRASE](#match_phrase)
- [QSTR (Query String)](#qstr-query-string)
- [KQL (Kibana Query Language)](#kql-kibana-query-language)
- [Relevance Scoring](#relevance-scoring)
- [Semantic Search](#semantic-search)
- [FORK / FUSE (Hybrid Search)](#fork--fuse-hybrid-search)
- [LOOKUP JOIN](#lookup-join)
- [Parameters in ES|QL](#parameters-in-esql)
- [Advanced Search Functions (Preview)](#advanced-search-functions-preview)
- [Placement Rules](#placement-rules)
- [Common Patterns](#common-patterns)
- [Full-Text Search vs Pattern Matching](#full-text-search-vs-pattern-matching)
- [Interaction with Text Analyzers](#interaction-with-text-analyzers)
---
## When to Use Full-Text Search
Use full-text search functions (`MATCH`, `QSTR`, `KQL`, `MATCH_PHRASE`) when:
- Searching natural-language text (log messages, descriptions, titles, comments)
- Relevance ranking matters (most relevant results first)
- You need analyzer features on `text` fields: case-insensitive matching, stemming, synonyms, fuzzy matching (note:
analyzer features do **not** apply to `semantic_text` fields)
- Searching multivalued text fields
- Performance matters on large datasets
Use `LIKE`/`RLIKE` instead when:
- Pattern-matching on exact (keyword) values: file paths, URLs, status codes
- You need structural regex matching not covered by query string syntax
- Working on small datasets where analyzer support is unnecessary
---
## Functions Overview
| Function | Use Case | Version (GA) |
| ----------------------------- | ------------------------------------- | ------------ |
| `MATCH(field, query)` | Single-field text search | 9.1 |
| `field : "query"` | Shorthand for MATCH (no options) | 9.1 |
| `MATCH_PHRASE(field, phrase)` | Exact phrase matching (word order) | 9.1 |
| `QSTR(query_string)` | Multi-field search with Lucene syntax | 9.1 |
| `KQL(kql_string)` | Kibana Query Language queries | 9.1 |
---
## MATCH
Single-field text search. Equivalent to the Query DSL `match` query.
### Syntax
```esql
MATCH(field, query)
MATCH(field, query, {"option": value})
```
> **One field per `MATCH`:** The first argument must be a **single field from the index mapping** (an identifier such as
> `title` or `content`), not a string literal and not a comma-separated list. **`MATCH("title,content", "q")` is
> invalid** — that is not a real field name. To search several text fields, use separate calls combined with `OR` (for
> example `MATCH(title, "q") OR MATCH(body, "q")`) or use `QSTR` / `KQL` for a multi-field query string.
> **`MATCH(title, body, "phrase")` is invalid** — the second argument is the query text; the optional third is the
> options map, not another field.
### Basic Examples
```esql
// Simple text search
FROM logs-* METADATA _score
| WHERE MATCH(message, "connection timeout")
| SORT _score DESC
| LIMIT 100
// Search with AND operator (all terms must match)
FROM articles METADATA _score
| WHERE MATCH(title, "elasticsearch query language", {"operator": "AND"})
| SORT _score DESC
| LIMIT 20
// Fuzzy matching for typo tolerance
FROM docs METADATA _score
| WHERE MATCH(content, "authentcation error", {"fuzziness": "AUTO"})
| SORT _score DESC
| LIMIT 50
```
### Named Parameters
All parameters are optional. Analyzer-related parameters (`analyzer`, `fuzziness`,
`auto_generate_synonyms_phrase_query`) only apply to `text` fields — they have no effect on `semantic_text` fields.
| Parameter | Type | Default | Description |
| ------------------------------------- | ------- | ------- | ----------------------------------------------------- |
| `operator` | keyword | `"OR"` | Boolean logic between terms: `"OR"` or `"AND"` |
| `fuzziness` | varies | none | Edit distance: `"AUTO"`, `0`, `1`, `2` |
| `analyzer` | keyword | field's | Override the query-time analyzer (`text` fields only) |
| `boost` | float | `1.0` | Relevance score multiplier |
| `minimum_should_match` | varies | none | Min terms that must match (number or percentage) |
| `fuzzy_transpositions` | boolean | `true` | Allow ab→ba swaps in fuzzy matching |
| `max_expansions` | integer | — | Max terms for fuzzy/prefix expansion |
| `fuzzy_rewrite` | keyword | — | Rewrite method for fuzzy queries |
| `prefix_length` | integer | — | Leading chars unchanged in fuzzy matching |
| `lenient` | boolean | `false` | Ignore format errors (text query on numeric field) |
| `zero_terms_query` | keyword | — | Behavior when analyzer removes all tokens |
| `auto_generate_synonyms_phrase_query` | boolean | `true` | Auto-create phrase queries for multi-term synonyms |
### Supported Field Types
`text`, `semantic_text`, `keyword`, `boolean`, `date`, `date_nanos`, `double`, `integer`, `long`, `unsigned_long`, `ip`,
`version`.
> **Semantic search:** `MATCH` is **required** for searching `semantic_text` fields — it automatically performs semantic
> (vector) search instead of lexical search. No syntax change needed. Other full-text functions (`QSTR`, `KQL`,
> `MATCH_PHRASE`) do **not** support `semantic_text`.
---
## Colon Operator (`:`)
Shorthand for `MATCH()` with default parameters. Use for concise, simple searches.
### Syntax
```esql
field : "query"
```
### Examples
```esql
// Simple search
FROM logs-*
| WHERE message : "error"
// With scoring
FROM articles METADATA _score
| WHERE content : "machine learning"
| SORT _score DESC
| LIMIT 10
// Semantic search on semantic_text field
FROM knowledge_base METADATA _score
| WHERE semantic_content : "how to configure authentication"
| SORT _score DESC
| LIMIT 5
```
> **Limitation:** The colon operator does not support named parameters. Use `MATCH()` when you need `fuzziness`,
> `operator`, `analyzer`, or other options.
---
## MATCH_PHRASE
Matches documents where words appear in exact order. Equivalent to the Query DSL `match_phrase` query.
### Syntax
```esql
MATCH_PHRASE(field, phrase)
MATCH_PHRASE(field, phrase, {"option": value})
```
### Examples
```esql
// Exact phrase match
FROM articles METADATA _score
| WHERE MATCH_PHRASE(content, "machine learning pipeline")
| SORT _score DESC
| LIMIT 20
// With slop (allow N positions between words)
FROM docs METADATA _score
| WHERE MATCH_PHRASE(body, "connection refused", {"slop": 1})
| SORT _score DESC
| LIMIT 50
```
### Named Parameters
| Parameter | Type | Default | Description |
| ------------------ | ------- | ------- | --------------------------------------------- |
| `slop` | integer | `0` | Max positions allowed between matching tokens |
| `analyzer` | keyword | field's | Override the query-time analyzer |
| `boost` | float | `1.0` | Relevance score multiplier |
| `zero_terms_query` | keyword | — | Behavior when analyzer removes all tokens |
### Supported Field Types
`text`, `keyword`. Does **not** support `semantic_text` or numeric types.
### MATCH vs MATCH_PHRASE
| Query | "machine learning pipeline" | "learning machine" | "machine and learning" |
| -------------------------------------------------- | --------------------------- | ------------------ | ---------------------- |
| `MATCH(f, "machine learning")` | Yes | Yes | Yes |
| `MATCH(f, "machine learning", {"operator":"AND"})` | Yes | Yes | Yes |
| `MATCH_PHRASE(f, "machine learning")` | Yes | No | No |
| `MATCH_PHRASE(f, "machine learning", {"slop":1})` | Yes | No | Yes |
---
## QSTR (Query String)
Multi-field search using Lucene query string syntax. Equivalent to the Query DSL `query_string` query. Use when you need
complex boolean logic, wildcards, or field-specific searches in a single expression.
### Syntax
```esql
QSTR(query_string)
QSTR(query_string, {"option": value})
```
### Query String Mini-Language
| Syntax | Meaning | Example |
| ------------------------ | ------------------------------------ | ----------------------------------- |
| `term` | Match single term | `error` |
| `"phrase"` | Exact phrase | `"connection refused"` |
| `field:term` | Search specific field | `status:error` |
| `field:"phrase"` | Phrase on specific field | `message:"disk full"` |
| `term1 AND term2` | Both must match | `error AND timeout` |
| `term1 OR term2` | Either matches | `warning OR error` |
| `-term` | Exclude term | `error -test` |
| `term*` | Wildcard prefix | `connect*` |
| `term~N` | Fuzzy match (edit distance N) | `errror~1` |
| `"phrase"~N` | Proximity (words within N positions) | `"connection error"~3` |
| `(group)` | Grouping | `(error OR warning) AND production` |
| `field:(term1 OR term2)` | Multi-value on one field | `level:(error OR critical)` |
### Examples
```esql
// Multi-field boolean search
FROM logs-* METADATA _score
| WHERE QSTR("message:timeout AND level:error AND NOT host.name:test*")
| SORT _score DESC
| LIMIT 100
// Wildcard and proximity
FROM docs METADATA _score
| WHERE QSTR("title:elast* AND description:\"query language\"~2")
| SORT _score DESC
| LIMIT 20
// With default field and lenient mode
FROM logs-*
| WHERE QSTR("connection lost", {"default_field": "message", "lenient": true})
| LIMIT 100
```
### Named Parameters
| Parameter | Type | Default | Description |
| ------------------------ | ------- | ------- | ------------------------------------------------- |
| `default_field` | keyword | — | Default field when none specified in query string |
| `allow_leading_wildcard` | boolean | `true` | Allow `*` or `?` as first character |
| `lenient` | boolean | `false` | Ignore format-based errors |
| `fuzziness` | varies | — | Edit distance for fuzzy matching |
| `boost` | float | `1.0` | Relevance score multiplier |
---
## KQL (Kibana Query Language)
Run KQL queries within ES|QL. Useful for migrating existing Kibana search bar queries without rewriting them.
### Syntax
```esql
KQL(kql_string)
KQL(kql_string, {"option": value})
```
### Examples
```esql
// Basic KQL query
FROM logs-*
| WHERE KQL("message: error and host.name: server*")
| LIMIT 100
// KQL with multiple conditions
FROM web-logs METADATA _score
| WHERE KQL("http.request.method: GET and http.response.status_code >= 400")
| SORT _score DESC
| LIMIT 50
// Case-insensitive keyword matching (9.3+)
FROM logs-*
| WHERE KQL("level: Error", {"case_insensitive": true})
| LIMIT 100
```
### Named Parameters (9.3+)
| Parameter | Type | Default | Description |
| ------------------ | ------- | ------- | ---------------------------------------------- |
| `boost` | float | `1.0` | Relevance score multiplier |
| `time_zone` | keyword | — | UTC offset or IANA time zone for date literals |
| `case_insensitive` | boolean | `false` | Case-insensitive matching for keyword fields |
| `default_field` | keyword | — | Default field when none provided |
---
## Relevance Scoring
Full-text functions produce relevance scores. For lexical search on `text` fields, scoring uses BM25. For semantic
search on `semantic_text` fields, scoring is based on vector similarity. For hybrid search via `FUSE`, scores are
combined using the selected fusion method (RRF or linear). Scoring must be explicitly requested.
### Enabling Scores
Add `METADATA _score` to the `FROM` clause:
```esql
FROM index METADATA _score
| WHERE MATCH(field, "query")
| SORT _score DESC
| LIMIT 10
```
Without `METADATA _score`, full-text functions still filter documents but no ranking is applied.
### Score Boosting
Boost specific fields or queries by combining multiple MATCH calls with different boost values:
```esql
FROM articles METADATA _score
| WHERE MATCH(title, "elasticsearch", {"boost": 2.0})
OR MATCH(content, "elasticsearch")
| SORT _score DESC
| LIMIT 20
```
### Score Thresholds
Filter out low-relevance results:
```esql
FROM docs METADATA _score
| WHERE MATCH(content, "query optimization")
| WHERE _score > 2.0
| SORT _score DESC
| LIMIT 50
```
### Custom Scoring
Combine `_score` with other fields in `EVAL`:
```esql
FROM products METADATA _score
| WHERE MATCH(description, "wireless headphones")
| EVAL custom_score = _score + rating / 5.0
| SORT custom_score DESC
| LIMIT 20
```
---
## Semantic Search
Search `semantic_text` fields using `MATCH` or `:` for vector-based semantic matching. No separate function is needed —
MATCH automatically performs semantic search on `semantic_text` fields.
```esql
// Semantic search via colon operator
FROM knowledge_base METADATA _score
| WHERE semantic_content : "how do I reset my password"
| SORT _score DESC
| LIMIT 10
// Semantic search via MATCH
FROM knowledge_base METADATA _score
| WHERE MATCH(semantic_content, "configure two-factor authentication")
| SORT _score DESC
| LIMIT 10
```
---
## FORK / FUSE (Hybrid Search)
Combines multiple search strategies in parallel and merges results with relevance scoring.
```esql
FROM index METADATA _id, _index, _score
| FORK
(WHERE MATCH(text_field, "keyword query") | SORT _score DESC | EVAL branch = "lexical")
(WHERE MATCH(semantic_field, "semantic query") | SORT _score DESC | EVAL branch = "semantic")
| FUSE
| SORT _score DESC
| LIMIT 25
```
**Rules:**
1. `METADATA _id, _index, _score` must be in the `FROM` clause — FUSE requires all three. The same `METADATA _index`
declaration is required **before** you filter on `_index` inside a branch (for example `WHERE _index LIKE "logs-*"`).
Without `METADATA _index`, `_index` is not a valid column.
2. Each branch uses `WHERE MATCH(...)` inside parentheses — not a bare `MATCH` command
3. Each branch should `SORT _score DESC` to feed ranked results into FUSE
4. FUSE supports two methods: `rrf` (Reciprocal Rank Fusion, default) and `linear` (weighted linear combination with
optional `minmax` score normalization and per-branch `weights`)
5. The `_fork` column in results indicates which branch(es) matched each document
6. A maximum of 8 forks are allowed
### Examples
```esql
// Hybrid lexical + semantic search (RRF, default)
FROM articles METADATA _id, _index, _score
| FORK
(WHERE MATCH(title, "elasticsearch performance") | SORT _score DESC | LIMIT 10)
(WHERE semantic_content : "how to make elasticsearch faster" | SORT _score DESC | LIMIT 10)
| FUSE
| SORT _score DESC
| LIMIT 10
// LINEAR fusion with minmax normalization and custom weights
FROM articles METADATA _id, _index, _score
| FORK
(WHERE MATCH(title, "elasticsearch performance") | SORT _score DESC | LIMIT 50)
(WHERE semantic_content : "how to make elasticsearch faster" | SORT _score DESC | LIMIT 50)
| FUSE linear WITH { "normalizer": "minmax", "weights": { "fork1": 0.6, "fork2": 0.4 } }
| SORT _score DESC
| LIMIT 10
// Three-way hybrid: lexical, semantic, and KNN
FROM docs METADATA _id, _index, _score
| FORK
(WHERE MATCH(content, "query optimization") | SORT _score DESC | LIMIT 20)
(WHERE semantic_content : "how to speed up database queries" | SORT _score DESC | LIMIT 20)
(WHERE KNN(embedding, TEXT_EMBEDDING("query optimization", "my-endpoint")) | SORT _score DESC | LIMIT 20)
| DROP embedding
| FUSE
| SORT _score DESC
| LIMIT 10
```
---
## LOOKUP JOIN
Joins search results with a pre-built lookup index to enrich them with additional fields.
```esql
FROM source-index
| STATS count = COUNT(*) BY join_key_field
| LOOKUP JOIN lookup-index-name ON join_key_field
| KEEP join_key_field, count, enriched_field_from_lookup
| LIMIT 10
```
**Constraints:**
- Target must be a **separate, pre-built lookup index** — self-joins are not valid
- The lookup index must be in lookup mode (`index.mode: lookup`)
- After `STATS`, only aggregated columns exist — original source fields are gone
- Parameters (`?param`) cannot be used as field names
### Examples
```esql
// Enrich search results with category labels
FROM logs-* METADATA _score
| WHERE MATCH(message, "authentication error")
| STATS error_count = COUNT(*) BY host.name
| LOOKUP JOIN host-metadata ON host.name
| KEEP host.name, error_count, environment, team
| SORT error_count DESC
| LIMIT 20
// Enrich aggregated results with user info
FROM audit-logs
| WHERE MATCH(message, "permission denied")
| STATS denied_count = COUNT(*) BY user.id
| LOOKUP JOIN users-lookup ON user.id
| KEEP user.id, denied_count, user.name, department
| SORT denied_count DESC
| LIMIT 10
```
---
## Parameters in ES|QL
Use `?param_name` syntax for agent-controlled dynamic values:
```esql
FROM orders-*
| WHERE region == ?region AND @timestamp >= NOW() - ?days::integer * 1d
| STATS total = SUM(amount) BY product_category
| SORT total DESC
| LIMIT ?limit
```
**Parameter types:** `keyword`, `text`, `integer`, `long`, `double`, `boolean`, `date`
**Gotchas:**
- Parameters are **values only** — `?field_name` cannot be used as a dynamic column reference
- Duration syntax (`30d`) cannot be a parameter directly — use `?days::integer * 1d` instead
- Optional parameters should have defaults to prevent null-breaking query syntax
---
## Advanced Search Functions (Preview)
These functions are available in recent versions as tech preview.
### KNN — Dense Vector Search (9.2+, preview)
```esql
FROM index METADATA _score
| WHERE KNN(vector_field, [0.5, 0.8, 0.3])
| SORT _score DESC
| LIMIT 10
// With text embedding
FROM index METADATA _score
| WHERE KNN(embedding_field, TEXT_EMBEDDING("search query", "my-inference-endpoint"))
| SORT _score DESC
| LIMIT 10
```
### TOP_SNIPPETS — Search Result Highlights (9.3+, preview)
Extract the best-matching text snippets from a field:
```esql
FROM articles METADATA _score
| WHERE MATCH(content, "elasticsearch performance")
| EVAL snippets = TOP_SNIPPETS(content, "elasticsearch performance", {"num_snippets": 3, "num_words": 50})
| KEEP title, snippets, _score
| SORT _score DESC
| LIMIT 10
```
Options: `num_snippets` (number of snippets to return), `num_words` (max words per snippet).
### DECAY — Distance-Based Scoring (9.3+, preview)
Score based on distance from an origin (date, number, or geo point):
```esql
FROM events METADATA _score
| WHERE MATCH(title, "conference")
| EVAL recency_score = DECAY("gauss", @timestamp, NOW(), "7d", "30d")
| SORT recency_score DESC
| LIMIT 20
```
---
## Placement Rules
Full-text search functions (`MATCH`, `QSTR`, `KQL`, `MATCH_PHRASE`) must appear in a `WHERE` clause **before** any
processing command has modified the column set. In practice this means they must be placed in the `WHERE` immediately
after `FROM` (or after another `WHERE`). They can also appear in per-aggregation `STATS ... WHERE` filters.
They **cannot** be used after any of these commands: `EVAL`, `GROK`, `DISSECT`, `KEEP`, `DROP`, `RENAME`, `MV_EXPAND`,
`STATS`, `LIMIT`, `SHOW`, `ROW`.
```esql
// CORRECT — MATCH directly after FROM
FROM logs-* METADATA _score
| WHERE MATCH(message, "timeout")
| SORT _score DESC
| LIMIT 50
// WRONG — MATCH after EVAL will fail
FROM logs-*
| EVAL lower_msg = TO_LOWER(message)
| WHERE MATCH(message, "timeout")
```
**Important:** Full-text functions require indexed fields. They cannot operate on runtime-computed values, `ROW`
literals, or fields created by `EVAL`.
---
## Common Patterns
### Search Logs for Error Messages
```esql
FROM logs-* METADATA _score
| WHERE @timestamp > NOW() - 1 hour
| WHERE MATCH(message, "connection refused timeout")
| KEEP @timestamp, host.name, message, _score
| SORT _score DESC
| LIMIT 100
```
### Find Documents by Exact Phrase
```esql
FROM docs METADATA _score
| WHERE MATCH_PHRASE(content, "null pointer exception")
| KEEP title, content, _score
| SORT _score DESC
| LIMIT 20
```
### Multi-Criteria Search with QSTR
```esql
FROM logs-* METADATA _score
| WHERE @timestamp > NOW() - 24 hours
| WHERE QSTR("message:(timeout OR refused) AND level:error AND NOT host.name:staging*")
| KEEP @timestamp, host.name, level, message, _score
| SORT _score DESC
| LIMIT 100
```
### Aggregate Search Results
```esql
FROM logs-*
| WHERE MATCH(message, "authentication failure")
| WHERE @timestamp > NOW() - 24 hours
| STATS failure_count = COUNT(*) BY host.name
| SORT failure_count DESC
| LIMIT 20
```
### Migrate KQL from Kibana Search Bar
```esql
// Original KQL in Kibana: message: error and host.name: prod-*
FROM logs-*
| WHERE KQL("message: error and host.name: prod-*")
| SORT @timestamp DESC
| LIMIT 100
```
### Combine Text Search with Aggregation
```esql
FROM logs-*
| WHERE MATCH(message, "disk space")
| WHERE @timestamp > NOW() - 7 days
| STATS
count = COUNT(*),
hosts_affected = COUNT_DISTINCT(host.name)
BY day = DATE_TRUNC(1 day, @timestamp)
| SORT day DESC
```
---
## Full-Text Search vs Pattern Matching
| Capability | Full-Text (`MATCH`, `QSTR`) | Pattern (`LIKE`, `RLIKE`) |
| ------------------------- | --------------------------- | --------------------------- |
| Uses inverted index | Yes (fast) | No (scans values) |
| Analyzer support | Yes (stemming, synonyms) | No |
| Relevance scoring | Yes (`_score`) | No |
| Case-insensitive | Yes (via analyzer) | Manual (`TO_LOWER` + match) |
| Fuzzy matching | Yes (`fuzziness` option) | Manual (complex regex) |
| Wildcard patterns | Limited (`*` in QSTR) | Full regex support |
| Works on keyword fields | Yes | Yes |
| Works on computed values | No (index fields only) | Yes |
| Performance on large data | Excellent | Poor |
---
## Interaction with Text Analyzers
Full-text search functions automatically use the field's configured analyzer at both index time and query time. **These
analyzer features apply to `text` fields only — they do not apply to `semantic_text` fields**, which use vector-based
similarity instead of token analysis.
**Analyzer-powered capabilities (text fields only):**
- **Case-insensitive matching** — analyzers typically lowercase tokens
- **Stemming** — "running" matches "run", "runs", "ran"
- **Stopword removal** — common words like "the", "a" are excluded
- **Synonyms** — configured synonym mappings are applied
- **ASCII folding** — "café" matches "cafe"
**Override the analyzer** at query time using the `analyzer` parameter (text fields only):
```esql
FROM logs-*
| WHERE MATCH(message, "query text", {"analyzer": "my_custom_analyzer"})
```
**Contrast with `LIKE`/`RLIKE`:** These operators work on exact stored values and bypass all analyzer processing.
references/esql-version-history.md
# ES|QL Version History and Feature Availability
This document tracks ES|QL language features, commands, and functions across Elasticsearch versions. Use this to
determine compatibility when writing queries for specific Elasticsearch deployments.
> **Paired releases:** Certain minor versions shipped simultaneously with nearly identical feature sets. When a feature
> appears in one, assume it is in both unless explicitly noted otherwise. Paired versions: **8.18 / 9.0**, **8.19 /
> 9.1**.
>
> **Serverless:** Elastic Cloud Serverless reports a forward-moving `version.number` from `GET /` (aligned with the next
> minor from main), so clients that only semver-compare often behave as if the cluster is “latest.” **Do not** rely on
> that for feature gating: check `build_flavor` — if it is `"serverless"`, all GA and preview features are available and
> you should skip version-based gates. For snapshot builds (e.g., `9.4.0-SNAPSHOT`), strip the `-SNAPSHOT` suffix and
> use the major.minor for version checks.
## Table of Contents
- [Version Timeline Overview](#version-timeline-overview)
- [Feature Availability by Version](#feature-availability-by-version)
- [Major Limitations](#major-limitations)
- [Cross-Cluster Query Support](#cross-cluster-query-support)
- [Output Formats](#output-formats)
- [API Endpoints](#api-endpoints)
- [Performance Tips by Version](#performance-tips-by-version)
- [Version Detection](#version-detection)
- [References](#references)
## Version Timeline Overview
| Version | Release | Status | Key Additions |
| ------- | -------- | ------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| 8.11 | Nov 2023 | Tech Preview | Initial ES\|QL release |
| 8.12 | Jan 2024 | Tech Preview | Spatial types, PROFILE |
| 8.13 | Mar 2024 | Tech Preview | Async queries, cross-cluster ENRICH |
| 8.14 | May 2024 | **GA** | Spatial functions, regex optimization |
| 8.15 | Aug 2024 | GA | Type casting (`::`), Arrow output |
| 8.16 | Oct 2024 | GA | Per-aggregation WHERE, new math/string functions |
| 8.17 | Dec 2024 | GA | MATCH, QSTR full-text functions |
| 8.18 | Feb 2025 | GA | LOOKUP JOIN (preview), scoring, KQL |
| 8.19 | Apr 2025 | GA | MATCH_PHRASE, FORK, CHANGE_POINT (preview) |
| 9.0 | Feb 2025 | GA | Released with 8.18 features |
| 9.1 | Jun 2025 | GA | Full-text functions GA, FORK (preview) |
| 9.2 | Oct 2025 | GA | Multi-field joins, TS, INLINE STATS (preview), CHANGE_POINT GA, FUSE (preview), RERANK (preview) |
| 9.3 | Jan 2026 | GA | INLINE STATS GA, SET directive (preview), Lucene-pushable JOIN predicates |
| 9.4 | May 2026 | GA | TS/TBUCKET GA, time series funcs GA, WITHOUT/METRICS_INFO/TS_INFO, PROMQL (preview), Views, RERANK GA, MV_EXPAND/VALUES GA, SET time_zone, SET approximation |
## Feature Availability by Version
### Commands
| Command | Introduced | GA | Notes |
| ------------------- | ---------- | -------- | ---------------------------------------------------------------- |
| `FROM` | 8.11 | 8.14 | Source command |
| `WHERE` | 8.11 | 8.14 | Filtering |
| `EVAL` | 8.11 | 8.14 | Computed columns |
| `STATS ... BY` | 8.11 | 8.14 | Aggregations with grouping |
| `SORT` | 8.11 | 8.14 | Ordering results |
| `LIMIT` | 8.11 | 8.14 | Result set size |
| `KEEP` | 8.11 | 8.14 | Column selection |
| `DROP` | 8.11 | 8.14 | Column removal |
| `RENAME` | 8.11 | 8.14 | Column renaming |
| `DISSECT` | 8.11 | 8.14 | Pattern extraction |
| `GROK` | 8.11 | 8.14 | Log parsing |
| `ENRICH` | 8.11 | 8.14 | Data enrichment |
| `MV_EXPAND` | 8.11 | 9.4 | Multi-value expansion (GA) |
| `SHOW` | 8.11 | 8.14 | Metadata display |
| `ROW` | 8.11 | 8.14 | Literal row creation |
| `LOOKUP JOIN` | 8.18/9.0 | 8.19/9.1 | SQL-style LEFT JOIN with lookup indices |
| `INLINE STATS` | 9.2 | 9.3 | Inline aggregations (like window functions) |
| `FORK` | 8.19/9.1 | 9.4 | Multiple execution branches |
| `FUSE` | 9.2 | Preview | Combine results from FORK branches |
| `TS` | 9.2 | 9.4 | Time series source command (GA in 9.4) |
| `PROMQL` | 9.4 | Preview | Source command using PromQL syntax on TSDS |
| `METRICS_INFO` | 9.4 | 9.4 | TSDS metric catalogue (after `TS`) |
| `TS_INFO` | 9.4 | 9.4 | Per-(metric, time series) metadata (after `TS`) |
| `RERANK` | 9.2 | 9.4 | Re-score results with inference (GA in 9.4) |
| `COMPLETION` | 9.2 | 9.2 | LLM text generation |
| `SAMPLE` | 8.19/9.1 | Preview | Random sampling |
| `CHANGE_POINT` | 8.19/9.1 | 9.2 | Spike/dip detection (Platinum license) |
| `MMR` | 9.4 | Preview | Maximal Marginal Relevance diversification |
| `URI_PARTS` | 9.4 | 9.4 | Parse URI into structured columns (pipe command) |
| `USER_AGENT` | 9.4 | 9.4 | Parse user agent into structured columns (pipe command) |
| `REGISTERED_DOMAIN` | 9.4 | 9.4 | `REGISTERED_DOMAIN`: extract from hostname (pipe command) |
| Views | 9.4 | Preview | Virtual indices from ES\|QL queries (Stack only, not Serverless) |
### Full-Text Search Functions
| Function | Introduced | GA | Notes |
| ----------------------------- | ---------- | -------- | ---------------------------- |
| `MATCH(field, query)` | 8.17 | 8.19/9.1 | Basic full-text matching |
| `QSTR(query_string)` | 8.17 | 8.19/9.1 | Query string syntax (Lucene) |
| `KQL(kql_string)` | 8.18/9.0 | 8.19/9.1 | Kibana Query Language |
| `MATCH_PHRASE(field, phrase)` | 8.19/9.1 | 8.19/9.1 | Exact phrase matching |
| Match operator (`:`) | 8.17 | 8.19/9.1 | Shorthand for MATCH |
**Scoring support:**
- `METADATA _score` available from 8.18/9.0
- Must use `SORT _score DESC` to rank by relevance
### Spatial Functions
| Function | Introduced | Notes |
| ----------------------------- | ---------- | ------------------------------------- |
| `GEO_POINT` type | 8.12 | Basic spatial type support |
| `CARTESIAN_POINT` type | 8.12 | Cartesian coordinate support |
| `ST_INTERSECTS` | 8.14 | Geometry intersection test |
| `ST_CONTAINS` | 8.14 | Containment test |
| `ST_DISJOINT` | 8.14 | Disjoint test |
| `ST_WITHIN` | 8.14 | Within test |
| `ST_X`, `ST_Y` | 8.14 | Coordinate extraction |
| `ST_DISTANCE` | 8.15 | Distance calculation |
| `ST_EXTENT_AGG` | 8.18/9.0 | Bounding box aggregation |
| `ST_ENVELOPE` | 8.18/9.0 | Bounding box for geometry |
| `ST_DIMENSION` | 9.4 | Geometry dimension (0/1/2) |
| `ST_GEOMETRYTYPE` | 9.4 | Geometry type as string |
| `ST_ISEMPTY` | 9.4 | Test if geometry is empty |
| `ST_BUFFER` | 9.4 | Buffer around geometry |
| `ST_SIMPLIFYPRESERVETOPOLOGY` | 9.4 | Simplify geometry preserving topology |
### Date/Time Functions
| Function | Introduced | Notes |
| ----------------- | -------------- | ------------------------------------ |
| `NOW()` | 8.11 | Current timestamp |
| `DATE_TRUNC` | 8.11 | Truncate to interval |
| `DATE_EXTRACT` | 8.11 | Extract date parts |
| `DATE_FORMAT` | 8.11 | Format dates (no TZ until 9.3) |
| `DATE_PARSE` | 8.11 | Parse date strings (no TZ until 9.3) |
| `DATE_DIFF` | 8.13 | Difference between dates |
| `date_nanos` type | 8.17 (preview) | Nanosecond precision timestamps |
| `TRANGE` | 9.3 (preview) | Time range filter on `@timestamp` |
### String Functions
| Function | Introduced | Notes |
| --------------------------- | ---------- | ---------------------------------------------------- |
| `LEFT`, `RIGHT` | 8.11 | Substring extraction |
| `SUBSTRING` | 8.11 | Position-based extraction |
| `CONCAT` | 8.11 | String concatenation |
| `TRIM`, `LTRIM`, `RTRIM` | 8.11 | Whitespace removal |
| `TO_UPPER`, `TO_LOWER` | 8.13 | Case conversion |
| `LOCATE` | 8.14 | Find substring position |
| `SPACE` | 8.16 | Generate spaces |
| `REVERSE` | 8.16 | Reverse string |
| `BIT_LENGTH`, `BYTE_LENGTH` | 8.17 | String length in bits/bytes |
| `STARTS_WITH`, `ENDS_WITH` | 8.11 | Prefix/suffix matching |
| `CONTAINS` | 9.2 | Substring containment check |
| `JSON_EXTRACT` | 9.4 | Extract value from JSON string by JSONPath (preview) |
### Multi-Value Functions
| Function | Introduced | Notes |
| ------------------------- | ---------- | --------------------------------------- |
| `MV_COUNT` | 8.11 | Count values |
| `MV_CONCAT` | 8.11 | Join values |
| `MV_FIRST`, `MV_LAST` | 8.13 | First/last value |
| `MV_MIN`, `MV_MAX` | 8.11 | Min/max value |
| `MV_SUM`, `MV_AVG` | 8.11 | Sum/average |
| `MV_MEDIAN` | 8.11 | Median value |
| `MV_SORT` | 8.14 | Sort multi-values |
| `MV_SLICE` | 8.14 | Slice multi-values |
| `MV_PERCENTILE` | 8.16 | Percentile calculation |
| `MV_PSERIES_WEIGHTED_SUM` | 8.16 | Weighted sum |
| `MV_DIFFERENCE` | 9.4 | Set difference of two MV fields |
| `MV_UNION` | 9.4 | Set union of two MV fields |
| `MV_INTERSECTION` | 9.4 | Set intersection of two MV fields |
| `MV_INTERSECTS` | 9.4 | True if MV fields share a value |
| `MV_CONTAINS` | 9.4 | True if first MV contains all of second |
### Aggregation Functions
| Function | Introduced | Notes |
| ------------------------------------- | ---------- | ---------------------------------------------------- |
| `COUNT`, `COUNT_DISTINCT` | 8.11 | Counting |
| `SUM`, `AVG` | 8.11 | Basic aggregations |
| `MIN`, `MAX` | 8.11 | Extended to strings/IPs in 8.16 |
| `MEDIAN`, `MEDIAN_ABSOLUTE_DEVIATION` | 8.11 | Statistical |
| `PERCENTILE` | 8.11 | Percentile calculation |
| `TOP` | 8.15 | Top N values |
| `VALUES` | 8.14 | Unique values (GA in 9.4) |
| `ST_EXTENT_AGG` | 8.18/9.0 | Spatial bounding box |
| `WEIGHTED_AVG` | 8.16 | Weighted average |
| `STD_DEV` | 8.18/9.0 | Standard deviation |
| `VARIANCE` | 8.18/9.0 | Variance |
| `FIRST(value, sort_field)` | 9.4 | Value from row with earliest sort field |
| `LAST(value, sort_field)` | 9.4 | Value from row with latest sort field |
| `EARLIEST(@timestamp)` | 9.4 | Min `@timestamp` (1-arg shorthand) |
| `LATEST(@timestamp)` | 9.4 | Max `@timestamp` (1-arg shorthand) |
| `SPARKLINE` | Serverless | Histogram sparkline (Serverless only, not 9.4 Stack) |
### Grouping Functions
| Function | Introduced | Notes |
| ------------ | ---------- | ---------------------------------------------------------------- |
| `BUCKET` | 8.11 | Numeric/date bucketing in `BY` clause |
| `CATEGORIZE` | 8.18/9.0 | Auto-categorization of text in `BY` clause |
| `TBUCKET` | 9.2 | Time bucketing from `@timestamp`; preferred in TS (GA in 9.4) |
| `WITHOUT` | 9.4 | Group time series by every dimension except the listed ones (GA) |
### Per-Aggregation WHERE
Available since 8.16. Allows filtering individual aggregations without affecting others:
```esql
| STATS total = COUNT(*), errors = COUNT(*) WHERE level == "error" BY service.name
```
### IP Functions
| Function | Introduced | Notes |
| ------------ | ---------- | ------------------------------ |
| `CIDR_MATCH` | 8.11 | Check IP against CIDR ranges |
| `IP_PREFIX` | 8.14 | Extract network prefix from IP |
| `TO_IP` | 8.11 | Convert string to IP type |
### Time Series Aggregation Functions
Available under `TS ... | STATS`. See [time-series-queries.md](time-series-queries.md) for full reference. All time
series aggregation functions in this table — both the 9.2-introduced set and the 9.3-introduced set (`DERIV`,
`PERCENTILE_OVER_TIME`, `STDDEV_OVER_TIME`, `VARIANCE_OVER_TIME`) — are **GA since 9.4**.
| Function | Introduced | Status | Notes |
| -------------------------- | ------------- | -------- | ----------------------------------------------------------------- |
| `RATE` | 9.2 (preview) | GA (9.4) | Per-second rate of counter increase |
| `IRATE` | 9.2 (preview) | GA (9.4) | Instant rate (last two data points) |
| `INCREASE` | 9.2 (preview) | GA (9.4) | Absolute counter increase in window |
| `DELTA` | 9.2 (preview) | GA (9.4) | Absolute change of a gauge |
| `IDELTA` | 9.2 (preview) | GA (9.4) | Change between last two data points |
| `AVG_OVER_TIME` | 9.2 (preview) | GA (9.4) | Average value over time |
| `SUM_OVER_TIME` | 9.2 (preview) | GA (9.4) | Sum of values over time |
| `MIN_OVER_TIME` | 9.2 (preview) | GA (9.4) | Minimum value over time |
| `MAX_OVER_TIME` | 9.2 (preview) | GA (9.4) | Maximum value over time |
| `FIRST_OVER_TIME` | 9.2 (preview) | GA (9.4) | Earliest value by `@timestamp` |
| `LAST_OVER_TIME` | 9.2 (preview) | GA (9.4) | Latest value by `@timestamp` (implicit default for numeric/gauge) |
| `COUNT_OVER_TIME` | 9.2 (preview) | GA (9.4) | Count of values over time |
| `COUNT_DISTINCT_OVER_TIME` | 9.2 (preview) | GA (9.4) | Count of distinct values over time |
| `PRESENT_OVER_TIME` | 9.2 (preview) | GA (9.4) | `true` if field has values in window |
| `ABSENT_OVER_TIME` | 9.2 (preview) | GA (9.4) | `true` if field has no values in window |
| `DERIV` | 9.3 (preview) | GA (9.4) | Derivative via linear regression |
| `PERCENTILE_OVER_TIME` | 9.3 (preview) | GA (9.4) | Percentile of values over time |
| `STDDEV_OVER_TIME` | 9.3 (preview) | GA (9.4) | Population standard deviation over time |
| `VARIANCE_OVER_TIME` | 9.3 (preview) | GA (9.4) | Population variance over time |
**Sliding window parameter (second argument):**
- 9.2-9.3 (preview) — accepted window values are limited to multiples of the `TBUCKET` interval in the `BY` clause; if
no window is specified, the bucket interval is used implicitly.
- 9.4+ (GA) — all window values are accepted, with performance optimizations when the window is a multiple of the
`TBUCKET` interval. Mixing windows that are smaller than the time bucket for one metric with windows larger than the
time bucket for another metric in the same query is not allowed.
### Conditional Functions
| Function | Introduced | Notes |
| ----------- | ------------- | ---------------------------------- |
| `CLAMP` | 9.3 (preview) | Clamp values to `[min, max]` range |
| `CLAMP_MIN` | 9.3 (preview) | Set lower bound for values |
| `CLAMP_MAX` | 9.3 (preview) | Set upper bound for values |
### Type Casting
| Syntax | Introduced | Notes |
| --------------- | ---------- | ---------------------- |
| `TO_STRING(x)` | 8.11 | Function-based casting |
| `TO_INTEGER(x)` | 8.11 | Function-based casting |
| `TO_DOUBLE(x)` | 8.11 | Function-based casting |
| `x::string` | 8.15 | Operator-based casting |
| `x::integer` | 8.15 | Operator-based casting |
## Major Limitations
### Pagination (Not Supported)
ES|QL **does not support cursor-based pagination** like the Search API's `search_after` or `scroll`.
**Current behavior:**
- Default: 1,000 rows returned
- Maximum: 10,000 rows (configurable via `esql.query.result_truncation_max_size`)
- No cursor or continuation token
- GitHub tracking issue: [#100000](https://github.com/elastic/elasticsearch/issues/100000)
**Workarounds:**
- Use `WHERE` to filter to relevant subset
- Use `STATS` to aggregate at query time
- For exports, use Search API with `search_after` instead
### Time Zone Support (GA in 9.4+; Serverless)
ES|QL supports query-wide timezone via the `SET time_zone` directive (GA in 9.4+; Serverless). This accepts IANA
timezone strings and UTC offsets, and applies to all date/time operations including `DATE_TRUNC`, `DATE_FORMAT`,
`NOW()`, bucketing, and display.
```esql
SET time_zone = "America/New_York";
FROM logs-*
| STATS errors = COUNT(*) BY hour = DATE_TRUNC(1 hour, @timestamp)
| SORT hour DESC
```
**Remaining limitations (all versions):**
- No per-function timezone argument — `DATE_TRUNC(1 hour, @timestamp, "America/New_York")` does **not** work
- `DATE_FORMAT` and `DATE_PARSE` do not accept timezone parameters directly; use `SET time_zone` instead
**Versions before 9.4:** No timezone support. All dates are processed in UTC. Workaround: use `EVAL` to add/subtract
hours manually:
```esql
| EVAL local_time = timestamp + 1 hour
```
### Nested Fields (Not Supported)
ES|QL **cannot query nested field types**. Unlike other unsupported types (which return `null`), nested fields are **not
returned at all** — they are silently omitted from results.
- Cannot use nested paths like `nested_field.sub_field`
- Must flatten data at index time for ES|QL access
### Unsupported Field Types
These field types are not supported or have limitations:
| Type | Status |
| -------------- | --------------------------------------------------------------------------------------------- |
| `nested` | Not supported - returns null |
| `flattened` | Not natively supported; use `METADATA _source` + `JSON_EXTRACT` for sub-key access |
| `join` | Not supported |
| `date_range` | Not supported |
| `binary` | Not supported |
| `completion` | Not supported |
| `rank_feature` | Not supported |
| `histogram` | Supported in `TS` via cast only (`::exponential_histogram`/`::tdigest`; 9.3 preview / 9.4 GA) |
### JOIN Limitations
`LOOKUP JOIN` (8.18/9.0+):
- Only LEFT OUTER JOIN behavior
- Lookup index must use `index.mode: lookup` setting
- Lookup index limited to single shard (max 2B docs)
- Cross-cluster joins require lookup index on all clusters
- Only supports equality joins before 9.2
`LOOKUP JOIN` improvements in 9.2 (tech preview):
- Multi-field joins supported
- Complex join predicates with `<`, `>`, `<=`, `>=`
- Expression-based join conditions
`LOOKUP JOIN` improvements in 9.3 (tech preview):
- Lucene-pushable predicates: `MATCH`, `QSTR`, `KQL`, `CIDR_MATCH` in join conditions
- Further performance gains for filtered joins
### Subqueries (Limited)
ES|QL supports **subqueries in `FROM`** (9.4+; Serverless) for combining results from multiple pipelines (UNION ALL
semantics). These are non-correlated — each branch is independent.
```esql
FROM
(FROM web_logs | WHERE status >= 500 | KEEP @timestamp, message, service.name),
(FROM app_logs | WHERE level == "error" | KEEP @timestamp, message, service.name)
| SORT @timestamp DESC
```
**Not supported:**
- Subqueries in `WHERE` clauses (no `WHERE field IN (FROM ...)`)
- Correlated subqueries (branches cannot reference outer columns)
- Nested SELECT / CTEs (Common Table Expressions)
Use `INLINE STATS` (9.2+) for per-row vs. aggregate comparison patterns.
## Cross-Cluster Query Support
| Feature | Version | Notes |
| ------------------------- | ------- | ----------------------------------------- |
| Basic CCS | 8.13 | Query remote clusters |
| Cross-cluster ENRICH | 8.13 | Enrich with remote data |
| Cross-cluster LOOKUP JOIN | 9.2 | Join with remote lookup indices |
| `skip_unavailable` | 8.17 | Graceful handling of unavailable clusters |
## Output Formats
| Format | Version | Notes |
| ------ | ------- | ----------------------- |
| JSON | 8.11 | Default format |
| CSV | 8.11 | Tabular output |
| TSV | 8.11 | Tab-separated |
| Arrow | 8.15 | Apache Arrow IPC format |
## API Endpoints
| Endpoint | Version | Notes |
| ---------------------------- | ------- | -------------------------------- |
| `POST /_query` | 8.11 | Synchronous query |
| `POST /_query/async` | 8.13 | Async query submission |
| `GET /_query/async/{id}` | 8.13 | Get async query results |
| `DELETE /_query/async/{id}` | 8.13 | Cancel async query |
| `PUT /_query/view/{name}` | 9.4 | Create/update view (Stack only) |
| `GET /_query/view/{name}` | 9.4 | Get view definition (Stack only) |
| `DELETE /_query/view/{name}` | 9.4 | Delete view (Stack only) |
## Performance Tips by Version
### 8.14+
- Regex patterns are optimized
- Enrich supports text fields
### 8.15+
- Use `::` casting instead of `TO_*` functions (cleaner syntax)
- Arrow format for analytics tool integration
### 8.17+
- Use `MATCH`/`QSTR` instead of `LIKE`/`RLIKE` for text search (50-1000x faster)
- Full-text functions use Lucene optimizations
### 9.1+
- Use `INLINE STATS` to avoid multiple queries
- Full-text functions are GA and stable
### 9.2+
- Use `TS` with `RATE`, `AVG_OVER_TIME`, etc. for time series metrics aggregations (preview in 9.2-9.3, GA in 9.4)
- Use `TBUCKET` for time bucketing in TS queries (GA in 9.4)
- Multi-field `LOOKUP JOIN` for complex correlations
- `FUSE` for hybrid search scoring
### 9.3+
- Use `TRANGE` instead of manual `WHERE @timestamp` filters
- Sliding window parameter for time series functions (e.g. `RATE(field, 10m)`); in 9.2-9.3 the window must be a multiple
of the `TBUCKET` interval, this restriction is lifted in 9.4
- `CLAMP`, `CLAMP_MIN`, `CLAMP_MAX` for bounding metric values
- Histogram metrics in `TS` (preview): query `exponential_histogram` / `tdigest` with standard aggregations; cast plain
`histogram` with `::exponential_histogram` or `::tdigest` — see
[Histogram Metrics](time-series-queries.md#histogram-metrics)
### 9.4+
- `TS`, `TBUCKET`, and all time series aggregation functions are **GA** — safe for production metrics queries
- Histogram metrics in `TS` are **GA** (`exponential_histogram` / `tdigest`; plain `histogram` still requires a cast)
- Use `METRICS_INFO` / `TS_INFO` to discover TSDS schemas instead of inspecting mappings or field capabilities
- Use `WITHOUT(dim, ...)` to group by all dimensions except specific ones — avoids enumerating every dimension manually
- Sliding window accepts arbitrary durations — `RATE(field, 7m)` with `TBUCKET(5 minute)` now works
- Use `SET time_zone` with IANA strings for timezone-aware queries instead of manual `EVAL` offset arithmetic
- Use `SET approximation = true` to approximate large `STATS` summaries via sampling/extrapolation (preview in 9.4, GA
in 9.5+ and Serverless) — returns estimates with confidence intervals; see
[query-approximation.md](query-approximation.md)
- Use `SET unmapped_fields = "load"` to query fields missing from some indices without errors
- Use `FIRST`/`LAST` (or `EARLIEST`/`LATEST`) instead of `SORT` + `LIMIT 1` for grouped first/last-value queries
- Use `PROMQL` when porting Prometheus dashboards/alerts; otherwise prefer `TS` for native ES|QL
### Serverless (latest)
Serverless includes all 9.4 features and may have additional preview features:
- `SPARKLINE` — histogram sparkline aggregation (Serverless only, not 9.4 Stack)
## Version Detection
To check ES|QL availability and version:
```bash
# Check Elasticsearch version and build flavor (use build_flavor to detect Serverless)
curl -s localhost:9200 | jq '.version | {number, build_flavor}'
# Test ES|QL availability
curl -X POST localhost:9200/_query \
-H "Content-Type: application/json" \
-d '{"query": "ROW x = 1"}'
```
## References
- [ES|QL Timeline of Improvements](https://www.elastic.co/search-labs/blog/esql-timeline-of-improvements)
- [ES|QL Limitations](https://www.elastic.co/docs/reference/query-languages/esql/limitations)
- [Elasticsearch Release Notes](https://www.elastic.co/docs/release-notes/elasticsearch)
- [ES|QL for Search](https://www.elastic.co/docs/solutions/search/esql-for-search)
- [LOOKUP JOIN Documentation](https://www.elastic.co/docs/reference/query-languages/esql/esql-lookup-join)
references/generation-tips.md
# ES|QL Query Generation Tips
Guidelines for generating accurate ES|QL queries from natural language.
> **Cluster detection:** Check `build_flavor` in the `GET /` response. For Serverless (`"serverless"`), **do not**
> version-gate: `version.number` tracks the next minor from main (semver-only clients may see it as “latest”), but
> feature availability is not determined by that string — use `build_flavor` as the signal. For Stack (`"default"`), use
> `version.number` for feature checks (strip `-SNAPSHOT` suffix on pre-release builds).
## Table of Contents
- [Critical Syntax Rules](#critical-syntax-rules)
- [Step-by-Step Generation Process](#step-by-step-generation-process)
- [Field Name Conventions](#field-name-conventions)
- [Query Optimization Tips](#query-optimization-tips)
- [Key Patterns](#key-patterns)
- [Common Query Templates](#common-query-templates)
- [Handling Ambiguity](#handling-ambiguity)
- [Output Formatting Suggestions](#output-formatting-suggestions)
## Critical Syntax Rules
### String Literals Use Double Quotes Only
ES|QL uses **double quotes** for string literals — never single quotes. This is the most common source of
`token recognition error at: '` failures. SQL habits lead models to write `'value'` when ES|QL requires `"value"`.
```esql
// WRONG — single quotes cause parse errors
| WHERE status == 'open'
| EVAL priority = CASE(status == 'open', 'high', 'low')
// CORRECT — always double quotes
| WHERE status == "open"
| EVAL priority = CASE(status == "open", "high", "low")
```
This applies everywhere: `WHERE`, `EVAL`, `CASE`, `STATS ... BY`, function arguments, and string constants.
### CASE Uses Condition-Value Pairs (Not SQL Syntax)
ES|QL `CASE` takes alternating condition-value pairs with an optional default — it does **not** support
`CASE WHEN ... THEN ... ELSE ... END` syntax.
```esql
// WRONG — SQL-style CASE
| EVAL grade = CASE WHEN score > 90 THEN "A" WHEN score > 80 THEN "B" ELSE "C" END
// CORRECT — ES|QL pairs: CASE(cond1, val1, cond2, val2, ..., default)
| EVAL grade = CASE(score > 90, "A", score > 80, "B", "C")
```
Two-branch conditionals use three arguments (condition, true-value, false-value):
```esql
| EVAL priority = CASE(status == "open", "high", "low")
```
### Aggregation Function Names Differ from SQL
ES|QL function names use underscores where SQL does not. The most common mistake is `STDDEV()` — the correct ES|QL name
is `STD_DEV()`.
| SQL Name | ES\|QL Name |
| -------- | ----------- |
| STDDEV | STD_DEV |
```esql
// WRONG — SQL function name
| STATS sd = STDDEV(total)
// CORRECT — ES|QL uses underscored name
| STATS sd = STD_DEV(total)
```
### String Concatenation Uses CONCAT (No + Operator)
ES|QL does not support the `+` operator for string concatenation. Use `CONCAT()` instead. ES|QL also does not have
`SUBSTRING`, `STRPOS`, `SPLIT`, or `INSTR` — use `DISSECT` or `GROK` for string extraction.
```esql
// WRONG — + operator does not work on strings
| EVAL full_name = first_name + " " + last_name
// CORRECT
| EVAL full_name = CONCAT(first_name, " ", last_name)
```
### DATE_EXTRACT Part Names Differ from SQL
`DATE_EXTRACT(part, date)` uses ES|QL-specific part name strings — not SQL keywords like `HOUR` or `DAY`. The part
string must be **double-quoted** and is **case-insensitive**.
| SQL Part | ES\|QL Part Name |
| -------- | -------------------- |
| YEAR | `"year"` |
| QUARTER | `"quarter"` |
| MONTH | `"month_of_year"` |
| WEEK | `"week"` |
| DAY | `"day_of_month"` |
| DOW | `"day_of_week"` |
| DOY | `"day_of_year"` |
| HOUR | `"hour_of_day"` |
| MINUTE | `"minute_of_hour"` |
| SECOND | `"second_of_minute"` |
```esql
// WRONG — SQL-style part names or single quotes
| EVAL hour = DATE_EXTRACT("hour", @timestamp)
| EVAL hour = DATE_EXTRACT('HOUR_OF_DAY', @timestamp)
// CORRECT — ES|QL part name in double quotes
| EVAL hour = DATE_EXTRACT("hour_of_day", @timestamp)
| STATS count = COUNT(*) BY hour = DATE_EXTRACT("hour_of_day", @timestamp)
```
### Date Arithmetic Uses DATE_DIFF (No Subtraction)
ES|QL does not support the `-` operator between two date values. Use `DATE_DIFF(unit, start, end)` instead.
```esql
// WRONG — subtraction between dates is not supported
| EVAL days = end_date - start_date
// CORRECT — DATE_DIFF computes the difference in the given unit
| EVAL days = DATE_DIFF("day", start_date, end_date)
```
Valid units: `"year"`, `"quarter"`, `"month"`, `"week"`, `"day"`, `"hour"`, `"minute"`, `"second"`, `"millisecond"`.
---
## Step-by-Step Generation Process
### 1. Identify the Data Source
**Question:** What index or data should be queried?
- Look for index names, data types, or subject areas mentioned
- Common patterns: `logs-*`, `metrics-*`, `events-*`, `apm-*`
- If unclear, use wildcards or ask for clarification
```esql
FROM logs-* // Generic logs
FROM metrics-* // Metrics data
FROM my-index-2024.* // Dated indices
```
For time series data streams (TSDS), use `TS` instead of `FROM` to enable time series aggregation functions like `RATE`,
`AVG_OVER_TIME`, etc. (preview from 9.2 to 9.3, **GA since 9.4**):
```esql
TS metrics-* // Time series source — enables RATE, AVG_OVER_TIME, etc.
```
**When the question asks about rates, throughput, CPU/memory trends, or metric comparisons**, prefer a `metrics-*` or
TSDS index with `TS` over a general log index with `FROM`. Check the schema — if an index has `Index mode: time_series`,
always use `TS`.
### 2. Determine Time Range
**Question:** What time period should be covered?
| User Expression | ES\|QL |
| --------------- | ------------------------------------------------------------------------------------------ |
| "last hour" | `@timestamp > NOW() - 1 hour` |
| "last 24 hours" | `@timestamp > NOW() - 24 hours` |
| "last 7 days" | `@timestamp > NOW() - 7 days` |
| "today" | `@timestamp >= DATE_TRUNC(1 day, NOW())` |
| "yesterday" | `@timestamp >= DATE_TRUNC(1 day, NOW()) - 1 day AND @timestamp < DATE_TRUNC(1 day, NOW())` |
| "this week" | `@timestamp >= DATE_TRUNC(1 week, NOW())` |
| "this month" | `@timestamp >= DATE_TRUNC(1 month, NOW())` |
**Default:** If no time range is specified, add a reasonable default (e.g., last 24 hours) to avoid scanning too much
data.
### 3. Identify Filters
**Question:** What conditions should narrow the results?
Look for:
- Status/level: "errors", "warnings", "successful"
- Environment: "production", "staging", "dev"
- Source/host: specific servers, services, applications
- Values: specific codes, IDs, names
```esql
// Multiple filters
| WHERE level == "error"
| WHERE environment == "production"
| WHERE service.name == "api-gateway"
```
Or combined:
```esql
| WHERE level == "error" AND environment == "production" AND service.name == "api-gateway"
```
**Negation and NULL values:** ES|QL uses three-valued logic. `WHERE field != "value"` silently excludes rows where the
field is `NULL` (missing). When generating negation filters, always add an `IS NULL` guard:
```esql
| WHERE environment != "test" OR environment IS NULL
```
### 4. Determine Output Type
**Question:** Does the user want raw data or aggregated results?
| User Intent | Approach |
| ------------------------------------ | ---------------------------------- |
| "show me", "list", "find" | Raw data with KEEP, SORT, LIMIT |
| "count", "how many" | STATS with COUNT |
| "average", "total", "sum" | STATS with aggregation function |
| "by X", "per X", "grouped by" | STATS ... BY grouping |
| "top N", "most common" | STATS + SORT DESC + LIMIT |
| "distribution", "breakdown" | STATS COUNT BY category |
| "over time", "trend" | STATS BY DATE_TRUNC |
| "patterns", "categorize", "types of" | STATS ... BY CATEGORIZE(field) |
| "spike", "dip", "anomaly", "change" | CHANGE_POINT value ON key |
| "patterns over time" | CATEGORIZE + BUCKET + CHANGE_POINT |
**Prefer single advanced queries over multiple basic ones.** When the user asks to "find patterns" or "analyze logs,"
use `CATEGORIZE` in one query rather than running several `STATS ... BY field` queries against different fields.
Similarly, use `CHANGE_POINT` to detect anomalies rather than producing hourly counts for the user to eyeball.
### 5. Select Fields
**Question:** What fields should be shown?
For raw data queries, use KEEP to select relevant fields:
```esql
| KEEP @timestamp, host.name, message, level
```
For aggregations, the output fields are defined by STATS:
```esql
| STATS count = COUNT(*), avg_time = AVG(response_time) BY endpoint
```
### 6. Apply Ordering and Limits
**Question:** How should results be ordered and limited?
- Time-based: `SORT @timestamp DESC`
- By count/value: `SORT count DESC`
- Alphabetical: `SORT name ASC`
**Always add LIMIT** unless the user specifically wants all results:
```esql
| LIMIT 100 // Reasonable default
| LIMIT 1000 // Maximum before considering pagination
```
---
## Field Name Conventions
When generating queries, use common field naming conventions:
### Elastic Common Schema (ECS)
| Category | Common Fields |
| ----------- | -------------------------------------------------------------- |
| Timestamp | `@timestamp` |
| Message | `message` |
| Log level | `log.level`, `level` |
| Host | `host.name`, `host.ip` |
| Service | `service.name`, `service.type` |
| HTTP | `http.request.method`, `http.response.status_code`, `url.path` |
| User | `user.name`, `user.id` |
| Source | `source.ip`, `source.port` |
| Destination | `destination.ip`, `destination.port` |
| Error | `error.message`, `error.type` |
| Event | `event.action`, `event.category`, `event.outcome` |
### Default to ECS Dotted Names
When schema discovery is not available and you must guess field names, always prefer ECS dotted notation over flat
names. Flat names like `source_ip` or `service` are common mistakes — most Elastic indices use the dotted ECS form.
| Prefer (ECS) | Avoid (flat) |
| ---------------- | ------------ |
| `source.ip` | `source_ip` |
| `service.name` | `service` |
| `event.category` | `event` |
| `event.outcome` | `outcome` |
| `host.name` | `hostname` |
### Legacy/Custom Fields
Some indices may use non-ECS field names:
- `status_code` instead of `http.response.status_code`
- `hostname` instead of `host.name`
- `timestamp` instead of `@timestamp`
**Recommendation:** Always run `./esql.js schema <index>` to discover actual field names before generating queries.
Never guess — index and field names vary across deployments.
---
## Query Optimization Tips
### 1. Filter Early
Put WHERE clauses as early as possible:
```esql
// Good - filter first
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| WHERE level == "error"
| STATS count = COUNT(*) BY host.name
// Less efficient - filtering after processing
FROM logs-*
| STATS count = COUNT(*) BY host.name, level
| WHERE level == "error"
```
### 2. Use Appropriate Time Ranges
Smaller time ranges = faster queries:
```esql
// Specific range is faster
| WHERE @timestamp > NOW() - 1 hour
// Than scanning all data
// (no time filter)
```
### 3. Limit Fields
Only keep fields you need:
```esql
// Good - specific fields
| KEEP @timestamp, message, host.name
// Less efficient - all fields
// (no KEEP command)
```
### 4. Use LIMIT
Prevent returning excessive rows:
```esql
| LIMIT 100 // Always include for raw data queries
```
### 5. Check for Pre-Existing Computed Fields
Before computing derived values (distances, durations, rates, etc.) with `EVAL`, check the schema for fields that were
already calculated at ingest time. Many indices pre-compute common values — using them is simpler and avoids
recomputation.
```esql
// Prefer: use the pre-computed field
FROM kibana_sample_data_flights
| STATS avg_distance = AVG(DistanceKilometers)
// Avoid: recomputing what already exists
FROM kibana_sample_data_flights
| EVAL distance_km = ST_DISTANCE(OriginLocation, DestLocation) / 1000
| STATS avg_distance = AVG(distance_km)
```
---
## Key Patterns
### Per-Aggregation WHERE (8.16+)
Use `COUNT(*) WHERE condition` instead of CASE-based workarounds to compute conditional metrics in a single pass:
```esql
FROM logs-*
| STATS
total = COUNT(*),
errors = COUNT(*) WHERE level == "error",
warnings = COUNT(*) WHERE level == "warning"
BY service.name
| EVAL error_rate = ROUND(errors * 100.0 / total, 2)
```
### LOOKUP JOIN and ENRICH
`LOOKUP JOIN` (8.18+) is the preferred way to enrich query results from another index. On clusters **before 8.18**, fall
back to `ENRICH` — it provides similar enrichment capability but requires a pre-configured enrich policy.
**If no enrich policy exists**, suggest the user create one. Example setup:
```bash
# 1. Create the enrich policy
PUT /_enrich/policy/customers_policy
{
"match": {
"indices": "customers",
"match_field": "customer_id",
"enrich_fields": ["name", "region", "email"]
}
}
# 2. Execute the policy (builds the enrich index)
POST /_enrich/policy/customers_policy/_execute
```
Then the query uses `ENRICH` instead of `LOOKUP JOIN`:
```esql
// 8.18+ — LOOKUP JOIN (preferred, no policy needed, easier to update)
FROM orders
| LOOKUP JOIN customers_lookup ON customer_id
| KEEP order_id, customer_id, name, region, total
// Pre-8.18 — ENRICH (requires policy setup above)
FROM orders
| ENRICH customers_policy ON customer_id WITH name, region
| KEEP order_id, customer_id, name, region, total
```
**Multi-field joins (9.2+):** Join on multiple fields when the lookup table has a composite key:
```esql
FROM application_logs
| LOOKUP JOIN service_registry ON service_name, environment
| KEEP service_name, environment, owner_team, response_time_ms
```
> Multi-field joins have no ENRICH equivalent — ENRICH only supports a single match field.
**Pre-join checklist:** Before writing any `LOOKUP JOIN`, verify these two things:
1. **Field name match:** Does the join key have the same name in both the source and lookup index? If not, add `RENAME`
before the join. This is a common source of silent failures.
2. **Composite key:** Does the lookup table require multiple fields to uniquely identify a row? If so, list all key
fields in the `ON` clause (9.2+).
**Field name mismatches:** When the join key has a different name in the source vs the lookup table, use `RENAME` before
the join:
```esql
FROM support_tickets
| RENAME product AS product_name
| LOOKUP JOIN knowledge_base ON product_name
| KEEP ticket_id, description, resolution
```
### Time Series (TS) Queries
When `schema` reports `Index mode: time_series`, use the `TS` source command instead of `FROM`. Critical syntax rules:
**1. Use the data stream name, not the resolved backing index:**
```esql
// WRONG — resolved backing index
FROM .ds-metrics-tsds-2026.03.09-000001
// CORRECT — data stream name (shown by schema command)
TS metrics-tsds
```
The `schema` command displays the data stream name when the index is a TSDS backing index.
**2. TBUCKET takes only a duration — not @timestamp:**
`TBUCKET` is not `DATE_TRUNC`. Do not pass `@timestamp`. Always assign a column alias so you can reference it in `SORT`:
```esql
// WRONG — DATE_TRUNC-style syntax
| STATS avg_cpu = AVG(cpu) BY bucket = TBUCKET(@timestamp, 5 minutes)
// WRONG — no alias makes SORT difficult
| STATS avg_cpu = AVG(cpu) BY TBUCKET(5 minutes)
// CORRECT — duration only with alias for SORT
| STATS avg_cpu = AVG(cpu) BY bucket = TBUCKET(5 minutes)
| SORT bucket
```
**3. Counter fields need RATE() wrapped in an outer aggregation:**
`RATE()` computes per-time-series rates. When grouping by non-time dimensions (e.g., `host`), wrap it in `SUM()`
(counters are additive). Bare `RATE() BY host` fails:
```esql
// WRONG — bare RATE with non-time grouping
TS metrics-tsds
| STATS request_rate = RATE(requests) BY host
// CORRECT — SUM wraps RATE for non-time groupings
TS metrics-tsds
| STATS request_rate = SUM(RATE(requests)) BY TBUCKET(1 hour), host
```
For **gauge** fields, use `AVG()` or `MAX()` as the outer function. Prefer the plain form — the inner `LAST_OVER_TIME`
is implicit and sufficient for most gauge queries. Use explicit `AVG_OVER_TIME` when you need the average of all samples
in the window (not just the last). Do not use `AVG_OVER_TIME` for histogram fields; see the histogram tip below:
```esql
// Preferred — plain aggregation (implicit LAST_OVER_TIME)
TS metrics-tsds
| STATS avg_cpu = AVG(cpu) BY TBUCKET(5 minutes), service.name
// Only use explicit *_OVER_TIME when you need specific window behavior
TS metrics-tsds
| STATS avg_cpu = AVG(AVG_OVER_TIME(cpu)) BY TBUCKET(5 minutes), service.name
```
See [Time Series Queries](time-series-queries.md) for the full inner/outer aggregation model.
**4. Histogram fields:** Check `field_type` with `METRICS_INFO` (`histogram`, `exponential_histogram`, or `tdigest` —
OTel is a common source; `metric_type` is `histogram` for all three). `exponential_histogram` and `tdigest` **merge by
default as inner, per-series aggregation** — use `SUM`/`AVG`/`COUNT`/`PERCENTILE`/…, not `*_OVER_TIME`. Plain
`histogram` is not usable without a cast. “Average over time” on a histogram means `AVG(field)`, not `AVG_OVER_TIME`:
```esql
// WRONG — *_OVER_TIME is the wrong shape for histogram metrics
TS metrics-* | STATS SUM(SUM_OVER_TIME(jvm.gc.duration)) BY TBUCKET(1 hour)
// CORRECT — exponential_histogram / tdigest: merge + standard aggregation (no cast)
TS metrics-tsds
| WHERE TRANGE(1 hour)
| STATS count = COUNT(jvm.gc.duration),
avg = AVG(jvm.gc.duration),
p99 = PERCENTILE(jvm.gc.duration, 99)
BY jvm.gc.action, TBUCKET(5 minutes)
```
Always cast with `::exponential_histogram` when plain `histogram` is present (alone or mixed) or a type error requires
it; omit the cast when the targeted streams are unambiguously `exponential_histogram` or `tdigest`. Prefer `::tdigest`
only when the metric actually stores T-Digest data. Cast table:
[Histogram Metrics](time-series-queries.md#histogram-metrics).
**Version status:** `TS`, `TBUCKET`, the new `WITHOUT(...)` grouping function, the new `METRICS_INFO` / `TS_INFO`
discovery commands, and **all** time series aggregation functions are **GA since 9.4** — including the 9.2-introduced
set (`RATE`, `IRATE`, `INCREASE`, `DELTA`, `IDELTA`, all `*_OVER_TIME`, `PRESENT_OVER_TIME`, `ABSENT_OVER_TIME`) and the
9.3-introduced set (`DERIV`, `PERCENTILE_OVER_TIME`, `STDDEV_OVER_TIME`, `VARIANCE_OVER_TIME`). On clusters in 9.2-9.3
these features are tech preview. `TRANGE` remains in preview.
**Pre-9.2 limitation:** The `TS` command, `RATE()`, `TBUCKET()`, and `AVG_OVER_TIME()` all require Elasticsearch
**9.2+**. On older clusters, counter fields (`counter_long`, `counter_double`) cannot be aggregated meaningfully —
standard aggregation functions like `MAX()`, `SUM()`, and `AVG()` reject counter field types. There is no workaround.
When the cluster is pre-9.2 and the question involves counter rates or time-series-specific aggregations, explain that
the `TS` command and `RATE()` are required (9.2+) and the query cannot be expressed on the current cluster version.
For **gauge** fields in time-series indices on pre-9.2 clusters, `FROM` with standard aggregations (`AVG`, `MAX`, `MIN`)
still works — only counter fields are affected.
**Sliding window restriction (9.2-9.3):** When the user wants a per-time-series aggregation window different from the
`TBUCKET` interval (`RATE(field, 10m) BY TBUCKET(1m)`), the window must be a multiple of the bucket interval on preview
clusters. **9.4+** (GA) accepts arbitrary windows.
### INLINE STATS (9.2+)
`INLINE STATS` is available in **9.2+** only. It computes an aggregation and appends the result as a new column to every
row (like a SQL window function). Use cases that require comparing individual rows to group-level aggregates (e.g.,
"find values above the group average", "percentage of total") depend on `INLINE STATS` and **cannot be expressed in
ES|QL before 9.2**. There is no fallback.
When the cluster is pre-9.2 and the question requires per-row vs. aggregate comparison, explain that `INLINE STATS` is
needed and suggest the user either upgrade or perform the comparison client-side.
### Pipe Commands: URI_PARTS, USER_AGENT, REGISTERED_DOMAIN (9.4+; Serverless)
These are **pipe commands** (like `DISSECT`/`GROK`), not scalar functions. They must appear on their own pipeline stage
with `target = expression` syntax. A target prefix is mandatory.
```esql
// WRONG — function-call syntax does not work
| EVAL parts = URI_PARTS(url.full)
// CORRECT — pipe command syntax with target prefix
| URI_PARTS parts = url.full
| KEEP parts.domain, parts.path, parts.scheme
```
When the user asks to "parse URLs", "extract domains", or "parse user agents", reach for these commands instead of
`DISSECT`/`GROK`:
| User Request | Command |
| ------------------------- | ------------------- |
| Parse/decompose a URL | `URI_PARTS` |
| Parse a user agent string | `USER_AGENT` |
| Extract registered domain | `REGISTERED_DOMAIN` |
### Grouped Top-N with LIMIT BY (9.4+; Serverless)
`LIMIT n BY field` keeps the top N rows per group after sorting. The number comes **before** `BY`.
```esql
// Top 3 error-producing hosts per service
FROM logs-*
| WHERE level == "error"
| STATS cnt = COUNT(*) BY service.name, host.name
| SORT cnt DESC
| LIMIT 3 BY service.name
```
This replaces the common `INLINE STATS` + rank-and-filter pattern for simple grouped top-N.
### Subqueries in FROM vs FORK
**Subqueries** (9.4+; Serverless) combine results from **different** data sources (UNION ALL semantics). **FORK** runs
**different analyses** on the **same** data source.
| Scenario | Use |
| ------------------------------------- | ---------- |
| Combine errors from two index sets | Subqueries |
| Run multiple aggregations on one set | FORK |
| Compare time windows of the same data | FORK |
| Union independent pipelines | Subqueries |
```esql
// Subqueries — different sources
FROM
(FROM web_logs | WHERE status >= 500 | KEEP @timestamp, message, service.name),
(FROM app_logs | WHERE level == "error" | KEEP @timestamp, message, service.name)
| SORT @timestamp DESC
// FORK — same source, different analyses
FROM logs-*
| FORK
( WHERE level == "error" | STATS errors = COUNT(*) BY service.name )
( WHERE level == "warning" | STATS warnings = COUNT(*) BY service.name )
```
### External IPs — CIDR_MATCH with RFC 1918
When the user asks about "external IPs" or "public IPs", exclude private (RFC 1918) ranges with `NOT CIDR_MATCH`:
```esql
FROM security-events
| WHERE event.outcome == "failure"
AND NOT CIDR_MATCH(source.ip, "10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16")
```
---
## Common Query Templates
### Error Investigation
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| WHERE level == "error"
| KEEP @timestamp, message, host.name, service.name, error.message
| SORT @timestamp DESC
| LIMIT 100
```
### Service Health Overview
```esql
FROM metrics-*
| WHERE @timestamp > NOW() - 15 minutes
| STATS
avg_cpu = AVG(system.cpu.percent),
avg_mem = AVG(system.memory.used.pct),
host_count = COUNT_DISTINCT(host.name)
BY service.name
| SORT avg_cpu DESC
```
### API Performance Analysis
```esql
FROM apm-*
| WHERE @timestamp > NOW() - 1 hour
| STATS
count = COUNT(*),
avg_duration = AVG(transaction.duration.us),
p95_duration = PERCENTILE(transaction.duration.us, 95),
error_count = COUNT(CASE(transaction.result != "success", 1, null))
BY transaction.name
| EVAL error_rate = ROUND(error_count * 100.0 / count, 2)
| SORT count DESC
| LIMIT 20
```
### Traffic Analysis
```esql
FROM web-logs
| WHERE @timestamp > NOW() - 24 hours
| STATS
requests = COUNT(*),
unique_ips = COUNT_DISTINCT(client.ip)
BY hour = DATE_TRUNC(1 hour, @timestamp)
| SORT hour DESC
```
### Security Event Review
```esql
FROM security-*
| WHERE @timestamp > NOW() - 24 hours
| WHERE event.category == "authentication"
| WHERE event.outcome == "failure"
| STATS
failures = COUNT(*)
BY user.name, source.ip
| WHERE failures > 5
| SORT failures DESC
```
---
## Handling Ambiguity
When the user request is ambiguous:
### Missing Index
If no index specified, make a reasonable assumption:
- "show errors" → `FROM logs-*`
- "show CPU usage" → `FROM metrics-*`
- "show requests" → `FROM web-logs` or `FROM access-*`
Or output the query with a placeholder and note:
```esql
FROM <index-pattern> // Specify your index
| WHERE ...
```
### Missing Time Range
Add a sensible default:
```esql
| WHERE @timestamp > NOW() - 24 hours // Default: last 24 hours
```
### Unclear Aggregation
When "show X" could mean list or count:
- If followed by "by Y" → aggregation
- If asking for specifics → raw data
- If asking "how many" → count
- Default to raw data with limit
### Unknown Field Names
If field names are uncertain:
1. Use common ECS names as first guess
2. Suggest running schema discovery
3. Note the assumption in output
---
## Output Formatting Suggestions
When presenting generated queries:
```text
=== ES|QL Query ===
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| WHERE level == "error"
| STATS count = COUNT(*) BY host.name
| SORT count DESC
| LIMIT 10
=== Explanation ===
- Queries all log indices
- Filters to the last hour
- Counts errors per host
- Returns top 10 hosts by error count
=== To Execute ===
./esql.js raw "FROM logs-* | WHERE @timestamp > NOW() - 1 hour | WHERE level == \"error\" | STATS count = COUNT(*) BY host.name | SORT count DESC | LIMIT 10"
```
references/promql-command.md
# ES|QL PROMQL Command
Query time series indices using **Prometheus Query Language (PromQL)** as a source command in ES|QL. The `PROMQL`
command is the bridge for users who already know PromQL or are migrating Prometheus dashboards and alerts onto an
Elasticsearch backend, while still letting them post-process results with regular ES|QL pipes.
> **Version:** `PROMQL` is a **preview** feature available since Elastic Stack **9.4** and on Elastic Cloud Serverless.
> Treat it as preview — syntax, options, and supported PromQL functions may change in future releases. See
> [esql-version-history.md](esql-version-history.md) for version availability.
## Table of Contents
- [When to Use PROMQL](#when-to-use-promql)
- [Syntax](#syntax)
- [Options](#options)
- [Output Columns](#output-columns)
- [Implicit Range Selectors](#implicit-range-selectors)
- [Examples](#examples)
- [Post-Processing with ES|QL](#post-processing-with-esql)
- [PROMQL vs TS](#promql-vs-ts)
- [Limitations](#limitations)
- [Kibana Time Filtering](#kibana-time-filtering)
- [Guidelines](#guidelines)
- [References](#references)
---
## When to Use PROMQL
Prefer `PROMQL` when **any** of the following apply:
- The user explicitly asks for a PromQL query, references Prometheus syntax (`sum by (instance) (...)`, label matchers
like `{cluster="prod"}`, etc), or is migrating a Prometheus dashboard or alert. If the user explicitly requests for
PromQL but the query is not supported yet (check [Limitations](#limitations) below), state the issue.
- Compatibility with Prometheus tooling is required (Grafana panels, alerting rules, scripts that already speak PromQL).
Prefer the [`TS` command](time-series-queries.md) when:
- The user wrote ES|QL (or is asking in natural language without PromQL terms) and the query is naturally expressed in
the inner/outer aggregation paradigm (`SUM(RATE(...))`, `AVG(AVG_OVER_TIME(...))`).
- The query mixes time series with non-time-series data sources or uses ES|QL features like `LOOKUP JOIN`,
`CHANGE_POINT`, or `INLINE STATS` _before_ the metrics aggregation.
`PROMQL` and `TS` target the same TSDS indices — choose based on the syntax that best matches the user's intent.
---
## Syntax
```esql
PROMQL [ <option> ... ] [ <result_name> = ] ( <PromQL expression> )
```
- Zero or more space-separated `key=value` options.
- A PromQL expression, optionally wrapped in parentheses and assigned a `<result_name>`.
- The expression follows standard
[Prometheus query language](https://prometheus.io/docs/prometheus/latest/querying/basics/) syntax (label matchers,
range selectors, aggregations, binary operations) within the [Limitations](#limitations) below.
### Minimal example
```esql
PROMQL sum by (instance) (rate(http_requests_total))
```
### Named result
```esql
PROMQL http_rate = (sum by (instance) (rate(http_requests_total)))
```
When a `<result_name>` is provided, the metric column is named `<result_name>` instead of the raw PromQL expression. In
the example above, the column would be named `http_rate`.
---
## Options
The options mirror the Prometheus [HTTP API](https://prometheus.io/docs/prometheus/latest/querying/api/#range-queries)
with ES|QL-specific additions.
| Option | Default | Description |
| ----------------- | ----------- | --------------------------------------------------------------------------------------------------------------------- |
| `index` | `metrics-*` | Indices, data streams, or aliases. Supports wildcards and date math. |
| `step` | inferred | Query resolution step width. Auto-derived from `buckets` and the time range when omitted. |
| `buckets` | `100` | Target bucket count for auto-step derivation. Mutually exclusive with `step`. Requires a known time range. |
| `start` | inferred | Inclusive start of the time range. Falls back to Kibana's date picker, or unrestricted if missing. |
| `end` | inferred | Inclusive end of the time range. Falls back to Kibana's date picker, or unrestricted if missing. |
| `scrape_interval` | `1m` | Expected metric collection interval. Used as the implicit range selector window: `max(step, scrape_interval)`. |
| `<result_name>=` | _none_ | Optional name for the metric output column. Defaults to the PromQL expression text. Wrap the expression in `( ... )`. |
**Time format for `start` / `end`:** ISO-8601 strings (e.g., `"2026-04-01T00:00:00Z"`). The same formats accepted by
`TRANGE` work here.
**`step` vs `buckets`:** Pass exactly one. `step` fixes the resolution (`step=5m`); `buckets` lets the engine pick a
step that produces around N buckets across the time range (`buckets=50`).
---
## Output Columns
The result table has these columns:
| Column | Type | Description |
| ------------------------------------------------------- | --------- | --------------------------------------------------------------- |
| The PromQL expression (or `<result_name>` if specified) | `double` | The computed metric value |
| `step` | `date` | Timestamp for each evaluation step |
| Grouping labels (when `by (...)` or `without (...)`) | `keyword` | One column per grouping label |
| `_timeseries` | `keyword` | JSON-encoded labels when there is no `by`/`without` aggregation |
When the PromQL expression includes a cross-series aggregation like `sum by (instance) (...)`, each grouping label
becomes its own column (`instance:keyword`). Without a cross-series aggregation, all labels collapse into a single
`_timeseries` column as a JSON string.
---
## Implicit Range Selectors
Standard PromQL requires range vector functions to specify a range selector: `rate(http_requests_total[5m])`. The
`PROMQL` command **allows omitting the range selector** entirely:
```esql
PROMQL scrape_interval=15s sum(rate(http_requests_total))
```
When the range selector is absent, the window is computed automatically as `max(step, scrape_interval)`. This is
particularly useful for Kibana dashboards where `step` is determined by the date picker and you want the range vector to
scale with it.
You can still pass an explicit range selector when you need a fixed window: `rate(http_requests_total[5m])`.
---
## Examples
### Fully adaptive query (recommended for Kibana)
Let Kibana's date picker drive the time range, and let `step` and the range selector be inferred:
```esql
PROMQL index=metrics-* sum by (instance) (rate(http_requests_total))
```
The query responds to the date picker, adjusts the step size to the selected range, and sizes the implicit range
selector window accordingly. This is the recommended pattern for dashboard panels.
### Range query with explicit parameters
```esql
PROMQL index=k8s step=5m start="2024-05-10T00:20:00.000Z" end="2024-05-10T00:25:00.000Z" (
sum(avg_over_time(network.cost[5m]))
)
```
| sum(avg_over_time(network.cost[5m])):double | step:date |
| ------------------------------------------- | ------------------------ |
| 50.25 | 2024-05-10T00:20:00.000Z |
### Cross-series aggregation by label
```esql
PROMQL index=k8s step=1h result=(sum by (cluster) (network.cost))
| SORT result
```
| result:double | step:datetime | cluster:keyword |
| ------------- | ------------------------ | --------------- |
| 15.875 | 2024-05-10T00:00:00.000Z | staging |
| 18.625 | 2024-05-10T00:00:00.000Z | prod |
| 26.5 | 2024-05-10T00:00:00.000Z | qa |
### Label filtering with named result
```esql
PROMQL index=k8s step=1h cost=(max by (cluster) (network.total_bytes_in{cluster!="prod"}))
| SORT cluster
```
| cost:double | step:datetime | cluster:keyword |
| ----------- | ------------------------ | --------------- |
| 10797.0 | 2024-05-10T00:00:00.000Z | qa |
| 7403.0 | 2024-05-10T00:00:00.000Z | staging |
### Ad-hoc query with inferred step
For queries outside Kibana, set `start` and `end` explicitly. The step and range selector window are still inferred from
the time range and the default `buckets` value:
```esql
PROMQL index=metrics-*
start="2026-04-01T00:00:00Z"
end="2026-04-01T01:00:00Z"
sum by (instance) (rate(http_requests_total))
```
### Bucket count instead of fixed step
```esql
PROMQL index=metrics-*
buckets=50
start="2026-04-01T00:00:00Z"
end="2026-04-01T01:00:00Z"
sum(rate(http_requests_total))
```
---
## Post-Processing with ES|QL
Because `PROMQL` is a source command, its output flows into the rest of the pipeline. Use ES|QL commands after the
PROMQL stage for further aggregation, filtering, ordering, and enrichment:
```esql
PROMQL index=k8s step=1h bytes=(max by (cluster) (network.bytes_in))
| STATS max_bytes = MAX(bytes) BY cluster
| SORT cluster
```
| max_bytes:double | cluster:keyword |
| ---------------- | --------------- |
| 931.0 | prod |
| 972.0 | qa |
| 238.0 | staging |
### Enrich with LOOKUP JOIN
Join PromQL results with a lookup index using a grouping label as the join key:
```esql
PROMQL index=metrics-*
http_rate=(sum by (instance) (rate(http_requests_total)))
| LOOKUP JOIN instance_metadata ON instance
```
This pattern combines PromQL's expressiveness for time series math with ES|QL's strengths for joining external metadata,
filtering, and shaping output.
---
## PROMQL vs TS
| Aspect | `PROMQL` | `TS` |
| ------------------- | ------------------------------------------- | -------------------------------------------------- |
| Syntax | Prometheus Query Language | ES\|QL inner/outer aggregation |
| Default index | `metrics-*` | None — caller must specify |
| Time filtering | `start`/`end` options or Kibana date picker | `WHERE TRANGE(...)` or `WHERE @timestamp ...` |
| Bucketing | `step` / `buckets` options | `BY TBUCKET(interval)` |
| Range vector window | Implicit (`max(step, scrape_interval)`) | Bucket interval, or sliding window arg (9.3+) |
| Counter aggregation | `sum(rate(metric))` | `STATS SUM(RATE(metric)) BY TBUCKET(...)` |
| Gauge aggregation | `avg_over_time(metric[5m])` | `STATS AVG(AVG_OVER_TIME(metric)) BY TBUCKET(...)` |
| Label filtering | `metric{cluster="prod"}` | `WHERE cluster == "prod"` |
| Available since | 9.4 (preview) | 9.2 (preview) |
Both commands target TSDS indices and can be followed by the same set of ES|QL processing commands (`WHERE`, `EVAL`,
`STATS`, `SORT`, `LIMIT`, `LOOKUP JOIN`, etc.).
---
## Limitations
In 9.4 preview, `PROMQL` has the following limitations:
- **Group modifiers are not supported.** Constructs like `on(chip) group_left(chip_name)` will fail. Use `LOOKUP JOIN`
in ES|QL after the PROMQL stage to attach extra labels.
- **Set operators are not supported.** `or`, `and`, and `unless` between PromQL expressions are unavailable. Express set
logic in ES|QL after the PROMQL stage instead.
- **Some PromQL functions are unavailable.** Notably `histogram_quantile`, `predict_linear`, and `label_join` are not
supported. Use `TS` with `PERCENTILE_OVER_TIME` for percentile-style metrics, or compute equivalents in ES|QL.
- **Time bucket alignment differs.** Buckets align to fixed calendar boundaries rather than the query start time. This
can cause slight differences from native Prometheus, especially for short ranges or large step sizes.
- **Index defaults to `metrics-*`.** If your TSDS data lives elsewhere, always set `index` explicitly to avoid scanning
unrelated indices.
- **Preview status.** Behavior, supported PromQL surface, and option names may evolve before GA.
When a question requires a feature in this list, fall back to the [`TS` command](time-series-queries.md) and express the
equivalent computation in ES|QL.
---
## Kibana Time Filtering
When writing `PROMQL` queries for Kibana (Discover, dashboards, alerts), **do not set `start` and `end` manually**.
Kibana injects the date picker's range automatically and the engine derives `step` from it. Setting `start`/`end`
explicitly overrides the date picker.
```esql
// Kibana — let the date picker drive start/end and step
PROMQL index=metrics-* sum by (instance) (rate(http_requests_total))
```
For ad-hoc queries outside Kibana (direct `POST /_query`), set `start` and `end` explicitly.
---
## Guidelines
- **Prefer `PROMQL` only when the user explicitly thinks in PromQL** or is porting a Prometheus query/dashboard.
Otherwise, prefer `TS` — it integrates more naturally with the rest of ES|QL and is GA in 9.4.
- **Always set `index`** in production queries instead of relying on the `metrics-*` default — narrower patterns reduce
scan volume and prevent accidental matches against unrelated indices.
- **Use named results** (`http_rate=(...)`) when chaining further ES|QL commands. Named columns are easier to reference
than the raw PromQL expression text.
- **Omit range selectors for adaptive dashboards.** Implicit range selectors (`rate(http_requests_total)` without
`[5m]`) make the query scale with the date picker.
- **Pick `step` or `buckets`, not both.** Use `buckets` when you want a target panel resolution; use `step` when you
need a fixed grain (e.g., to align with downstream aggregation).
- **Fall back to `TS` for unsupported features.** Histograms (`histogram_quantile`), set logic (`or`/`and`/`unless`),
group modifiers, and `label_join` are not available — express the computation with ES|QL primitives instead.
- **Do not mix `WHERE @timestamp` filters with `start`/`end`.** Time filtering belongs in the PROMQL options or via
Kibana's date picker; standard ES|QL `WHERE` clauses run _after_ the PromQL stage and don't bound the metric scan.
---
## References
- [ES|QL PROMQL command](https://www.elastic.co/docs/reference/query-languages/esql/commands/promql) — official
documentation
- [Prometheus Query Language](https://prometheus.io/docs/prometheus/latest/querying/basics/) — PromQL fundamentals
- [Prometheus HTTP API](https://prometheus.io/docs/prometheus/latest/querying/api/#range-queries) — origin of the option
semantics
- [Time series data streams (TSDS)](https://www.elastic.co/docs/manage-data/data-store/data-streams/time-series-data-stream-tsds)
- [time-series-queries.md](time-series-queries.md) — `TS` command and ES|QL native time series functions
- [esql-version-history.md](esql-version-history.md) — feature availability by Elasticsearch version
references/query-approximation.md
# ES|QL Query Approximation (Approximate STATS)
Approximate `STATS` aggregations using random sampling and extrapolation. Enabling approximation makes ES|QL rewrite the
query to sample rows and extrapolate, returning estimates together with confidence intervals and a certification flag
instead of exact results. Use it when the user runs heavy `STATS` summaries over large datasets and approximate results
with known error bounds are acceptable in exchange for dramatically faster execution.
> **Version:** Approximation is **GA on Elastic Cloud Serverless and Elastic Stack 9.5+**. It was introduced as a
> preview in Elastic Stack 9.4. It is controlled by the `approximation` setting of the
> [`SET` directive](esql-reference.md#query-directives) (preview since 9.3). See
> [esql-version-history.md](esql-version-history.md) for version availability.
Approximation breaks the dependency between performance and dataset size: accuracy depends mainly on the data and the
query, not on how many rows are in the source index, so the speed advantage grows as the data grows.
## Table of Contents
- [Enabling Approximation](#enabling-approximation)
- [Understanding the Output](#understanding-the-output)
- [Configuration Options](#configuration-options)
- [Supported Aggregation Functions](#supported-aggregation-functions)
- [Unsupported Query Patterns](#unsupported-query-patterns)
- [When Approximation Is Less Effective](#when-approximation-is-less-effective)
- [Exact Execution via Index Summary Statistics](#exact-execution-via-index-summary-statistics)
- [Using SAMPLE Directly](#using-sample-directly)
- [Guidelines](#guidelines)
- [Summary](#summary)
- [References](#references)
---
## Enabling Approximation
Prepend `SET approximation=true;` to an existing `STATS` query. No other change to the query is required — the rewrite
(sampling, extrapolation, confidence interval computation) is automatic.
```esql
SET approximation=true;
FROM web_traffic
| WHERE @timestamp >= NOW() - 1 week
| STATS total_hits = COUNT(),
avg_load_time = AVG(page_load_ms)
BY country_code
| SORT total_hits DESC
| LIMIT 5
```
## Understanding the Output
An approximate query returns the same columns as the exact query, plus **two extra columns for each estimated
quantity**:
- `_approximation_confidence_interval(<col>)` — the central **90%** confidence interval for the estimate: an interval
that has a 0.9 probability of containing the true value.
- `_approximation_certified(<col>)` — a boolean. When `true`, the statistical assumptions behind the interval hold and
the confidence interval is trustworthy. When `false`, the estimate may still be accurate, but the distribution could
not be confirmed to satisfy those assumptions — treat the interval with caution.
For example, a query computing `total_hits = COUNT()` and `avg_load_time = AVG(page_load_ms)` `BY country_code` returns
the `total_hits`, `avg_load_time`, and `country_code` columns plus `_approximation_confidence_interval(total_hits)`,
`_approximation_certified(total_hits)`, `_approximation_confidence_interval(avg_load_time)`, and
`_approximation_certified(avg_load_time)`.
## Configuration Options
The defaults work well for most queries. Tune them by passing a map value to `approximation` instead of `true`.
Map entries:
- `rows` (integer) — number of sampled rows used to approximate the query. Must be **at least 10,000**. `null` uses the
system default. Defaults: **1,000,000** rows for grouped `STATS` (queries with a `BY` clause) and **100,000** rows
otherwise.
- `confidence_level` (double) — confidence level of the computed intervals. Default **0.90**. `null` **disables**
confidence interval (and certification) computation, which can yield an additional speedup.
### Disabling confidence intervals
Skip interval and certification computation when only point estimates are needed:
```esql
SET approximation={"confidence_level":null};
FROM web_traffic
| WHERE @timestamp >= NOW() - 1 day
| STATS total_bytes = SUM(response_bytes),
avg_load_time = AVG(page_load_ms)
BY datacenter_region
| SORT total_bytes DESC
| LIMIT 10
```
### Controlling the sample size
Increase `rows` when results are too imprecise — particularly for high-cardinality grouping. Larger samples improve
accuracy at the cost of reduced speedup; as long as the sample stays well below the total row count, there is still a
performance benefit.
```esql
SET approximation={"rows":5000000};
FROM web_traffic
| WHERE @timestamp >= NOW() - 1 week
| STATS total_hits = COUNT(*),
avg_load_time = AVG(page_load_ms)
BY url_path
| SORT total_hits DESC
| LIMIT 25
```
Both options can be combined: `SET approximation={"rows":2000000,"confidence_level":0.95};`.
## Supported Aggregation Functions
Approximation applies to aggregation functions where sampling and extrapolation produce statistically sound estimates
(for example `COUNT`, `COUNT(*)`, `SUM`, `AVG`, `MEDIAN`, `PERCENTILE`).
The following aggregation functions are **not supported** and cause the query to **fall back to exact execution**:
`COUNT_DISTINCT`, `MIN`, `MAX`, `FIRST`, `LAST`, `TOP`, `ABSENT`, `PRESENT`, `ST_CENTROID_AGG`, `ST_EXTENT_AGG`.
Some of these (e.g. `MIN`, `MAX`) are intrinsically hard to estimate reliably from a sample without strong
distributional assumptions, so they are excluded to avoid accidental misuse. For `COUNT_DISTINCT` and similar, use the
[`SAMPLE` command](#using-sample-directly) instead.
## Unsupported Query Patterns
These patterns are not supported for approximation and fall back to exact execution:
- Queries using the `TS` or `PROMQL` source command.
- Pipelines containing **two or more `STATS` commands**.
The `FORK`, `LOOKUP JOIN`, and `INLINE STATS` processing commands **are** supported since version 9.5.
## When Approximation Is Less Effective
Approximation works best on large, broad `STATS` queries. Two patterns reduce or eliminate the benefit:
### Highly selective filters
If a `WHERE` clause matches only a small fraction of the data, the data is already small and sampling adds little. ES|QL
detects this during the rewrite and falls back to exact execution — but the rewrite itself adds overhead. If you know in
advance the query matches very few rows, run it **without** approximation.
### High-cardinality grouping
When the `BY` expression has very high cardinality, individual groups may receive very few sampled rows. This can cause:
- Groups with **fewer than 10 samples** being dropped entirely from results.
- Large estimation errors for retained groups.
- No results at all if the grouping field is unique per document.
Sorting by ascending count (finding the rarest groups) is especially problematic, since heavy hitters may require
sampling most of the dataset. If accuracy for high-cardinality queries matters, increase `rows`. As a rule of thumb, aim
for at least a few hundred samples per group.
## Exact Execution via Index Summary Statistics
Some aggregations can be computed directly from summary statistics maintained in the index (for example, a simple
`COUNT(*)` over an indexed numeric field with no grouping). The planner detects these cases and runs them exactly, since
they are already fast. No action is needed — when this happens, the confidence intervals have **zero length**,
indicating the results are exact.
## Using SAMPLE Directly
For full control, or for aggregations not supported by automatic approximation (such as `COUNT_DISTINCT`), use the
[`SAMPLE` command](esql-reference.md#sample). It gives raw sampled data with **no** automatic extrapolation or
confidence interval computation — interpreting the result and accounting for sampling bias is your responsibility.
```esql
// Distinct count over ~1% of the data (COUNT_DISTINCT is not supported by automatic approximation)
FROM web_traffic
| SAMPLE 0.01
| STATS unique_visitors = COUNT_DISTINCT(client_ip)
// Frequency profile over a sample; adjust the probability to observe convergence
FROM web_traffic
| SAMPLE 0.01
| STATS c = COUNT(*) BY search_phrase
```
## Guidelines
1. **Use approximation when**: the user runs a large `STATS` summary, exact values are not strictly required, and faster
results are desirable. The benefit grows with dataset size.
2. **Do not use approximation when**: the query is highly selective (matches few rows), needs exact results, or uses an
unsupported function or query pattern (it will silently fall back to exact, adding only rewrite overhead).
3. **Always surface the error bounds**: when reporting approximate results, include or mention the
`_approximation_confidence_interval(...)` values and check `_approximation_certified(...)`. Do not present
approximate estimates as exact figures.
4. **Zero-length intervals mean exact**: a confidence interval of zero length indicates the planner executed the query
exactly from index summary statistics.
5. **Disable intervals for max speed**: when only point estimates are needed, set `confidence_level` to `null`.
6. **Tune `rows` for high-cardinality grouping**: increase the sample size (minimum 10,000; default 1,000,000 grouped /
100,000 ungrouped) until groups have at least a few hundred samples each.
7. **Reach for `SAMPLE`** when an unsupported function like `COUNT_DISTINCT` is required.
## Summary
| Aspect | Detail |
| ------------------------------- | --------------------------------------------------------------------------------------------------------------- |
| Enable approximation | `SET approximation=true;` |
| Disable confidence intervals | `SET approximation={"confidence_level":null};` |
| Custom sample size | `SET approximation={"rows":N};` (`N` ≥ 10,000) |
| Default sample size (grouped) | 1,000,000 rows |
| Default sample size (ungrouped) | 100,000 rows |
| Confidence interval default | Central 90% interval (`confidence_level` 0.90) |
| Minimum samples per group | 10 (groups below this are dropped) |
| Added output columns | `_approximation_confidence_interval(col)`, `_approximation_certified(col)` |
| Unsupported functions | `COUNT_DISTINCT`, `MIN`, `MAX`, `FIRST`, `LAST`, `TOP`, `ABSENT`, `PRESENT`, `ST_CENTROID_AGG`, `ST_EXTENT_AGG` |
| Falls back to exact | `TS`/`PROMQL` source; 2+ `STATS` commands; highly selective filters |
## References
- [Approximate STATS queries](https://www.elastic.co/docs/reference/query-languages/esql/esql-query-approximation)
- [ES|QL SET directive — `approximation`](https://www.elastic.co/docs/reference/query-languages/esql/commands/set#esql-approximation)
references/query-patterns.md
# ES|QL Query Patterns
Common patterns for generating ES|QL queries from natural language requests.
## Table of Contents
- [Pattern Recognition Guide](#pattern-recognition-guide)
- [Time-Based Queries](#time-based-queries)
- [Aggregation Patterns](#aggregation-patterns)
- [Filtering Patterns](#filtering-patterns)
- [Transformation Patterns](#transformation-patterns)
- [Log Parsing Patterns](#log-parsing-patterns)
- [Advanced Patterns](#advanced-patterns)
- [Newer Feature Patterns](#newer-feature-patterns)
- [ML and Analytics Patterns](#ml-and-analytics-patterns)
- [Common Mistakes to Avoid](#common-mistakes-to-avoid)
## Pattern Recognition Guide
When translating natural language to ES|QL, identify these key elements:
| User Says | ES\|QL Element |
| -------------------------------------- | ---------------------------------------------- |
| "show," "list," "get," "find" | `FROM` + `KEEP` (select fields) |
| "from," "in" (index) | `FROM index-pattern` |
| "where," "with," "that have," "filter" | `WHERE condition` |
| "last X hours/days," "since" | `WHERE @timestamp > NOW() - X time` |
| "between date X and date Y" | `WHERE @timestamp >= "X" AND @timestamp < "Y"` |
| "count," "how many" | `STATS count = COUNT(*)` |
| "average," "mean" | `STATS avg = AVG(field)` |
| "total," "sum" | `STATS total = SUM(field)` |
| "maximum," "highest," "top value" | `STATS max = MAX(field)` |
| "minimum," "lowest" | `STATS min = MIN(field)` |
| "by," "per," "grouped by," "for each" | `... BY field` |
| "top N," "first N," "limit" | `LIMIT N` |
| "sorted by," "order by" | `SORT field [DESC/ASC]` |
| "unique," "distinct" | `STATS COUNT_DISTINCT(field)` |
| "contains," "includes" | `WHERE field LIKE "*value*"` or `MATCH()` |
| "starts with" | `WHERE STARTS_WITH(field, "prefix")` |
| "ends with" | `WHERE ENDS_WITH(field, "suffix")` |
| "change point," "spike," "dip" | `CHANGE_POINT value ON key` |
| "categorize logs," "group messages" | `STATS ... BY category = CATEGORIZE(message)` |
---
## Time-Based Queries
### Recent Data
```text
"show errors from the last hour"
→
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| WHERE level == "error"
| SORT @timestamp DESC
| LIMIT 100
```
### Time Range
```text
"events between January 1 and January 15, 2024"
→
FROM events-*
| WHERE @timestamp >= "2024-01-01" AND @timestamp < "2024-01-16"
```
### Time Bucketing
```text
"count events per hour for today"
→
FROM events-*
| WHERE @timestamp > NOW() - 24 hours
| STATS count = COUNT(*) BY bucket = DATE_TRUNC(1 hour, @timestamp)
| SORT bucket DESC
```
### Time Comparisons
```text
"requests slower than 5 seconds"
→
FROM api-logs
| WHERE response_time > 5000
| SORT response_time DESC
| LIMIT 100
```
---
## Aggregation Patterns
### Simple Count
```text
"how many errors are there"
→
FROM logs-*
| WHERE level == "error"
| STATS total_errors = COUNT(*)
```
### Count by Category
```text
"count of events by status code"
→
FROM web-logs
| STATS count = COUNT(*) BY status_code
| SORT count DESC
```
### Multiple Aggregations
```text
"show min, max, and average response time"
→
FROM api-logs
| STATS
min_time = MIN(response_time),
max_time = MAX(response_time),
avg_time = AVG(response_time)
```
### Grouped Multiple Aggregations
```text
"average and max CPU per host"
→
FROM metrics-*
| STATS
avg_cpu = AVG(system.cpu.percent),
max_cpu = MAX(system.cpu.percent)
BY host.name
| SORT avg_cpu DESC
```
### Top N Pattern
```text
"top 10 hosts by error count"
→
FROM logs-*
| WHERE level == "error"
| STATS error_count = COUNT(*) BY host.name
| SORT error_count DESC
| LIMIT 10
```
### Percentiles
```text
"p50, p95, p99 response times by endpoint"
→
FROM api-logs
| STATS
p50 = PERCENTILE(response_time, 50),
p95 = PERCENTILE(response_time, 95),
p99 = PERCENTILE(response_time, 99)
BY endpoint
| SORT p99 DESC
```
### Unique Counts
```text
"count of unique users per day"
→
FROM user-events
| STATS unique_users = COUNT_DISTINCT(user_id) BY day = DATE_TRUNC(1 day, @timestamp)
| SORT day DESC
```
---
## Filtering Patterns
### Exact Match
```text
"errors from production"
→
FROM logs-*
| WHERE level == "error" AND environment == "production"
```
### Multiple Values (IN)
```text
"events with status 400, 401, or 403"
→
FROM web-logs
| WHERE status_code IN (400, 401, 403)
```
### Pattern Matching
```text
"requests to /api endpoints"
→
FROM web-logs
| WHERE url LIKE "/api/*"
```
### Full-Text Search (8.17+)
```text
"documents containing 'connection timeout'"
→
FROM logs-* METADATA _score
| WHERE MATCH(message, "connection timeout")
| SORT _score DESC
| LIMIT 100
```
### Null Handling
```text
"records where error field exists"
→
FROM logs-*
| WHERE error IS NOT NULL
```
### Negation
**Warning:** ES|QL uses three-valued logic. `!=` excludes rows where the field is `NULL` (missing). Include an explicit
`IS NULL` check to avoid silent false negatives.
```text
"all events except from test environment"
→
FROM events-*
| WHERE environment != "test" OR environment IS NULL
```
---
## Transformation Patterns
### Computed Fields
```text
"show response time in seconds"
→
FROM api-logs
| EVAL response_time_sec = response_time_ms / 1000
| KEEP endpoint, response_time_sec
```
### String Manipulation
```text
"extract domain from email addresses"
→
FROM users
| EVAL domain = SUBSTRING(email, LOCATE("@", email) + 1, LENGTH(email))
| KEEP email, domain
```
### Conditional Values
```text
"categorize response times as fast/medium/slow"
→
FROM api-logs
| EVAL speed_category = CASE(
response_time < 100, "fast",
response_time < 500, "medium",
"slow"
)
| STATS count = COUNT(*) BY speed_category
```
### Rate Calculation
```text
"error rate percentage by service"
→
FROM logs-*
| STATS
total = COUNT(*),
errors = COUNT(CASE(level == "error", 1, null))
BY service.name
| EVAL error_rate = ROUND(errors * 100.0 / total, 2)
| SORT error_rate DESC
```
**Simpler with per-aggregation WHERE (8.16+):**
```text
FROM logs-*
| STATS
total = COUNT(*),
errors = COUNT(*) WHERE level == "error"
BY service.name
| EVAL error_rate = ROUND(errors * 100.0 / total, 2)
| SORT error_rate DESC
```
---
## Log Parsing Patterns
### GROK for Structured Extraction
```text
"parse Apache access logs"
→
FROM raw-logs
| GROK message "%{IP:client_ip} - - \\[%{HTTPDATE:timestamp}\\] \"%{WORD:method} %{URIPATHPARAM:path} HTTP/%{NUMBER:http_version}\" %{NUMBER:status:int} %{NUMBER:bytes:int}"
| KEEP client_ip, method, path, status, bytes
```
### DISSECT for Simple Patterns
```text
"extract user and action from 'User X performed Y'"
→
FROM audit-logs
| DISSECT message "User %{user} performed %{action}"
| STATS count = COUNT(*) BY user, action
```
---
## Advanced Patterns
### Multi-Index Query
```text
"combine data from logs and metrics"
→
FROM logs-*, metrics-*
| WHERE @timestamp > NOW() - 1 hour
| KEEP @timestamp, host.name, message, cpu.percent
```
### Data Enrichment with LOOKUP JOIN
Prefer `LOOKUP JOIN` over `ENRICH` — no policy setup required, changes reflected immediately.
```text
"add user info to logs"
→
FROM logs-*
| LOOKUP JOIN users ON user.id
| KEEP @timestamp, message, user.name, user.department
| SORT @timestamp DESC
| LIMIT 100
```
```text
"enrich security events with threat intelligence"
→
FROM security-events
| LOOKUP JOIN threat_intel ON source.ip
| WHERE threat_level IS NOT NULL
| KEEP @timestamp, source.ip, threat_level, threat_type
| SORT @timestamp DESC
```
### Data Enrichment with ENRICH
Use `ENRICH` when a pre-configured enrich policy already exists (e.g., GeoIP) or on versions prior to LOOKUP JOIN.
```text
"add geo info to IP addresses"
→
FROM web-logs
| ENRICH geoip-policy ON client.ip WITH country_name, city_name
| STATS requests = COUNT(*) BY country_name
| SORT requests DESC
```
### Multivalue Handling
```text
"count occurrences of each tag"
→
FROM documents
| MV_EXPAND tags
| STATS count = COUNT(*) BY tags
| SORT count DESC
```
### Chained Aggregations
```text
"average daily count per week"
→
FROM events
| STATS daily_count = COUNT(*) BY day = DATE_TRUNC(1 day, @timestamp)
| STATS avg_daily = AVG(daily_count) BY week = DATE_TRUNC(1 week, day)
| SORT week DESC
```
---
## Newer Feature Patterns
### Per-Aggregation WHERE Filters (8.16+)
```text
"count of successful, failed, and total requests by endpoint"
→
FROM web-logs
| STATS
total = COUNT(*),
success = COUNT(*) WHERE status_code >= 200 AND status_code < 300,
errors = COUNT(*) WHERE status_code >= 400
BY endpoint
| EVAL error_rate = ROUND(errors * 100.0 / total, 2)
| SORT error_rate DESC
```
### INLINE STATS (9.2+)
```text
"show each employee's salary compared to their department average"
→
FROM employees
| INLINE STATS dept_avg = AVG(salary) BY department
| EVAL diff_from_avg = ROUND(salary - dept_avg, 2)
| KEEP name, department, salary, dept_avg, diff_from_avg
| SORT diff_from_avg DESC
```
```text
"find flights longer than the average distance for their destination"
→
FROM flights
| INLINE STATS avg_dist = AVG(distance) BY destination
| WHERE distance > avg_dist
| KEEP flight_id, destination, distance, avg_dist
```
### Grouped Top-N with LIMIT BY (9.4+; Serverless)
```text
"top 3 error types per service"
→
FROM logs-*
| WHERE @timestamp > NOW() - 24 hours AND level == "error"
| STATS cnt = COUNT(*) BY service.name, error.type
| SORT cnt DESC
| LIMIT 3 BY service.name
```
```text
"most recent event per user"
→
// 9.4+/Serverless: use LATEST to get the most recent value per group
FROM events-*
| STATS last_action = LATEST(event.action), last_ts = LATEST(@timestamp) BY user.name
// Alternative with LIMIT BY
FROM events-*
| SORT @timestamp DESC
| LIMIT 1 BY user.name
| KEEP user.name, @timestamp, event.action
```
### Subquery Composition (9.4+; Serverless)
```text
"combine web server errors and application errors into one view"
→
FROM
(FROM web_logs
| WHERE @timestamp > NOW() - 1 hour AND status_code >= 500
| EVAL source = "web"
| KEEP @timestamp, message, service.name, source),
(FROM app_logs
| WHERE @timestamp > NOW() - 1 hour AND level == "error"
| EVAL source = "app"
| KEEP @timestamp, message, service.name, source)
| SORT @timestamp DESC
| LIMIT 100
```
```text
"count errors from different log sources by service"
→
FROM
(FROM web_logs | WHERE status_code >= 500 | KEEP @timestamp, service.name),
(FROM app_logs | WHERE level == "error" | KEEP @timestamp, service.name)
| STATS errors = COUNT(*) BY service.name
| SORT errors DESC
```
### MATCH_PHRASE (8.19/9.1+)
```text
"find documents with the exact phrase 'out of memory'"
→
FROM logs-* METADATA _score
| WHERE MATCH_PHRASE(message, "out of memory")
| SORT _score DESC
| LIMIT 50
```
---
## ML and Analytics Patterns
### Change Point Detection
Use when the user wants to find when a metric spiked, dipped, or changed trend. Requires a time-ordered series (e.g.
hourly/daily counts).
```text
"when did request rate spike in the last 24 hours"
→
FROM logs-*
| WHERE @timestamp > NOW() - 24 hours
| STATS c = COUNT(*) BY t = BUCKET(@timestamp, 30 seconds)
| SORT t
| CHANGE_POINT c ON t
| WHERE type IS NOT NULL
```
### Log Categorization (CATEGORIZE)
Use when the user wants to group log messages by similar format or see "types" of log lines.
```text
"group similar log messages" / "what types of errors do we have"
→
FROM logs-*
| WHERE @timestamp > NOW() - 24 hours
| STATS count = COUNT() BY category = CATEGORIZE(message)
| SORT count DESC
| LIMIT 20
```
### Change Points in Category Counts
Use when the user wants to find when counts per log category spiked or changed over time. Combines CATEGORIZE with time
bucketing and CHANGE_POINT.
```text
"when did each log category spike" / "change points in category counts"
→
FROM logs-*
| STATS c = COUNT(*) BY category = CATEGORIZE(message), bucket = BUCKET(@timestamp, 1 minute)
| SORT category, bucket
| CHANGE_POINT c ON bucket
```
---
## Common Mistakes to Avoid
- **Forgetting LIMIT** - Always add `LIMIT` to prevent returning too many rows
- **Wrong time field** - Common names: `@timestamp`, `timestamp`, `time`, `date`
- **Case sensitivity** - Field names are case-sensitive: `host.Name` ≠ `host.name`
- **String vs Keyword** - Use `.keyword` suffix for exact matches on text fields: `WHERE status.keyword == "active"`
- **Type mismatches** - Convert types when needed: `EVAL num = TO_INTEGER(string_field)`
- **STATS without aggregation** - STATS requires aggregate functions (`STATS count = COUNT(*) BY host`, not
`STATS BY host`)
- **Missing FROM or TS** - Every query must start with a source command
- **Pipe placement** - Each command needs a pipe before it (except FROM)
- **NULL exclusion in negation** - `!=` silently excludes rows where the field is `NULL` (missing). This is the most
common source of silent false negatives.
- **CATEGORIZE grouping order** - `CATEGORIZE(field)` must be the first grouping in `STATS ... BY`. You cannot do
`BY host.name, category = CATEGORIZE(message)`.
- **CHANGE_POINT needs ordered input** - You may need to sort the sequence on the key.
- **LOOKUP JOIN must precede STATS** - Fields from a joined index are discarded after aggregation. Always join first,
then aggregate:
```esql
// Wrong: JOIN after STATS loses joined fields
FROM events
| STATS total = COUNT(*) BY category_id
| LOOKUP JOIN categories ON category_id
// Correct: JOIN first, then aggregate
FROM events
| LOOKUP JOIN categories ON category_id
| STATS total = COUNT(*) BY category_name
```
- **DATE_EXTRACT parameter order** - The date part string comes first, the date expression second:
```esql
// Wrong: DATE_EXTRACT(@timestamp, "HOUR_OF_DAY")
// Correct:
| EVAL hour = DATE_EXTRACT("HOUR_OF_DAY", @timestamp)
```
- **Datetime subtraction** - ES|QL does not support direct datetime arithmetic. Use `DATE_DIFF` to compute intervals:
```esql
// Wrong: end_time - start_time
// Correct:
| EVAL duration_hours = DATE_DIFF("hour", start_time, end_time)
```
- **STD_DEV, not STDDEV** - The standard deviation function is `STD_DEV` (with underscore):
```esql
// Wrong: STDDEV(field)
// Correct:
| STATS sd = STD_DEV(latency_ms) BY endpoint
```
references/time-series-queries.md
# ES|QL Time Series Queries
Query metrics data in Elasticsearch using the `TS` source command and time series aggregation functions. Requires
Elasticsearch 9.2+.
> **Status:** `TS`, **all** time series aggregation functions (the 9.2-introduced set — `RATE`, `IRATE`, `INCREASE`,
> `DELTA`, `IDELTA`, `AVG_OVER_TIME`, `SUM_OVER_TIME`, `MIN_OVER_TIME`, `MAX_OVER_TIME`, `FIRST_OVER_TIME`,
> `LAST_OVER_TIME`, `COUNT_OVER_TIME`, `COUNT_DISTINCT_OVER_TIME`, `PRESENT_OVER_TIME`, `ABSENT_OVER_TIME` — and the
> 9.3-introduced set — `DERIV`, `PERCENTILE_OVER_TIME`, `STDDEV_OVER_TIME`, `VARIANCE_OVER_TIME`), the `TBUCKET`
> grouping function, the new `WITHOUT(...)` grouping function, and the new `METRICS_INFO` and `TS_INFO` discovery
> commands are **GA since 9.4** (preview from 9.2 to 9.3 for the 9.2/9.3 features; new in 9.4 for `WITHOUT`,
> `METRICS_INFO`, and `TS_INFO`). `TRANGE` remains in preview. **Looking for PromQL?** Elasticsearch 9.4+ also exposes a
> `PROMQL` source command for running Prometheus Query Language directly against TSDS indices. See
> [promql-command.md](promql-command.md). Prefer `PROMQL` only when the user explicitly thinks in PromQL or is migrating
> Prometheus dashboards/alerts; otherwise prefer `TS` and the inner/outer aggregation paradigm described below.
## Table of Contents
- [TS Source Command](#ts-source-command)
- [Inner/Outer Aggregation Paradigm](#innerouter-aggregation-paradigm)
- [Histogram Metrics](#histogram-metrics)
- [Time Series Aggregation Functions](#time-series-aggregation-functions)
- [TBUCKET Grouping Function](#tbucket-grouping-function)
- [WITHOUT Grouping Function](#without-grouping-function)
- [Metric and Time Series Discovery](#metric-and-time-series-discovery)
- [TRANGE Time Filter](#trange-time-filter)
- [CLAMP Functions](#clamp-functions)
- [Kibana Time Filtering](#kibana-time-filtering)
- [Common Query Patterns](#common-query-patterns)
- [Guidelines](#guidelines)
- [References](#references)
---
## TS Source Command
`TS` replaces `FROM` when querying
[time series data streams (TSDS)](https://www.elastic.co/docs/manage-data/data-store/data-streams/time-series-data-stream-tsds).
It enables time series aggregation functions (`RATE`, `AVG_OVER_TIME`, etc.) inside `STATS`.
**Availability:** Preview from 9.2 to 9.3, **GA since 9.4**. GA on Elastic Cloud Serverless.
**Syntax:**
```esql
TS index_pattern [METADATA fields]
```
**Key differences from `FROM`:**
- Targets only TSDS indices (`index.mode: time_series`)
- Enables inner/outer aggregation paradigm in `STATS`
- Cannot combine with `FORK` before `STATS` is applied
- Optimized for processing time series data; `FROM` may produce unexpected results on TSDS indices
- When there is **no** `STATS` command in the query, `TS` returns rows sorted by `@timestamp` descending by default —
useful for listing recent values across many time series.
**Best practices:**
- Always use `TS` (not `FROM`) for aggregations on time series indices
- Add a time range filter with `TRANGE` to limit scan volume
- Avoid aggregating multiple metrics with different dimensional cardinalities in the same query
---
## Inner/Outer Aggregation Paradigm
The first `STATS` after a `TS` command uses a two-level aggregation model:
1. **Inner function** (time series aggregation) -- evaluated per individual time series (e.g. `RATE`, `AVG_OVER_TIME`)
2. **Outer function** (standard aggregation) -- aggregates inner results across groups (e.g. `SUM`, `AVG`, `MAX`)
```esql
TS metrics
| STATS SUM(RATE(search_requests)) BY TBUCKET(1 hour), host
// ^^^ ^^^^ inner: RATE per time series
// outer: SUM across time series sharing the same host + bucket
```
A single host can map to multiple underlying time series. The outer `SUM` combines the per-time-series rates into one
value per host per bucket. Use `SUM` as the outer function for counters (rates are additive). Use `AVG` or `MAX` for
gauges depending on intent.
If the inner function is omitted, the default inner aggregation depends on the field type:
- **Numeric and gauge fields** — `LAST_OVER_TIME()` is assumed implicitly.
- **`exponential_histogram` and `tdigest` fields** — distributions are **merged** across the time window (not
`LAST_OVER_TIME`). Use standard aggregations like `SUM`, `AVG`, and `PERCENTILE` directly.
- **Plain `histogram` fields** — can't be directly processed; cast with `::exponential_histogram` (or `::tdigest`) first
— see [Histogram Metrics](#histogram-metrics).
```esql
// Gauge — equivalent queries
TS metrics | STATS AVG(memory_usage)
TS metrics | STATS AVG(LAST_OVER_TIME(memory_usage))
// exponential_histogram / tdigest — merge + standard aggregation (no cast)
TS metrics-tsds | STATS SUM(jvm.gc.duration) BY TBUCKET(1 hour)
// plain histogram — cast required
TS metrics-tsds | STATS SUM(jvm.gc.duration::exponential_histogram) BY TBUCKET(1 hour)
```
**When to use `*_OVER_TIME` vs plain aggregations for gauges:** For simple gauge queries (average CPU, max memory),
prefer `AVG(cpu)` over `AVG(AVG_OVER_TIME(cpu))` — the implicit `LAST_OVER_TIME` is sufficient and produces cleaner
queries. Use explicit `*_OVER_TIME` only when you need a specific window behavior: `AVG_OVER_TIME` to average all
samples (not just the last), `MIN_OVER_TIME`/`MAX_OVER_TIME` to find extremes within each time series before
aggregating, or `DELTA`/`DERIV` to compute changes. When in doubt, omit the inner function. For histogram fields, do not
use `*_OVER_TIME` — see [Histogram Metrics](#histogram-metrics).
Since 9.3 (preview), use a time series function directly without an outer aggregation to get one value per time series
per bucket. The result is implicitly grouped by all dimensions of each time series and includes a `_timeseries` column
with the dimension key/value pairs — see [WITHOUT Grouping Function](#without-grouping-function) for narrowing this
grouping (`BY WITHOUT(dim, ...)`, GA since 9.4).
```esql
TS metrics
| WHERE TRANGE(1 day)
| STATS RATE(search_requests) BY TBUCKET(1 hour)
```
Nesting two time series functions is **not allowed** and causes an error:
```esql
// INVALID -- nested time series functions
TS metrics | STATS AVG_OVER_TIME(RATE(memory_usage))
```
---
## Histogram Metrics
Distribution metrics use field types `histogram`, `exponential_histogram`, or `tdigest` (not plain numeric
`gauge`/`counter`). OpenTelemetry is a common source — OTLP often stores `exponential_histogram`; other ingestion paths
may store plain `histogram` or `tdigest`. For a candidate metric, use `METRICS_INFO` (9.4+) for `metric_name`,
`data_stream`, `field_type`, and `metric_type` — not mappings or field caps. The same metric name can appear on multiple
streams with different `field_type` values. See [METRICS_INFO](#metrics_info).
For `exponential_histogram` and `tdigest`, the default inner aggregation is **histogram merge** (it is an internal ES|QL
aggregation which cannot be referenced manually in queries) — use `SUM` / `AVG` / `COUNT` / `MIN` / `MAX` / `MEDIAN` /
`PERCENTILE` directly. Plain `histogram` is **not** merged automatically; cast first (see below). Do **not** use
`*_OVER_TIME` (plain `histogram` rejects it; `exponential_histogram` may accept it — still the wrong shape). Asking for
“average over time” or “avg duration” means `AVG(field)`, not `AVG_OVER_TIME`.
**When to cast:** `TS` merge accepts `exponential_histogram` or `tdigest`, not plain `histogram`. Decide from
`METRICS_INFO` (or a type error):
| `METRICS_INFO.field_type` for the streams you query | Cast? |
| ------------------------------------------------------------------------ | --------------------------------------- |
| only `exponential_histogram` or only `tdigest` | Omit the cast |
| any plain `histogram` (alone **or** mixed with other distribution types) | Required: `::exponential_histogram` |
| type / incompatible-types / ambiguities error mentioning `histogram` | Add `::exponential_histogram` and retry |
Always cast when plain `histogram` is in play — including mixed streams. If the targeted streams are unambiguously
`exponential_histogram` or `tdigest`, no cast is needed. Default required cast is `::exponential_histogram` (converts
plain `histogram`; no-op for `exponential_histogram`). Use `::tdigest` instead when you know the metric actually stores
T-Digest data (for example, when values in a plain `histogram` field are mid-points rather than bucket boundaries — that
causes less precision loss).
```esql
// WRONG — *_OVER_TIME is the wrong shape (even if it succeeds)
TS metrics-* | STATS SUM(SUM_OVER_TIME(jvm.gc.duration)) BY TBUCKET(1 hour)
// CORRECT — pure exponential_histogram or tdigest on the stream from METRICS_INFO: no cast
TS metrics-tsds
| WHERE TRANGE(1 hour)
| STATS count = COUNT(jvm.gc.duration),
avg = AVG(jvm.gc.duration),
p99 = PERCENTILE(jvm.gc.duration, 99)
BY jvm.gc.action, TBUCKET(5 minutes)
// CORRECT — plain histogram present or mixed streams: cast
TS metrics-*
| STATS SUM(jvm.gc.duration::exponential_histogram) BY TBUCKET(1 hour), service.name
```
`SUM` totals the values the histograms were originally created from; `COUNT` counts the number of values the histograms
were created from — it does _not_ count the number of histograms; use `PERCENTILE`/`MEDIAN`/`AVG` for latency. Prefer
the histogram metric from `METRICS_INFO` over a companion `*.summary` / `aggregate_metric_double` in `schema` unless the
user asks for it. Do not fall back to `*_OVER_TIME` or `TO_STRING`/`REPLACE` JSON parsing. For OTel ingestion
background, see [OTel histogram metrics in ES\|QL](https://www.elastic.co/search-labs/blog/otel-histogram-metrics-esql).
**Availability:** Histogram metric querying in `TS` is preview in 9.3 and **GA since 9.4** (same as
`exponential_histogram` / `tdigest` field types).
---
## Time Series Aggregation Functions
All functions below are available under `TS ... | STATS`. Each accepts a required field argument and an optional sliding
window (`time_duration`, 9.3+). The window must be a multiple of the `TBUCKET` interval. If omitted, the bucket interval
is used as the window.
### Counter Functions
For fields with `time_series_metric: counter` (`counter_double`, `counter_integer`, `counter_long`).
| Function | Description | Since | Status |
| ---------- | ---------------------------------------------------------------------- | ------------- | -------- |
| `RATE` | Per-second average rate of increase; handles counter resets | 9.2 (preview) | GA (9.4) |
| `IRATE` | Per-second rate between the last two data points; responsive to spikes | 9.2 (preview) | GA (9.4) |
| `INCREASE` | Absolute increase of the counter in the time window; handles resets | 9.2 (preview) | GA (9.4) |
```esql
// Average rate per host per hour
// host is a dimension of the TSDS index metrics
TS metrics
| WHERE TRANGE(1 hour)
| STATS SUM(RATE(search_requests)) BY TBUCKET(1 hour), host
// Instant rate (last two points only) per cluster and 10 minutes
// k8s is an example of a TSDS index, to showcase that time series indexes do not have to be called metrics
// cluster is a dimension of the TSDS index k8s
TS k8s
| STATS SUM(IRATE(network.total_bytes_in)) BY cluster, TBUCKET(10 minute)
// Total counter increase
TS k8s
| STATS SUM(INCREASE(network.total_bytes_in)) BY cluster, TBUCKET(10 minute)
```
### Gauge / Numeric Functions
For gauge metrics and general numeric fields (`double`, `integer`, `long`, `aggregate_metric_double`).
| Function | Description | Since | Status |
| -------------------------- | ----------------------------------------------------------------- | ------------- | -------- |
| `AVG_OVER_TIME` | Average value over the time window | 9.2 (preview) | GA (9.4) |
| `SUM_OVER_TIME` | Sum of values over the time window | 9.2 (preview) | GA (9.4) |
| `MIN_OVER_TIME` | Minimum value over the time window | 9.2 (preview) | GA (9.4) |
| `MAX_OVER_TIME` | Maximum value over the time window | 9.2 (preview) | GA (9.4) |
| `FIRST_OVER_TIME` | Earliest value by `@timestamp` | 9.2 (preview) | GA (9.4) |
| `LAST_OVER_TIME` | Latest value by `@timestamp` (implicit default for numeric/gauge) | 9.2 (preview) | GA (9.4) |
| `COUNT_OVER_TIME` | Count of values over the time window | 9.2 (preview) | GA (9.4) |
| `COUNT_DISTINCT_OVER_TIME` | Count of distinct values over the time window | 9.2 (preview) | GA (9.4) |
| `PERCENTILE_OVER_TIME` | Percentile of values; takes `(field, percentile)` | 9.3 (preview) | GA (9.4) |
| `STDDEV_OVER_TIME` | Population standard deviation over the time window | 9.3 (preview) | GA (9.4) |
| `VARIANCE_OVER_TIME` | Population variance over the time window | 9.3 (preview) | GA (9.4) |
| `DELTA` | Absolute change of a gauge in the time window | 9.2 (preview) | GA (9.4) |
| `IDELTA` | Change between the last two data points only | 9.2 (preview) | GA (9.4) |
| `DERIV` | Derivative over time using linear regression | 9.3 (preview) | GA (9.4) |
```esql
// Average memory per cluster per 5 minutes (plain form — implicit LAST_OVER_TIME is sufficient for gauges)
// cluster is a dimension of the TSDS index metrics
TS metrics
| WHERE TRANGE(1 day)
| STATS AVG(memory_usage) BY cluster, TBUCKET(5 minute)
// P95 network cost per cluster per minute
// k8s is an example of a TSDS index, to showcase that time series indexes do not have to be called metrics
// cluster is a dimension of the TSDS index k8s
TS k8s
| STATS MAX(PERCENTILE_OVER_TIME(network.cost, 95)) BY cluster, TBUCKET(1 minute)
// Gauge delta (absolute change in bytes)
TS k8s
| STATS SUM(DELTA(network.bytes_in)) BY cluster, TBUCKET(10 minute)
// Derivative of cost per pod over time
// pod is a dimension of the TSDS index k8s
TS k8s
| STATS MAX(DERIV(network.cost)) BY pod, TBUCKET(5 minute)
```
### Presence / Absence Functions
Detect whether a field has data in a given time window. Return `boolean`.
| Function | Description | Since | Status |
| ------------------- | ----------------------------------------------- | ------------- | -------- |
| `PRESENT_OVER_TIME` | `true` if field has values in the window | 9.2 (preview) | GA (9.4) |
| `ABSENT_OVER_TIME` | `true` if field has **no** values in the window | 9.2 (preview) | GA (9.4) |
```esql
// Detect pods with missing data
TS k8s
| STATS missing = MAX(ABSENT_OVER_TIME(events_received)) BY pod, TBUCKET(2 minute)
```
### Sliding Window
Pass a `time_duration` as the second argument to any time series function to use a sliding window for the
per-time-series aggregation. The window is orthogonal to time bucketing of output results (`TBUCKET`). If the window is
omitted, the `TBUCKET` interval is used implicitly.
```esql
// Average rate per host over a 10-minute sliding window, bucketed by 1 minute
// host is a dimension of the TSDS index metrics
TS metrics
| WHERE TRANGE(1 hour)
| STATS AVG(RATE(requests, 10m)) BY TBUCKET(1m), host
```
**Version behavior:**
- **9.2-9.3 (preview):** the window must be a **multiple of the `TBUCKET` interval** in the `BY` clause (for example,
with `TBUCKET(1m)` you may use `1m`, `2m`, `10m`; `7m` is rejected). If no window is specified, the `TBUCKET` interval
is used implicitly.
- **9.4+ (GA):** all window values are accepted, with performance optimizations when the window is a multiple of the
`TBUCKET` interval. **Restriction:** within a single query you cannot mix windows that are **smaller** than the bucket
interval for one metric with windows that are **larger** than the bucket interval for another metric.
---
## TBUCKET Grouping Function
Creates time buckets from `@timestamp`. Use in the `BY` clause of `STATS` for time-based grouping.
**Syntax:**
```esql
STATS ... BY bucket = TBUCKET(interval)
STATS ... BY TBUCKET(interval)
```
The interval is a time duration (`1 hour`, `5 minute`, `30s`) or date period (`1 month`). A string representation
(`"1 hour"`) also works.
`TBUCKET` is the preferred bucketing function for `TS` queries. It has a simpler signature than
`DATE_TRUNC(interval, @timestamp)` and is aware of time series semantics.
**Availability:** Preview from 9.2 to 9.3, **GA since 9.4**.
Always assign a column alias to `TBUCKET` so it can be referenced in `SORT`:
```esql
// 1-hour buckets — alias enables SORT
TS metrics
| STATS rate = SUM(RATE(requests)) BY bucket = TBUCKET(1 hour), host
| SORT bucket, host
// 5-minute buckets (plain form for gauge)
TS metrics
| STATS avg_cpu = AVG(cpu_percent) BY bucket = TBUCKET(5 minute), service
| SORT bucket
```
---
## WITHOUT Grouping Function
When the first `STATS` after `TS` uses a **bare** time series aggregation function (one not wrapped in an outer
aggregation such as `AVG()` or `SUM()`), rows are implicitly grouped by **all** dimensions of each time series. The
output includes a `_timeseries` `keyword` column containing a JSON-encoded object with the dimension key/value pairs
identifying each group. Only the dimensions that actually exist for a given time series appear in `_timeseries` — not
every dimension declared in the index mappings — so different rows in the result may carry different dimension keys.
`WITHOUT(...)` lets you make this grouping explicit, or narrow it to a subset of dimensions:
- `BY WITHOUT(dim1, dim2, ...)` groups by **all** dimensions **except** those listed.
- `BY WITHOUT()` (no arguments) explicitly groups by every dimension; it is equivalent to the implicit "group by all"
behavior.
When combining a bare time series function with other groupings, **only grouping functions** (`TBUCKET`, `WITHOUT`) are
allowed in the `BY` clause — bare dimension columns are rejected. For example:
```esql
// INVALID -- bare time series function with a bare dimension column in BY
TS k8s | STATS rate(network.total_bytes_in) BY host
```
Use `BY TBUCKET(...)` and/or `BY WITHOUT(...)`, or wrap the time series function with an outer aggregation.
**Availability:** **GA since 9.4**. Can only be used in the **first** `STATS` command under a `TS` source — using it in
a `FROM | STATS ... BY WITHOUT(...)` query is rejected.
**Examples:**
```esql
// Group by every dimension implicitly — _timeseries column carries the dimension labels
TS k8s
| STATS avg = AVG_OVER_TIME(network.cost)
| SORT avg DESC
// Group by every dimension EXCEPT pod
TS k8s
| STATS total_cost = SUM(network.cost) BY WITHOUT(pod)
| SORT total_cost
// Combine WITHOUT with TBUCKET to add a time bucket to the surviving dimensions
TS k8s
| STATS total_cost = SUM(network.cost) BY WITHOUT(pod), tbucket = TBUCKET(1 hour)
| SORT total_cost
// Equivalent to implicit grouping (group by all dimensions)
TS k8s
| STATS avg = AVG_OVER_TIME(network.cost) BY WITHOUT()
```
---
## Metric and Time Series Discovery
Two processing commands introduced in 9.4 expose the metric catalogue of a TSDS so you can discover what to query
without inspecting index mappings or calling the field capabilities API. Both must follow a `TS` source command and must
appear before pipeline-breaking commands (`STATS`, `SORT`, `LIMIT`).
| Command | Granularity | Status | Adds beyond `METRICS_INFO` |
| -------------- | ------------------------------------- | ------------ | ---------------------------------------------------------------- |
| `METRICS_INFO` | One row per **metric** | GA since 9.4 | — |
| `TS_INFO` | One row per **(metric, time series)** | GA since 9.4 | `dimensions` JSON column with the labels identifying each series |
### METRICS_INFO
Returns one row per distinct metric in the targeted TSDS, with applicable dimensions and metadata. Useful for "what
metrics exist in this stream?" and "what dimensions apply to metric X?".
**Output columns** (all `keyword`):
- `metric_name` — single-valued
- `data_stream` — multi-valued when several streams align on unit/metric_type/field_type
- `unit` — declared unit; may be `null` or multi-valued
- `metric_type` — `counter`, `gauge`, or `histogram`
- `field_type` — Elasticsearch field type (`long`, `double`, `histogram`, `exponential_histogram`, `tdigest`, …)
- `dimension_fields` — union of dimension field names across the series for the metric
Always scope discovery with `TRANGE` (or another time filter) before `METRICS_INFO` / `TS_INFO` so the catalogue is
built from a bounded scan, not the full index history. Examples below omit `TRANGE` for brevity — add
`| WHERE TRANGE(15m)` (or another range) after `TS` in real queries.
```esql
// List every metric, alphabetically
TS k8s
| METRICS_INFO
| SORT metric_name
// Restrict to metrics that have data matching a filter
TS k8s
| WHERE cluster == "prod"
| METRICS_INFO
| SORT metric_name
// Filter by metric type, count by it
TS k8s
| METRICS_INFO
| STATS metric_count = COUNT(*) BY metric_type
| SORT metric_type
// Check field_type for a candidate metric (histogram vs numeric)
TS k8s
| METRICS_INFO
| WHERE metric_name == "jvm.gc.duration"
| KEEP metric_name, data_stream, field_type, metric_type
// Find metrics whose name matches a pattern
TS k8s
| METRICS_INFO
| WHERE metric_name LIKE "network.eth0*"
| SORT metric_name
```
### TS_INFO
Returns one row per (metric, time series) combination, including the dimension key/value pairs that identify each
series. Useful for "which time series report this metric?" and "what label combinations exist?".
`TS_INFO` includes **all `METRICS_INFO` columns** plus a `dimensions` column — a JSON-encoded object such as
`{"job":"elasticsearch","instance":"instance_1"}` (single-valued).
```esql
// Every (metric, time series) pair in the data stream
TS k8s
| TS_INFO
| SORT metric_name, dimensions
// Filter the underlying series before discovery
TS k8s
| WHERE cluster == "prod"
| TS_INFO
| KEEP metric_name, dimensions
| SORT metric_name, dimensions
// Filter by metadata after TS_INFO (gauges only)
TS k8s
| TS_INFO
| WHERE metric_type == "gauge"
| SORT metric_name, dimensions
// Count distinct time series per metric
TS k8s
| TS_INFO
| STATS series_count = COUNT(*) BY metric_name
| SORT metric_name
// Count distinct metrics per time series — spot under- or over-reporting series
TS k8s
| TS_INFO
| STATS metric_count = COUNT_DISTINCT(metric_name) BY dimensions
| SORT dimensions
```
### Guidelines
- **Use these for TSDS schema discovery** before writing `RATE`/`AVG_OVER_TIME` queries — they replace the older
workflow of inspecting `_settings`, `_mapping`, or field capabilities for time series indices.
- **Reach for `METRICS_INFO` first** to enumerate metrics; reach for `TS_INFO` only when you need the exact dimension
combinations (label sets) of individual series.
- **Always time-filter before discovery** with `WHERE TRANGE(...)` (and optional dimension filters) so `METRICS_INFO` /
`TS_INFO` do not scan the full index history.
- **The output replaces the original table.** Anything you `STATS` / `SORT` / `LIMIT` afterwards operates on metadata
rows, not raw documents — there's no way to re-attach the data points after `METRICS_INFO` or `TS_INFO`.
- **Both commands are TSDS-only.** `FROM | METRICS_INFO` and `FROM | TS_INFO` are rejected.
---
## TRANGE Time Filter
Filter data by time range using `@timestamp`. Prefer `TRANGE` over manual `WHERE @timestamp > NOW() - ...` filters.
**Syntax:**
```esql
// Offset from now (last N time units)
WHERE TRANGE(offset)
// Explicit start and end
WHERE TRANGE(start, end)
```
**Examples:**
```esql
// Last hour
TS metrics
| WHERE TRANGE(1 hour)
| STATS SUM(RATE(requests)) BY TBUCKET(1 minute), host
// Explicit time range
TS metrics
| WHERE TRANGE("2024-05-10T00:00:00Z", "2024-05-10T01:00:00Z")
| STATS SUM(RATE(requests)) BY TBUCKET(5 minute), host
// Epoch milliseconds
// k8s is an example of a TSDS index, to showcase that time series indexes do not have to be called metrics
FROM k8s
| WHERE TRANGE(1715300236000, 1715300282000)
```
**Supported parameter types:** `time_duration`, `date_period`, `date`, `date_nanos`, `keyword` (date string), `long`
(epoch millis).
**Availability:** Preview since 9.3.
---
## CLAMP Functions
Bound metric values to a range. Useful for capping outliers or enforcing value limits in time series analysis.
| Function | Description | Syntax |
| ----------- | --------------------------------------------------- | ------------------------ |
| `CLAMP` | Clamp values to `[min, max]` range | `CLAMP(field, min, max)` |
| `CLAMP_MIN` | Set a lower bound; values below `min` become `min` | `CLAMP_MIN(field, min)` |
| `CLAMP_MAX` | Set an upper bound; values above `max` become `max` | `CLAMP_MAX(field, max)` |
**Availability:** Preview since 9.3.
```esql
// Clamp network cost between 1 and 10
TS k8s
| EVAL clamped_cost = CLAMP(network.cost, 1, 10)
| STATS SUM(clamped_cost) BY TBUCKET(1 minute)
// Aggregate with clamped values
TS k8s
| STATS total = SUM(CLAMP_MAX(network.cost, 1)) BY TBUCKET(1 minute)
```
---
## Kibana Time Filtering
When writing ES|QL queries for Kibana (Discover, dashboards, alerting), **do not add manual time range filters**. Kibana
automatically applies `@timestamp` filtering based on the date picker.
Write Kibana queries without time filters:
```esql
// Kibana query -- no time filter needed
TS metrics
| STATS SUM(RATE(search_requests)) BY TBUCKET(1 hour), host
```
Use explicit time filters (`TRANGE` or `WHERE @timestamp`) only for:
- Direct API queries (`POST /_query`) run outside Kibana
---
## Common Query Patterns
### Rate of a Counter per Host per Hour
```esql
TS metrics
| WHERE TRANGE(1 hour)
| STATS SUM(RATE(search_requests)) BY TBUCKET(1 hour), host
```
### Total Rate per Host (No Time Bucketing)
```esql
TS metrics
| WHERE TRANGE(1 hour)
| STATS SUM(RATE(search_requests)) BY host
```
### Average Gauge per Cluster Over Time
```esql
// Plain form — preferred for gauge metrics
TS metrics
| WHERE TRANGE(1 day)
| STATS AVG(memory_usage) BY TBUCKET(5 minute), cluster
```
### Per-Time-Series Averages vs Global Average
```esql
// Average of last values per time series (default — preferred for most gauge queries)
TS metrics | STATS AVG(memory_usage)
// Average of ALL samples per time series (use AVG_OVER_TIME only when you need this distinction)
TS metrics | STATS AVG(AVG_OVER_TIME(memory_usage))
```
### Detect Missing Data
```esql
// k8s is an example of a TSDS index, to showcase that time series indexes do not have to be called metrics
// pod is a dimension of the TSDS index k8s
TS k8s
| WHERE TRANGE(1 hour)
| STATS missing = MAX(ABSENT_OVER_TIME(events_received)) BY pod, TBUCKET(2 minute)
```
### Counter Increase Totals
```esql
TS k8s
| WHERE TRANGE(1 hour)
| STATS SUM(INCREASE(network.total_bytes_in)) BY cluster, TBUCKET(10 minute)
```
### Instant Rate (Last Two Points)
```esql
TS k8s
| WHERE TRANGE(1 hour)
| STATS SUM(IRATE(network.total_bytes_in)) BY cluster, TBUCKET(10 minute)
```
### Sliding Window Rate
```esql
TS metrics
| WHERE TRANGE(1 hour)
| STATS AVG(RATE(requests, 10m)) BY TBUCKET(1m), host
```
In 9.2-9.3 the window must be a multiple of the `TBUCKET` interval. In 9.4+ any window value is accepted (mixing windows
smaller and larger than the bucket interval for different metrics in the same query is not supported).
---
## Guidelines
- **Use `TS` for all aggregations on TSDS indices.** `FROM` is still available for listing raw documents, but use `TS`
for metrics aggregations.
- **Use `SUM` as the outer function for counters.** Rates and increases are additive across time series that share a
dimension (e.g. host). Use `AVG` or `MAX` for gauges.
- **Always add a time range filter** with `TRANGE` (or `WHERE @timestamp`) to limit scan volume, except in Kibana where
the date picker handles this automatically. Don't add a range filter if the user explicitly asks not to add it.
- **Version requirements:**
- `TS`, `TBUCKET`, `WITHOUT`, `METRICS_INFO`, `TS_INFO`, and **all** time series aggregation functions (the
9.2-introduced set — `RATE`, `IRATE`, `INCREASE`, `DELTA`, `IDELTA`, all `*_OVER_TIME` from 9.2,
`PRESENT_OVER_TIME`, `ABSENT_OVER_TIME` — and the 9.3-introduced set — `DERIV`, `PERCENTILE_OVER_TIME`,
`STDDEV_OVER_TIME`, `VARIANCE_OVER_TIME`): **GA since 9.4** (preview from 9.2-9.3 for the 9.2/9.3 functions; new in
9.4 for `WITHOUT`, `METRICS_INFO`, `TS_INFO`).
- `TRANGE`: 9.3+ (preview).
- Sliding window parameter (second argument to time series functions): introduced in 9.2-9.3 (preview, restricted to
multiples of the `TBUCKET` interval); **GA in 9.4** with arbitrary durations.
- `CLAMP`, `CLAMP_MIN`, `CLAMP_MAX`: 9.3+ (preview).
- **Do not nest time series functions.** `AVG_OVER_TIME(RATE(field))` is invalid. Use a standard aggregation as the
outer function.
- **Avoid mixing metrics with different dimensions** in one query. If `foo` and `bar` have different dimension values,
`SUM(RATE(foo)) + SUM(RATE(bar))` may produce nulls for mismatched dimensions.
- **For histogram / exponential_histogram metrics**, use standard aggregations (no `*_OVER_TIME`); cast with
`::exponential_histogram` when plain `histogram` is present, types are mixed, or a type error occurs — see
[Histogram Metrics](#histogram-metrics).
## References
- [ES|QL TS Command](https://www.elastic.co/docs/reference/query-languages/esql/commands/ts)
- [ES|QL PROMQL Command](https://www.elastic.co/docs/reference/query-languages/esql/commands/promql) — alternative
source command using PromQL syntax (9.4+ preview)
- [promql-command.md](promql-command.md) — PROMQL command reference in this skill
- [Time Series Aggregation Functions](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/time-series-aggregation-functions)
- [TBUCKET Function](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/grouping-functions/tbucket)
- [TRANGE Function](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/date-time-functions/trange)
- [Time Series Data Streams (TSDS)](https://www.elastic.co/docs/manage-data/data-store/data-streams/time-series-data-stream-tsds)
- [OTel histogram metrics in ES\|QL](https://www.elastic.co/search-labs/blog/otel-histogram-metrics-esql) — common
ingestion path for exponential histograms, casts, and query patterns
SKILL.md
---
name: elasticsearch-esql
description: >
Execute ES|QL (Elasticsearch Query Language) queries, use when the user wants to
query Elasticsearch data, analyze logs, aggregate metrics, explore data, or create
charts and dashboards from ES|QL results.
metadata:
author: elastic
version: 0.7.0
universal: true
compatibility: Elasticsearch 8.14 or later (ES|QL GA; introduced 8.11 as tech preview),
self-managed, Elastic Cloud Hosted, or Elastic Cloud Serverless; individual ES|QL
features are version-gated (see references/esql-version-history.md). Requires the
`elastic` CLI ≥ 0.2 with `stack es` support.
---
# Elasticsearch ES|QL
Execute ES|QL queries against Elasticsearch: discover the schema, choose the right ES|QL feature for the task, generate
the simplest correct query, and run it.
<!-- begin-partial: preamble -->
## Environment Configuration
This skill executes Elasticsearch operations through the `elastic` CLI. If the
[`elastic` CLI](https://github.com/elastic/cli#configuration) is not installed, tell the user what it is needed for. Do
not guess credentials, call the HTTP API directly, or attempt other workarounds.
This skill references operations in HTTP-shorthand form (e.g., `GET /`, `GET /_cat/indices`, `GET /{index}/_mapping`,
`GET /{index}/_settings/index.mode`, `POST /_query`). The [Operations](#operations) table at the end of this document
maps each shorthand to the equivalent `elastic` CLI command — always use the CLI rather than calling the HTTP API
directly.
<!-- end-partial: preamble -->
## What is ES|QL?
ES|QL (Elasticsearch Query Language) is a piped query language for Elasticsearch. It is **NOT** the same as:
- Elasticsearch Query DSL (JSON-based)
- SQL
- EQL (Event Query Language)
ES|QL uses pipes (`|`) to chain commands:
`FROM index | WHERE condition | STATS aggregation BY field | SORT field | LIMIT n`
> **Prerequisite:** ES|QL requires `_source` to be enabled on queried indices. Indices with `_source` disabled (e.g.,
> `"_source": { "enabled": false }`) will cause ES|QL queries to fail.
>
> **Version Compatibility:** ES|QL was introduced in 8.11 (tech preview) and became GA in 8.14. Features like
> `LOOKUP JOIN` (8.18+), `MATCH` (8.17+), and `INLINE STATS` (9.2+) were added in later versions. On pre-8.18 clusters,
> use `ENRICH` as a fallback for `LOOKUP JOIN` (see generation tips). `INLINE STATS` and counter-field `RATE()` have
> **no fallback** before 9.2. Check [references/esql-version-history.md](references/esql-version-history.md) for feature
> availability by version.
>
> **Cluster Detection:** Call `GET /` to determine the cluster type and version:
>
> - `build_flavor: "serverless"` — Elastic Cloud Serverless. `version.number` tracks the stack line under active
> development (next minor from main), so clients that only semver-compare may treat Serverless as “latest.” **Do not**
> use `version.number` to gate features: if `build_flavor` is `"serverless"`, assume all GA and preview ES|QL features
> are available.
> - `build_flavor: "default"` — Stack (self-managed or Cloud-hosted). Use `version.number` for feature availability.
> - **Snapshot builds** have `version.number` like `9.4.0-SNAPSHOT`. Strip the `-SNAPSHOT` suffix and use the
> major.minor for version checks. Snapshot builds include all features from that version plus potentially unreleased
> features from development — if a query fails with an unknown function/command, it may simply not have landed yet.
> Elastic employees commonly use snapshot builds for testing.
## Process
1. **Verify the connection and detect the deployment type.** Call `GET /` first. This confirms connectivity and detects
whether the deployment is a Serverless project (all features available) or a versioned cluster (features depend on
version). The `build_flavor` field is the authoritative signal — if it equals `"serverless"`, ignore the reported
version number and use all ES|QL features freely. If the call fails, stop and point the user at the CLI configuration
instructions rather than guessing endpoints or credentials.
2. **Discover the schema (required — never guess index or field names).** List candidate indices with
`GET /_cat/indices` (pass a pattern to narrow), then fetch field types for the chosen index with
`GET /{index}/_mapping`.
Always run schema discovery before generating queries. Index names and field names vary across deployments and cannot
be reliably guessed. Even common-sounding data (e.g., "logs") may live in indices named `logs-test`, `logs-app-*`, or
`application_logs`. Field names may use ECS dotted notation (`source.ip`, `service.name`) or flat custom names — the
only way to know is to check.
**Prefer simplicity:** Query a single index unless the user explicitly asks for data across multiple sources. Do not
combine indices with different schemas using `COALESCE` unless specifically requested — pick the single most relevant
index for the question. When multiple indices contain similar data, prefer the one with the most complete schema for
the task at hand.
**Detect time series indices.** Check the index mode with `GET /{index}/_settings/index.mode`. If it is
`time_series`, use `TS <data-stream>` (not `FROM`), `TBUCKET(interval)` (not `DATE_TRUNC`), and wrap counter fields
with `SUM(RATE(...))`. Read the full TS section in [Generation Tips](references/generation-tips.md) before writing
any time series query. For TSDS indices on 9.4+, prefer the in-language discovery commands `METRICS_INFO` and
`TS_INFO` (both GA) over inspecting mappings — they enumerate the metric catalogue and the dimension labels of each
time series directly, and are run as ES|QL queries via `POST /_query`. Treat `METRICS_INFO` as authoritative for
`metric_type` (`counter`/`gauge`/`histogram`) and `field_type` (`histogram`, `tdigest`, `exponential_histogram` for
distribution metrics). Both must follow `TS` and must precede `STATS`/`SORT`/`LIMIT`. See
[Time Series Queries](references/time-series-queries.md#metric-and-time-series-discovery):
```esql
TS metrics-tsds | METRICS_INFO | SORT metric_name
TS metrics-tsds | TS_INFO | KEEP metric_name, dimensions | SORT metric_name
```
3. **Choose the right ES|QL feature for the task.** Before writing queries, match the user's intent to the most
appropriate ES|QL feature. Prefer a single advanced query over multiple basic ones.
- "find patterns," "categorize," "group similar messages" → `CATEGORIZE(field)`
- "spike," "dip," "anomaly," "when did X change" → `CHANGE_POINT value ON key`
- "trend over time," "time series" → `STATS ... BY BUCKET(@timestamp, interval)` or `TS` for TSDB
- "PromQL", "Prometheus query/dashboard/alert", `sum by (instance) (...)`, label matchers like `{cluster="prod"}` →
`PROMQL` source command (9.4+ preview); see [PROMQL Command](references/promql-command.md). Prefer `TS` for native
ES|QL phrasing.
- "search," "find documents matching" → `MATCH` (default), `QSTR` (advanced boolean), `KQL` (Kibana migration). For
content/document relevance search, follow the [ES|QL Search Strategy](references/esql-search-strategy.md)
- "count," "average," "breakdown" → `STATS` with aggregation functions
- "approximate," "estimate," "rough numbers," "fast/cheap stats on huge data" → `SET approximation=true;` before a
`STATS` query (GA in 9.5+/Serverless, preview in 9.4); see [Query Approximation](references/query-approximation.md)
4. **Read the references** before generating queries:
- [Generation Tips](references/generation-tips.md) - key patterns (TS/TBUCKET/RATE, per-agg WHERE, LOOKUP JOIN,
CIDR_MATCH), common templates, and ambiguity handling
- [Time Series Queries](references/time-series-queries.md) - **read before any TS query**: inner/outer aggregation
model, TBUCKET syntax, RATE constraints, histogram metrics
- [PROMQL Command](references/promql-command.md) — **read before any PROMQL query**: options, output schema,
limitations, and `PROMQL` vs `TS` decision matrix (9.4+ preview)
- [ES|QL Complete Reference](references/esql-reference.md) - full syntax for all commands and functions
- [ES|QL Search Strategy](references/esql-search-strategy.md) — for content/document relevance search (retrieve →
fuse → rerank)
- [ES|QL Search Reference](references/esql-search.md) — for full-text search function syntax (MATCH, QSTR, KQL,
scoring)
- [Query Approximation](references/query-approximation.md) — **read before using `SET approximation`**: output
columns, sampling/confidence-level tuning, unsupported functions and patterns (GA in 9.5+/Serverless, preview in
9.4)
5. **Generate the query** following ES|QL syntax. Prefer the **simplest query** that answers the question — do not add
extra indices, fields, or transformations unless the user asks for them. Only include fields in `KEEP` that directly
answer the question. Do not add extra filter conditions beyond what the user specified (e.g., don't add
`OR level == "ERROR"` when the user just said "errors").
- Start with `FROM index-pattern` (or `TS index-pattern` for time series indices)
- Add `WHERE` for filtering (use `TRANGE` for time ranges on 9.3+)
- Use `EVAL` for computed fields
- Use `STATS ... BY` for aggregations
- For time series metrics: `TS` with `SUM(RATE(...))` for counters, `AVG(...)` for gauges, standard aggregations
(`SUM`, `AVG`, `PERCENTILE`, … — not `*_OVER_TIME`) for histogram metrics, and `TBUCKET(interval)` for time
bucketing — see the TS section in [Generation Tips](references/generation-tips.md) and
[Histogram Metrics](references/time-series-queries.md#histogram-metrics)
- For detecting spikes, dips, or anomalies, use `CHANGE_POINT` after time-bucketed aggregation
- Add `SORT` and `LIMIT` as needed
6. **Execute the query** with `POST /_query`. Request tabular (TSV) output for clean, decoration-free results that are
easy to read and post-process.
## ES|QL Quick Reference
> **Version availability:** This section omits version annotations for readability. Check
> [ES|QL Version History](references/esql-version-history.md) for feature availability by Elasticsearch version.
### Basic Structure
```esql
FROM index-pattern
| WHERE condition
| EVAL new_field = expression
| STATS aggregation BY grouping
| SORT field DESC
| LIMIT n
```
### Common Patterns
**Filter and limit:**
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 24 hours AND level == "error"
| SORT @timestamp DESC
| LIMIT 100
```
**Aggregate by time:** For time series (TSDS) indices, prefer `TS` with `TRANGE` and `TBUCKET` over `FROM` +
`DATE_TRUNC` (see the time series section below).
```esql
TS metrics-*
| WHERE TRANGE(7 days)
| STATS avg_cpu = AVG(cpu.percent) BY bucket = TBUCKET(1 hour)
| SORT bucket DESC
```
**Top N with count:**
```esql
FROM web-logs
| STATS count = COUNT(*) BY response.status_code
| SORT count DESC
| LIMIT 10
```
**Text search (8.17+):** Use `MATCH` as the default for full-text search instead of `LIKE`/`RLIKE` — it is significantly
faster and supports relevance scoring. `MATCH` on a `text` field is usually sufficient on its own — do not add redundant
keyword equality filters (e.g., `category == "X"`) alongside `MATCH` unless the user explicitly requests filtering. Use
`QSTR` only when you need advanced boolean logic, wildcards, or multi-field searches in a single expression. The first
argument to `MATCH` must be **one** real field name — not a string listing several fields (e.g. `"title,content"`) and
not multiple field arguments; combine fields with `MATCH(a, "q") OR MATCH(b, "q")`. `KQL` is available from 8.18/9.0+.
For content/document search use cases, follow the [ES|QL Search Strategy](references/esql-search-strategy.md). See
[ES|QL Search Reference](references/esql-search.md) for the full function guide.
```esql
FROM documents METADATA _score
| WHERE MATCH(content, "search terms")
| SORT _score DESC
| LIMIT 20
```
**String extraction:** Use `DISSECT` for structured delimiter-based patterns (preferred — produces named fields) and
`GROK` for regex-based extraction. For simple cases, `SUBSTRING(s, start, len)` for fixed-position extraction,
`SPLIT(s, delim)` to split into a multivalue, `LOCATE(substr, s)` to find a character position. `SPLIT` returns a
multivalue — use `MV_FIRST`, `MV_LAST`, or `MV_SLICE` to pick elements. `INSTR` and `STRPOS` do **not** exist — use
`LOCATE`. `REGEXP_EXTRACT` does not exist — use `GROK`.
```esql
// Extract domain from email using DISSECT (preferred — produces named fields)
FROM customers
| DISSECT email "%{local}@%{domain}"
| STATS count = COUNT(*) BY domain
// Alternative: extract domain from email using SPLIT
FROM customers
| EVAL domain = MV_LAST(SPLIT(email, "@"))
| STATS count = COUNT(*) BY domain
// Parse HTTP log lines
FROM logs-*
| DISSECT message "%{method} %{path} %{status_text}"
| KEEP @timestamp, method, path, status_text
```
**Log categorization (Platinum license):** Use `CATEGORIZE` to auto-cluster log messages into pattern groups. Prefer
this over running multiple `STATS ... BY field` queries when exploring or finding patterns in unstructured text.
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 24 hours
| STATS count = COUNT(*) BY category = CATEGORIZE(message)
| SORT count DESC
| LIMIT 20
```
**Change point detection (Platinum license):** Use `CHANGE_POINT` to detect spikes, dips, and trend shifts in a metric
series. Prefer this over manual inspection of time-bucketed counts.
```esql
FROM logs-*
| STATS c = COUNT(*) BY t = BUCKET(@timestamp, 30 seconds)
| SORT t
| CHANGE_POINT c ON t
| WHERE type IS NOT NULL
```
**Time series metrics:** With `TS`, use `TRANGE` for time filtering (9.3+) or omit it entirely — do **not** add a
redundant `WHERE @timestamp > NOW() - ...` alongside `TBUCKET`. The `TBUCKET` duration defines the aggregation window.
```esql
// Counter metric: SUM(RATE(...)) with TBUCKET(duration)
TS metrics-tsds
| WHERE TRANGE(1 hour)
| STATS SUM(RATE(requests)) BY TBUCKET(1 hour), host
// Gauge metric: AVG(...) — no RATE needed
TS metrics-tsds
| STATS avg_cpu = AVG(cpu) BY service.name, bucket = TBUCKET(5 minutes)
| SORT bucket
// Histogram metric: standard aggregation (merge); cast for wildcard/mixed streams
TS metrics-*
| STATS total_gc = SUM(jvm.gc.duration::exponential_histogram) BY TBUCKET(1 hour), service.name
```
**Time series with PromQL syntax (9.4+ preview):** Use the `PROMQL` source command when the user explicitly asks for
PromQL, references Prometheus syntax (`sum by (instance) (...)`, label matchers like `{cluster="prod"}`), or is
migrating a Prometheus dashboard or alert. The `PROMQL` command accepts standard PromQL with optional `index`, `step`,
`buckets`, `start`, `end`, and `scrape_interval` options, and produces a table that the rest of the ES|QL pipeline can
process. Range selectors are optional — when omitted, the window is `max(step, scrape_interval)`. Otherwise prefer `TS`
(GA in 9.4). `PROMQL` does **not** support group modifiers, set operators (`or`/`and`/`unless`), or functions like
`histogram_quantile`, `predict_linear`, and `label_join` — fall back to `TS` for those. See
[PROMQL Command](references/promql-command.md) for the full reference.
```esql
// Adaptive Kibana query — date picker drives time range and step
PROMQL index=metrics-* sum by (instance) (rate(http_requests_total))
// Named result, post-processed with ES|QL
PROMQL index=k8s step=1h bytes=(max by (cluster) (network.bytes_in))
| STATS max_bytes = MAX(bytes) BY cluster
| SORT cluster
```
**Data enrichment with LOOKUP JOIN:** The basic `ON` clause matches fields by name in both indices
(`LOOKUP JOIN idx ON field_name`). When the join key has a different name in the source, use `RENAME` first to align
names. 9.2+ tech preview also supports expression predicates (`ON expr == expr`); see
[ES|QL Complete Reference](references/esql-reference.md) for details. After `LOOKUP JOIN`, lookup columns are available
by their **original field names** — do **not** table-qualify them (e.g., write `threat_level`, not
`threat_intel.threat_level`). **Ordering tip:** when the question asks for top-N results, `SORT` and `LIMIT` _before_
`LOOKUP JOIN` to reduce enrichment cost. For general listings or full enrichment, place `LOOKUP JOIN` right after
`FROM`/`WHERE`.
```esql
// Field name mismatch — RENAME before joining
FROM support_tickets
| RENAME product AS product_name
| LOOKUP JOIN knowledge_base ON product_name
// Aggregate, limit, THEN enrich (top-N only)
FROM orders
| STATS total_spent = SUM(total) BY customer_id
| SORT total_spent DESC
| LIMIT 3
| LOOKUP JOIN customers_lookup ON customer_id
| KEEP name, customer_id, total_spent
// Multi-field join (9.2+)
FROM application_logs
| LOOKUP JOIN service_registry ON service_name, environment
| KEEP service_name, environment, owner_team
```
**Multivalue field filtering:** Use `MV_CONTAINS` to check if a multivalue field contains a specific value. Use
`MV_COUNT` to count values.
```esql
// Filter by multivalue membership
FROM employees
| WHERE MV_CONTAINS(languages, "Python")
// Find entries matching multiple values
FROM employees
| WHERE MV_CONTAINS(languages, "Java") AND MV_CONTAINS(languages, "Python")
// Count multivalue entries
FROM employees
| EVAL num_languages = MV_COUNT(languages)
| SORT num_languages DESC
```
**Change point detection (alternate example):** Use when the user asks about spikes, dips, or anomalies. Requires
time-bucketed aggregation, `SORT`, then `CHANGE_POINT`.
```esql
FROM logs-*
| STATS error_count = COUNT(*) BY bucket = DATE_TRUNC(1 hour, @timestamp)
| SORT bucket
| CHANGE_POINT error_count ON bucket AS type, pvalue
```
**Approximate STATS (GA in 9.5+/Serverless, preview in 9.4):** Prepend `SET approximation=true;` to a `STATS` query to
get fast estimates via sampling and extrapolation on large datasets when exact values are not required. The result adds
`_approximation_confidence_interval(col)` and `_approximation_certified(col)` columns per estimated quantity — report
those bounds, do not present estimates as exact. `COUNT_DISTINCT`, `MIN`, `MAX`, `FIRST`, `LAST`, `TOP` (and a few
others) are **not** supported and fall back to exact execution; use the `SAMPLE` command for those. Pipelines with 2+
`STATS`, or using the `TS`/`PROMQL` source command, also fall back. See
[Query Approximation](references/query-approximation.md).
```esql
SET approximation=true;
FROM web_traffic
| WHERE @timestamp >= NOW() - 1 week
| STATS total_hits = COUNT(*), avg_load_time = AVG(page_load_ms) BY country_code
| SORT total_hits DESC
| LIMIT 5
```
## Full Reference
For complete ES|QL syntax including all commands, functions, and operators, read:
- [ES|QL Complete Reference](references/esql-reference.md)
- [ES|QL Search Reference](references/esql-search.md) - Full-text search: MATCH, QSTR, KQL, MATCH_PHRASE, scoring,
semantic search
- [ES|QL Search Strategy](references/esql-search-strategy.md) - Relevance search strategy for content indices: retrieve
→ fuse → rerank
- [ES|QL Version History](references/esql-version-history.md) - Feature availability by Elasticsearch version
- [Query Patterns](references/query-patterns.md) - Natural language to ES|QL translation
- [Generation Tips](references/generation-tips.md) - Best practices for query generation
- [Time Series Queries](references/time-series-queries.md) - TS command, time series aggregation functions, TBUCKET
- [PROMQL Command](references/promql-command.md) - PromQL source command for TSDS indices (9.4+ preview)
- [Query Approximation](references/query-approximation.md) - Approximate STATS via sampling/extrapolation (GA in
9.5+/Serverless, preview in 9.4)
- [DSL to ES|QL Migration](references/dsl-to-esql-migration.md) - Convert Query DSL to ES|QL
## Error Handling
When query execution fails, read the error message from Elasticsearch and correct the query. Common issues:
- Field doesn't exist → Always inspect the mapping (`GET /{index}/_mapping`) and list indices (`GET /_cat/indices`)
before writing a query. Never guess field or index names — they vary across deployments.
- Type mismatch → Use type conversion functions (TO_STRING, TO_INTEGER, etc.)
- Syntax error → Review ES|QL reference for correct syntax. Always use **double quotes** for strings, never single
quotes.
- No results → Check time range and filter conditions
- Wrong function name → ES|QL uses underscored names: `STD_DEV()` not `STDDEV()`, `MEDIAN_ABSOLUTE_DEVIATION()` not
`MAD()`. Use `CONCAT()` for strings, not `+`. Use `CASE(cond, val, ...)` not `CASE WHEN...THEN...END`.
- Wrong date part → `DATE_EXTRACT` uses ES|QL part names: `"hour_of_day"` not `"hour"`, `"day_of_month"` not `"day"`,
`"month_of_year"` not `"month"`. Use `DATE_DIFF("day", start, end)` for date arithmetic, not subtraction.
## Examples
Each example follows the process: inspect the mapping first, then write the simplest correct query.
**"Top 10 source IPs by request count in the last hour"** — filter by time window, then aggregate and rank:
```esql
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| STATS requests = COUNT(*) BY source.ip
| SORT requests DESC
| LIMIT 10
```
**"Average response time per service, only for 5xx responses"** — filter to errors before aggregating:
```esql
FROM traces-*
| WHERE http.response.status_code >= 500
| STATS avg_ms = AVG(duration_ms) BY service.name
| SORT avg_ms DESC
```
**"Error count per day for the last week"** — bucket by day with `DATE_TRUNC`:
```esql
FROM logs-*
| WHERE log.level == "error" AND @timestamp > NOW() - 7 days
| STATS errors = COUNT(*) BY day = DATE_TRUNC(1 day, @timestamp)
| SORT day ASC
```
## Guidelines
- **Inspect before querying.** Read the mapping (`GET /{index}/_mapping`) and list indices (`GET /_cat/indices`) before
writing a query — never guess field or index names.
- **Filter early.** Put `WHERE` before `STATS` so aggregation runs over the smallest row set.
- **Always bound results.** End exploratory queries with `LIMIT`.
- **Quote correctly.** Use double quotes for string literals, never single quotes.
- **Respect version gating.** Confirm feature availability with `GET /` (`build_flavor`, `version.number`) and
references/esql-version-history.md before using newer commands such as `LOOKUP JOIN` or `INLINE STATS`.
- **Correct on error, do not guess.** Read the Elasticsearch error, fix the specific issue, and re-run.
## Operations
| HTTP API (shorthand) | `elastic` CLI command |
| ----------------------------------- | --------------------------------------------------------------------- |
| `GET /` | `elastic es info` |
| `GET /_cat/indices` | `elastic es cat indices --index '<pattern>'` |
| `GET /{index}/_mapping` | `elastic es indices get-mapping --index '<index>'` |
| `GET /{index}/_settings/index.mode` | `elastic es indices get-settings --index '<index>' --name index.mode` |
| `POST /_query` | `elastic es esql query --format tsv --query "<esql>"` |