Elasticsearch Esql

Elasticsearch ES|QL

Execute ES|QL queries against Elasticsearch.

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 for feature

availability by version.

>

Cluster Detection: Use the GET / response 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" — Self-managed or Elastic 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.

Environment Configuration

See Environment Setup for full connection configuration options (Elastic Cloud, direct URL, basic auth, local development).

Run node scripts/esql.js test to verify the connection. If the test fails, refer the user to the environment setup guide, then stop. Do not try to explore further until a successful connection test.

Usage

Get Index Information (for schema discovery)

node scripts/esql.js indices                    # List all indices
node scripts/esql.js indices "logs-*"           # List matching indices
node scripts/esql.js schema "logs-2024.01.01"   # Get field mappings for an index

Execute Raw ES|QL

node scripts/esql.js raw "FROM logs-* | STATS count = COUNT(*) BY host.name | SORT count DESC | LIMIT 5"

Execute with TSV Output

node scripts/esql.js raw "FROM logs-* | STATS count = COUNT(*) BY component | SORT count DESC" --tsv

TSV Output Options:

• --tsv or -t: Output as tab-separated values (clean, no decorations)
• --no-header: Omit the header row

Test Connection

node scripts/esql.js test

Guidelines

1. Detect deployment type: Always run node scripts/esql.js test first. This detects whether the deployment is a Serverless project (all features available) or a versioned cluster (features depend on version). The build_flavor field from GET / is the authoritative signal — if it equals "serverless", ignore the reported version number and use all ES|QL features freely.

2. Discover schema (required — never guess index or field names):

   node scripts/esql.js indices "pattern*"
   node scripts/esql.js schema "index-name"

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.

The schema command reports the index mode. If it shows Index mode: time_series, the output includes the data stream name and copy-pasteable TS syntax — 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 before writing any time series query. You can also check the index mode directly via the Elasticsearch index settings API:

   curl -s "$ELASTICSEARCH_URL/<index-name>/_settings/index.mode" -H "Authorization: ApiKey $ELASTICSEARCH_API_KEY"

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
• "search," "find documents matching" → MATCH (default), QSTR (advanced boolean), KQL (Kibana migration). For

content/document relevance search, follow the ES|QL Search Strategy

• "count," "average," "breakdown" → STATS with aggregation functions

4. Read the references before generating queries:

• Generation Tips - key patterns (TS/TBUCKET/RATE, per-agg WHERE, LOOKUP JOIN,

CIDR_MATCH), common templates, and ambiguity handling

• Time Series Queries - read before any TS query: inner/outer aggregation

model, TBUCKET syntax, RATE constraints

• ES|QL Complete Reference - full syntax for all commands and functions
• ES|QL Search Strategy — for content/document relevance search (retrieve →

fuse → rerank)

• ES|QL Search Reference — for full-text search function syntax (MATCH, QSTR, KQL,

scoring)

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, and TBUCKET(interval)

for time bucketing — see the TS section in Generation Tips for the three critical syntax rules

• For detecting spikes, dips, or anomalies, use CHANGE_POINT after time-bucketed aggregation
• Add SORT and LIMIT as needed

6. Execute with TSV flag:

   node scripts/esql.js raw "FROM index | STATS count = COUNT(*) BY field" --tsv

ES|QL Quick Reference

Version availability: This section omits version annotations for readability. Check

ES|QL Version History for feature availability by Elasticsearch version.

Basic Structure

FROM index-pattern
| WHERE condition
| EVAL new_field = expression
| STATS aggregation BY grouping
| SORT field DESC
| LIMIT n

Common Patterns

Filter and limit:

FROM logs-*
| WHERE @timestamp > NOW() - 24 hours AND level == "error"
| SORT @timestamp DESC
| LIMIT 100

Aggregate by time:

FROM metrics-*
| WHERE @timestamp > NOW() - 7 days
| STATS avg_cpu = AVG(cpu.percent) BY bucket = DATE_TRUNC(1 hour, @timestamp)
| SORT bucket DESC

Top N with count:

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. See ES|QL Search Reference for the full function guide.

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.

// 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.

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.

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.

// 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

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 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.

// 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.

// 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.

FROM logs-*
| STATS error_count = COUNT(*) BY bucket = DATE_TRUNC(1 hour, @timestamp)
| SORT bucket
| CHANGE_POINT error_count ON bucket AS type, pvalue

Full Reference

For complete ES|QL syntax including all commands, functions, and operators, read:

• ES|QL Complete Reference
• ES|QL Search Reference - Full-text search: MATCH, QSTR, KQL, MATCH_PHRASE, scoring,

semantic search

• ES|QL Search Strategy - Relevance search strategy for content indices: retrieve

→ fuse → rerank

• ES|QL Version History - Feature availability by Elasticsearch version
• Query Patterns - Natural language to ES|QL translation
• Generation Tips - Best practices for query generation
• Time Series Queries - TS command, time series aggregation functions, TBUCKET
• DSL to ES|QL Migration - Convert Query DSL to ES|QL
• Environment Setup - Connection configuration options

Error Handling

When query execution fails, the script returns:

• The generated ES|QL query
• The error message from Elasticsearch
• Suggestions for common issues

Common issues:

• Field doesn't exist → Always use get_schema and list_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

# Schema discovery
node scripts/esql.js test
node scripts/esql.js indices "logs-*"
node scripts/esql.js schema "logs-2024.01.01"

# Execute queries
node scripts/esql.js raw "FROM logs-* | STATS count = COUNT(*) BY host.name | LIMIT 10"
node scripts/esql.js raw "FROM metrics-* | STATS avg = AVG(cpu.percent) BY hour = DATE_TRUNC(1 hour, @timestamp)" --tsv

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
• Basic Query Structure
• Match All Query
• Term Query (Exact Match)
• Match Query (Full-Text Search)
• Match Phrase Query
• Multi-Match Query
• Query String Query
• Range Query
• Bool Query
• Exists Query
• Wildcard / Prefix Query
• Regexp Query
• Aggregations
• Sorting
• Field Selection (\_source)
• Pagination
• Script Fields
• LOOKUP JOIN (Replaces Enrichment Patterns)
• Filters Aggregation (Per-Aggregation WHERE)
• Pipeline Aggregations (Chained STATS)
• Highlighting
• ES|QL Limitations (vs Query DSL)
• Migration Checklist
• Performance Considerations
• 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

POST /my-index/_search
{
  "query": { ... },
  "aggs": { ... },
  "sort": [ ... ],
  "size": 100,
  "_source": ["field1", "field2"]
}

ES|QL

FROM my-index
| WHERE <conditions>
| STATS <aggregations> BY <groupings>
| SORT <field> DESC
| KEEP field1, field2
| LIMIT 100

---

Match All Query

Query DSL

{
  "query": {
    "match_all": {}
  },
  "size": 100
}

ES|QL

FROM my-index
| LIMIT 100

---

Term Query (Exact Match)

Query DSL

{
  "query": {
    "term": {
      "status": "published"
    }
  }
}

ES|QL

FROM my-index
| WHERE status == "published"

Multiple Terms (terms query)

Query DSL

{
  "query": {
    "terms": {
      "status": ["published", "draft"]
    }
  }
}

ES|QL

FROM my-index
| WHERE status IN ("published", "draft")

---

Match Query (Full-Text Search)

Query DSL

{
  "query": {
    "match": {
      "title": "elasticsearch guide"
    }
  }
}

ES|QL (8.17+)

FROM my-index
| WHERE MATCH(title, "elasticsearch guide")

Or using the match operator:

FROM my-index
| WHERE title : "elasticsearch guide"

With Relevance Scoring

FROM my-index METADATA _score
| WHERE MATCH(title, "elasticsearch guide")
| SORT _score DESC
| LIMIT 10

---

Match Phrase Query

Query DSL

{
  "query": {
    "match_phrase": {
      "title": "quick brown fox"
    }
  }
}

ES|QL (8.19+)

FROM my-index
| WHERE MATCH_PHRASE(title, "quick brown fox")

---

Multi-Match Query

Query DSL

{
  "query": {
    "multi_match": {
      "query": "elasticsearch",
      "fields": ["title", "content", "tags"]
    }
  }
}

ES|QL

FROM my-index
| WHERE MATCH(title, "elasticsearch")
    OR MATCH(content, "elasticsearch")
    OR MATCH(tags, "elasticsearch")

Or use QSTR for more flexibility:

FROM my-index
| WHERE QSTR("title:elasticsearch OR content:elasticsearch OR tags:elasticsearch")

---

Query String Query

Query DSL

{
  "query": {
    "query_string": {
      "query": "status:active AND (type:blog OR type:article)"
    }
  }
}

ES|QL

FROM my-index
| WHERE QSTR("status:active AND (type:blog OR type:article)")

---

Range Query

Query DSL

{
  "query": {
    "range": {
      "price": {
        "gte": 10,
        "lte": 100
      }
    }
  }
}

ES|QL

FROM my-index
| WHERE price >= 10 AND price <= 100

Date Range

Query DSL

{
  "query": {
    "range": {
      "@timestamp": {
        "gte": "now-24h",
        "lte": "now"
      }
    }
  }
}

ES|QL

FROM my-index
| WHERE @timestamp >= NOW() - 24 hours AND @timestamp <= NOW()

Or simply:

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

{
  "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.

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

{
  "query": {
    "exists": {
      "field": "user"
    }
  }
}

ES|QL

FROM my-index
| WHERE user IS NOT NULL

Does Not Exist

Query DSL

{
  "query": {
    "bool": {
      "must_not": {
        "exists": { "field": "user" }
      }
    }
  }
}

ES|QL

FROM my-index
| WHERE user IS NULL

---

Wildcard / Prefix Query

Query DSL

{
  "query": {
    "wildcard": {
      "name": "john*"
    }
  }
}

ES|QL

FROM my-index
| WHERE name LIKE "john*"

Or using STARTS_WITH:

FROM my-index
| WHERE STARTS_WITH(name, "john")

---

Regexp Query

Query DSL

{
  "query": {
    "regexp": {
      "name": "joh?n.*"
    }
  }
}

ES|QL

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

{
  "size": 0,
  "aggs": {
    "status_counts": {
      "terms": {
        "field": "status",
        "size": 10
      }
    }
  }
}

ES|QL

FROM my-index
| STATS count = COUNT(*) BY status
| SORT count DESC
| LIMIT 10

Date Histogram Aggregation

Query DSL

{
  "size": 0,
  "aggs": {
    "events_over_time": {
      "date_histogram": {
        "field": "@timestamp",
        "calendar_interval": "day"
      }
    }
  }
}

ES|QL

FROM my-index
| STATS count = COUNT(*) BY day = DATE_TRUNC(1 day, @timestamp)
| SORT day

Date Histogram with Sub-Aggregation

Query DSL

{
  "size": 0,
  "aggs": {
    "events_over_time": {
      "date_histogram": {
        "field": "@timestamp",
        "calendar_interval": "hour"
      },
      "aggs": {
        "avg_response": {
          "avg": { "field": "response_time" }
        }
      }
    }
  }
}

ES|QL

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

{
  "size": 0,
  "aggs": {
    "price_stats": {
      "stats": { "field": "price" }
    }
  }
}

ES|QL

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

{
  "size": 0,
  "aggs": {
    "response_percentiles": {
      "percentiles": {
        "field": "response_time",
        "percents": [50, 95, 99]
      }
    }
  }
}

ES|QL

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

{
  "size": 0,
  "aggs": {
    "unique_users": {
      "cardinality": { "field": "user_id" }
    }
  }
}

ES|QL

FROM my-index
| STATS unique_users = COUNT_DISTINCT(user_id)

Filter Aggregation

Query DSL

{
  "size": 0,
  "aggs": {
    "errors": {
      "filter": { "term": { "level": "error" } },
      "aggs": {
        "count": { "value_count": { "field": "_id" } }
      }
    }
  }
}

ES|QL

FROM my-index
| WHERE level == "error"
| STATS count = COUNT(*)

Or to get both total and filtered in one query using CASE:

FROM my-index
| STATS
    total = COUNT(*),
    errors = COUNT(CASE(level == "error", 1, null))

With per-aggregation WHERE (8.16+), this is simpler:

FROM my-index
| STATS
    total = COUNT(*),
    errors = COUNT(*) WHERE level == "error"

Nested Aggregations (Multiple Group By)

Query DSL

{
  "size": 0,
  "aggs": {
    "by_country": {
      "terms": { "field": "country" },
      "aggs": {
        "by_city": {
          "terms": { "field": "city" }
        }
      }
    }
  }
}

ES|QL

FROM my-index
| STATS count = COUNT(*) BY country, city
| SORT country, count DESC

---

Sorting

Query DSL

{
  "sort": [{ "@timestamp": { "order": "desc" } }, { "name": { "order": "asc" } }]
}

ES|QL

FROM my-index
| SORT @timestamp DESC, name ASC

---

Field Selection (\_source)

Query DSL

{
  "_source": ["title", "author", "date"]
}

ES|QL

FROM my-index
| KEEP title, author, date

Exclude Fields

Query DSL

{
  "_source": {
    "excludes": ["internal_*", "temp"]
  }
}

ES|QL

FROM my-index
| DROP internal_*, temp

---

Pagination

Query DSL

{
  "from": 20,
  "size": 10
}

ES|QL

ES|QL doesn't have direct from equivalent. Use filtering or time-based pagination:

FROM my-index
| SORT @timestamp DESC
| LIMIT 10

For subsequent pages, use the last seen value:

FROM my-index
| WHERE @timestamp < "2024-01-15T10:30:00Z"
| SORT @timestamp DESC
| LIMIT 10

---

Script Fields

Query DSL

{
  "script_fields": {
    "price_with_tax": {
      "script": {
        "source": "doc['price'].value * 1.1"
      }
    }
  }
}

ES|QL

FROM my-index
| EVAL price_with_tax = price * 1.1

---

LOOKUP JOIN (Replaces Enrichment Patterns)

Query DSL

{
  "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:

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

{
  "size": 0,
  "aggs": {
    "messages": {
      "filters": {
        "filters": {
          "errors": { "match": { "level": "error" } },
          "warnings": { "match": { "level": "warning" } }
        }
      }
    }
  }
}

ES|QL

With per-aggregation WHERE (8.16+):

FROM logs
| STATS
    errors = COUNT(*) WHERE level == "error",
    warnings = COUNT(*) WHERE level == "warning",
    total = COUNT(*)

---

Pipeline Aggregations (Chained STATS)

Query DSL

{
  "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:

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:

FROM sales
| INLINE STATS avg_amount = AVG(amount) BY category
| EVAL diff_from_avg = amount - avg_amount

---

Highlighting

Query DSL

{
  "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 in date functions | ✅ | ❌ (UTC only) | | JOIN (non-lookup) | N/A | ❌ (only LEFT JOIN on lookup index) |

Unsupported Field Types in ES|QL

• nested
• binary
• completion
• flattened
• 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.

Environment Configuration

Elasticsearch connection is configured via environment variables. Run node scripts/esql.js test to verify the connection. If the test fails, suggest these setup options to the user, then stop. Do not try to explore further until a successful connection test.

Elastic Cloud Serverless: After connecting, inspect GET /. If build_flavor is "serverless", do not use

version.number to decide which ES|QL features are allowed — Serverless tracks current GA and preview ES|QL, and the

reported version follows the main-line / next-minor line (semver-only clients may see it as “latest”). Prefer

build_flavor for detection and gating. For the full rules (including self-managed and snapshot builds), read

Cluster Detection in SKILL.md and the Serverless callout in

ES|QL Version History.

Option 1: Elastic Cloud (recommended for production)

export ELASTICSEARCH_CLOUD_ID="deployment-name:base64encodedcloudid"
export ELASTICSEARCH_API_KEY="base64encodedapikey"

Option 2: Direct URL with API Key

export ELASTICSEARCH_URL="https://elasticsearch:9200"
export ELASTICSEARCH_API_KEY="base64encodedapikey"

Option 3: Basic Authentication

export ELASTICSEARCH_URL="https://elasticsearch:9200"
export ELASTICSEARCH_USERNAME="elastic"
export ELASTICSEARCH_PASSWORD="changeme"

Option 4: Local Development with start-local

For local development and testing, use start-local to quickly spin up Elasticsearch and Kibana using Docker or Podman:

curl -fsSL https://elastic.co/start-local | sh

After installation completes, Elasticsearch runs at http://localhost:9200 and Kibana at http://localhost:5601. The script generates a random password for the elastic user and an API key, both stored in the .env file inside the created elastic-start-local folder.

To configure the environment variables for this skill, source the .env file and export the connection settings:

source elastic-start-local/.env
export ELASTICSEARCH_URL="$ES_LOCAL_URL"
export ELASTICSEARCH_API_KEY="$ES_LOCAL_API_KEY"

Then run node scripts/esql.js test to verify the connection.

Optional: Skip TLS verification (development only)

export ELASTICSEARCH_INSECURE="true"

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 Self-Managed: Version annotations in this document (e.g., "9.2+") apply to self-managed

Elasticsearch. 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 self-managed, use version.number (strip any -SNAPSHOT suffix)

for feature checks.

Table of Contents

• Query Structure
• Query Directives
• Source Commands
• Processing Commands
• Aggregate Functions
• Time Series Aggregation Functions
• String Functions
• Math Functions
• Date/Time Functions
• Type Conversion Functions
• IP Functions
• Spatial Functions
• Dense Vector Functions
• Multivalue Functions
• Conditional Functions
• Full-Text Search Functions
• Operators
• Syntax Details
• Metadata Fields
• Best Practices
• Example Queries

Query Structure

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.

Syntax:

SET setting = value; [SET settingN = valueN;]
source-command
| processing-commands

`unmapped_fields` (9.3+ preview) -- controls how unmapped fields are treated:

• FAIL (default) -- the query fails if it references unmapped fields
• NULLIFY -- treats unmapped fields as null values

`time_zone` (Serverless GA; self-managed planned) -- sets the default timezone for the query, overriding UTC default.

Examples:

SET unmapped_fields = "nullify";
FROM employees
| KEEP emp_no, foo
| SORT emp_no
| LIMIT 1

SET time_zone = "+05:00";
TS k8s
| WHERE @timestamp == "2024-05-10T00:04:49.000Z"
| STATS BY @timestamp, bucket = TBUCKET(3 hours)

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.

---

Source Commands

Source commands produce tables, typically from Elasticsearch data.

FROM

Retrieves data from indices, data streams, or aliases.

Syntax:

FROM index_pattern [METADATA fields]

Examples:

// 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-*

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:

ROW column1 = value1 [, column2 = value2, ...]

Examples:

ROW a = 1, b = "two", c = null
ROW x = [1, 2, 3]
ROW greeting = "hello", pi = 3.14159

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. Available since 9.2.

Syntax:

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, LAST_OVER_TIME() is assumed implicitly
• Cannot be combined with FORK before STATS is applied

Examples:

// 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

// Average of last memory usage 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))

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

SHOW

Returns information about the deployment.

Syntax:

SHOW INFO

---

Processing Commands

Processing commands transform the input table.

WHERE

Filters rows based on a boolean condition.

Syntax:

WHERE condition

Examples:

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:

// 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:

EVAL column1 = expression1 [, column2 = expression2, ...]

Examples:

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:

STATS aggregation1 [WHERE filter1] [, aggregation2 [WHERE filter2], ...] [BY grouping1, grouping2, ...]

Examples:

// 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:

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:

// 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:

KEEP column1 [, column2, ...]

Examples:

FROM employees
| KEEP first_name, last_name, salary

// With wildcards
FROM logs-*
| KEEP @timestamp, message, error.*

DROP

Removes specified columns.

Syntax:

DROP column1 [, column2, ...]

Examples:

FROM employees
| DROP internal_id, temp_field

// With wildcards
FROM data
| DROP temp_*, debug_*

RENAME

Renames columns.

Syntax:

RENAME old_name AS new_name [, old_name2 AS new_name2, ...]

Examples:

FROM employees
| RENAME emp_id AS employee_id

FROM data
| RENAME col1 AS column_one, col2 AS column_two

SORT

Sorts the table.

Syntax:

SORT column1 [ASC/DESC] [NULLS FIRST/LAST] [, column2 ...]

Examples:

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.

Syntax:

LIMIT number

Examples:

FROM logs-*
| SORT @timestamp DESC
| LIMIT 100

DISSECT

Extracts structured fields from a string using a pattern.

Syntax:

DISSECT field "%{pattern}"

Examples:

FROM logs
| DISSECT message "%{clientip} - - [%{timestamp}] \"%{method} %{path}\""

FROM apache_logs
| DISSECT message "%{ip} %{} %{} [%{timestamp}] \"%{request}\" %{status} %{bytes}"

Cookbook — Common DISSECT Patterns:

// 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 (e.g., %{*key} / %{&key} for dynamic key-value extraction).

GROK

Extracts fields using grok patterns (regex-based).

Syntax:

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:

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 or multiple pattern matching. 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:

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:

// 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+):

// 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):

// 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.

// 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 for setup steps).

Syntax:

ENRICH policy_name ON match_field [WITH new_field1, new_field2, ...]

Examples:

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:

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:

// 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:

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:

// 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:

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:

Examples:

// 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. Tech preview in 9.2 (GA on Serverless). Since 9.3, defaults to 1000 rows; configurable via esql.command.rerank.limit and esql.command.rerank.enabled cluster settings.

Syntax:

RERANK [column =] query ON field [, field, ...] [WITH { "inference_id": "endpoint" }]

Example:

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:

[column =] COMPLETION prompt WITH inference_endpoint

Example:

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:

SAMPLE probability

• probability -- value between 0 and 1 (exclusive), the chance each row is included

Example:

// Sample ~10% of rows for exploratory analysis
FROM logs-*
| SAMPLE 0.1
| STATS avg_duration = AVG(duration) BY service.name

MV_EXPAND

Expands multivalued fields into separate rows.

Syntax:

MV_EXPAND field

Examples:

FROM data
| MV_EXPAND tags
| STATS count = COUNT(*) BY tags

URI_PARTS (Planned)

Parses a URI string and extracts its components (domain, path, port, query, scheme, etc.) into new columns. Not yet released.

Syntax:

URI_PARTS prefix = expression

Example:

FROM web_logs
| URI_PARTS url_parts = request_url
| KEEP url_parts.domain, url_parts.path, url_parts.query

---

Aggregate Functions

Used with STATS command.

Grouping Functions

Used in the BY clause of STATS and INLINE STATS to create dynamic groups.

CATEGORIZE options (9.2+):

BUCKET examples:

// 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. Available since 9.2.

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:

// 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

---

Math Functions

---

Date/Time Functions

Time units: millisecond, second, minute, hour, day, week, month, year

Timespan literals: 1 day, 2 hours, 30 minutes, 1 week

---

Type Conversion Functions

---

IP Functions

Examples:

// 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

---

Dense Vector Functions

For vector search and similarity operations on dense_vector and semantic_text fields.

---

Multivalue Functions

For handling fields with multiple values.

---

Conditional Functions

---

Full-Text Search Functions

For text search with analyzer support (available since 8.17+).

MATCH

Basic text search.

FROM articles
| WHERE MATCH(content, "elasticsearch query")

// With options
FROM docs
| WHERE MATCH(title, "search", {"operator": "AND"})

MATCH (colon operator)

Shorthand for MATCH.

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.

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.

FROM logs
| WHERE QSTR("status:error AND (type:critical OR type:warning)")

KQL

Kibana Query Language support.

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+).

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+).

FROM articles
| WHERE MATCH(content, "elasticsearch")
| EVAL relevance = SCORE()
| SORT relevance DESC

TOP_SNIPPETS

Extracts best matching snippets from text fields (9.3+).

FROM articles
| WHERE MATCH(content, "elasticsearch query")
| EVAL snippet = TOP_SNIPPETS(content, "elasticsearch query")

Relevance Scoring

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:

WHERE name LIKE "John*"
WHERE email RLIKE ".*@example\\.com"
WHERE status IN ("active", "pending")
WHERE NOT (status == "deleted")

Arithmetic Operators

• +, -, *, /, % (modulo)

---

Syntax Details

Comments

// Single line comment

/* Multi-line
   comment */

FROM logs  // inline comment
| WHERE status == 200

String Literals

// 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

// 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.

// 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.

// 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.

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

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

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

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:

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

FROM articles METADATA _score
| WHERE MATCH(content, "machine learning")
| KEEP title, author, _score
| SORT _score DESC
| LIMIT 10

Data Transformation

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

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
• Quick Strategy Rules
• Standard Workflow
• Multi-Stage Retrieval Pattern
• Retrieval Strategies
• Lexical Retrieval
• Semantic Retrieval
• Vector Retrieval
• Hybrid Retrieval
• Semantic Intent Without Semantic Field
• Reranking Stage
• Semantic Reranking
• Embedding Similarity Rescore
• Phrase Search
• Multi-Index Search
• Weak Result Recovery
• Mandatory Rules
• 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:

retrieve → (optional fuse) → rerank

Start with inexpensive retrieval and apply expensive ranking only to a limited candidate set.

Quick Strategy Rules

Standard Workflow

1. Identify Indices

Use the narrowest index pattern that satisfies the request.

// Single index
FROM knowledge-base

// Multiple indices
FROM docs-*,articles-*

2. Inspect Mappings

Before writing queries, identify searchable fields.

Preferred fields for content search:

title
name
subject
summary
body
content
description
text

Field Types

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 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.

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.

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.

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.

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:

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

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

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.

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.

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 pattern when you do not need per-index branching.

Compatible Schemas

FROM docs-*,articles-* METADATA _score
| WHERE MATCH(title, ?query) OR MATCH(body, ?query)
| SORT _score DESC
| LIMIT 20

Different Schemas

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:

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 as starting templates:

• Lexical — see Lexical Retrieval
• Semantic — see Semantic Retrieval
• Hybrid — see Hybrid Retrieval, then add a 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:

retrieve → fuse → rerank

ES|QL Query Patterns

Common patterns for generating ES|QL queries from natural language requests.

Table of Contents

• Pattern Recognition Guide
• Time-Based Queries
• Aggregation Patterns
• Filtering Patterns
• Transformation Patterns
• Log Parsing Patterns
• Advanced Patterns
• Newer Feature Patterns
• ML and Analytics Patterns
• 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

"show errors from the last hour"
→
FROM logs-*
| WHERE @timestamp > NOW() - 1 hour
| WHERE level == "error"
| SORT @timestamp DESC
| LIMIT 100

Time Range

"events between January 1 and January 15, 2024"
→
FROM events-*
| WHERE @timestamp >= "2024-01-01" AND @timestamp < "2024-01-16"

Time Bucketing

"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

"requests slower than 5 seconds"
→
FROM api-logs
| WHERE response_time > 5000
| SORT response_time DESC
| LIMIT 100

---

Aggregation Patterns

Simple Count

"how many errors are there"
→
FROM logs-*
| WHERE level == "error"
| STATS total_errors = COUNT(*)

Count by Category

"count of events by status code"
→
FROM web-logs
| STATS count = COUNT(*) BY status_code
| SORT count DESC

Multiple Aggregations

"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

"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

"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

"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

"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

"errors from production"
→
FROM logs-*
| WHERE level == "error" AND environment == "production"

Multiple Values (IN)

"events with status 400, 401, or 403"
→
FROM web-logs
| WHERE status_code IN (400, 401, 403)

Pattern Matching

"requests to /api endpoints"
→
FROM web-logs
| WHERE url LIKE "/api/*"

Full-Text Search (8.17+)

"documents containing 'connection timeout'"
→
FROM logs-* METADATA _score
| WHERE MATCH(message, "connection timeout")
| SORT _score DESC
| LIMIT 100

Null Handling

"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.

"all events except from test environment"
→
FROM events-*
| WHERE environment != "test" OR environment IS NULL

---

Transformation Patterns

Computed Fields

"show response time in seconds"
→
FROM api-logs
| EVAL response_time_sec = response_time_ms / 1000
| KEEP endpoint, response_time_sec

String Manipulation

"extract domain from email addresses"
→
FROM users
| EVAL domain = SUBSTRING(email, LOCATE("@", email) + 1, LENGTH(email))
| KEEP email, domain

Conditional Values

"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

"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+):

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

"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

"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

"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.

"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

"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.

"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

"count occurrences of each tag"
→
FROM documents
| MV_EXPAND tags
| STATS count = COUNT(*) BY tags
| SORT count DESC

Chained Aggregations

"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+)

"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+)

"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

"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

MATCH_PHRASE (8.19/9.1+)

"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).

"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.

"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.

"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:

  // 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:

  // 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:

  // 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):

  // Wrong: STDDEV(field)
  // Correct:
  | STATS sd = STD_DEV(latency_ms) BY endpoint