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"

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. Both must follow TS and must precede STATS/SORT/LIMIT. See Time Series Queries.

   node scripts/esql.js raw "TS metrics-tsds | METRICS_INFO | SORT metric_name" --tsv
   node scripts/esql.js raw "TS metrics-tsds | TS_INFO | KEEP metric_name, dimensions | SORT metric_name" --tsv

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

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

• PROMQL Command — read before any PROMQL query: options, output schema,

limitations, and PROMQL vs TS decision matrix (9.4+ preview)

• 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

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 for the full reference.

// 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 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
• PROMQL Command - PromQL source command for TSDS indices (9.4+ preview)
• 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 support | ✅ | ✅ SET time_zone (Serverless GA) | | JOIN (non-lookup) | N/A | ❌ (only LEFT JOIN on lookup index) | | Subqueries / UNION ALL | N/A | ✅ FROM subqueries (Serverless preview) |

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.

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 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 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 table and esql-version-history.md for

per-function availability.

Table of Contents

• When to Use Full-Text Search
• Functions Overview
• MATCH
• Colon Operator (`:`)
• MATCH_PHRASE
• QSTR (Query String)
• KQL (Kibana Query Language)
• Relevance Scoring
• Semantic Search
• FORK / FUSE (Hybrid Search)
• LOOKUP JOIN
• Parameters in ES|QL
• Advanced Search Functions (Preview)
• Placement Rules
• Common Patterns
• Full-Text Search vs Pattern Matching
• 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

---

MATCH

Single-field text search. Equivalent to the Query DSL match query.

Syntax

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

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

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

field : "query"

Examples

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

MATCH_PHRASE(field, phrase)
MATCH_PHRASE(field, phrase, {"option": value})

Examples

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

Supported Field Types

text, keyword. Does not support semantic_text or numeric types.

MATCH vs MATCH_PHRASE

---

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

QSTR(query_string)
QSTR(query_string, {"option": value})

Query String Mini-Language

Examples

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

---

KQL (Kibana Query Language)

Run KQL queries within ES|QL. Useful for migrating existing Kibana search bar queries without rewriting them.

Syntax

KQL(kql_string)
KQL(kql_string, {"option": value})

Examples

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

---

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:

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:

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:

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:

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.

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

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

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

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

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

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)

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:

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

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.

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

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

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

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

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

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

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

---

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

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.

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
• Feature Availability by Version
• Major Limitations
• Cross-Cluster Query Support
• Output Formats
• API Endpoints
• Performance Tips by Version
• Version Detection
• References

Version Timeline Overview

Feature Availability by Version

Commands

Full-Text Search Functions

Scoring support:

• METADATA _score available from 8.18/9.0
• Must use SORT _score DESC to rank by relevance

Spatial Functions

Date/Time Functions

String Functions

Multi-Value Functions

Aggregation Functions

Grouping Functions

Per-Aggregation WHERE

Available since 8.16. Allows filtering individual aggregations without affecting others:

| STATS total = COUNT(*), errors = COUNT(*) WHERE level == "error" BY service.name

IP Functions

Time Series Aggregation Functions

Available under TS ... | STATS. See 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.

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

Type 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

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 (Limited before Serverless / 9.4)

ES|QL has limited timezone support on self-managed clusters prior to 9.4. All dates are processed in UTC internally and there is no per-function timezone argument.

On Serverless, ES|QL supports query-wide timezone via the SET time_zone directive (GA on 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.

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
• GitHub tracking issue: #107560

Self-managed before 9.4:

• SET time_zone only accepts UTC offsets ("+05:00"), not IANA timezone strings
• Workaround: use EVAL to add/subtract hours manually:

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

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` (Serverless tech preview) for combining results from multiple pipelines (UNION ALL semantics). These are non-correlated — each branch is independent.

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

Output Formats

API Endpoints

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

9.4+

• TS source command and all time series aggregation functions are now GA — both the 9.2-introduced set

(RATE, IRATE, INCREASE, DELTA, IDELTA, *_OVER_TIME, PRESENT_OVER_TIME, ABSENT_OVER_TIME) and the 9.3-introduced set (DERIV, PERCENTILE_OVER_TIME, STDDEV_OVER_TIME, VARIANCE_OVER_TIME).

• TBUCKET grouping function is GA.
• New WITHOUT(...) grouping function (GA) for time series queries: BY WITHOUT(dim1, ...) groups by every dimension

except the listed ones; BY WITHOUT() (no args) is equivalent to the implicit "group by all dimensions" behavior.

• New METRICS_INFO and TS_INFO processing commands (both GA) for discovering the metric catalogue and dimension

labels of TSDS data without inspecting index mappings. Both must come after a TS source command and must appear before pipeline-breaking commands (STATS/SORT/LIMIT). METRICS_INFO returns one row per distinct metric signature; TS_INFO returns one row per (metric, time series) combination with the identifying dimension labels.

• Sliding window parameter (RATE(field, 10m)) accepts arbitrary durations — no longer limited to multiples of the

TBUCKET interval. Note: a single query cannot mix windows smaller than the bucket for one metric with windows larger than the bucket for another metric.

• New PROMQL source command (preview) to run Prometheus Query Language directly against TSDS indices, with implicit

range selectors and a Kibana-aware step/buckets model. See promql-command.md. Prefer PROMQL only when the user explicitly thinks in PromQL or is migrating Prometheus dashboards/alerts; otherwise prefer TS.

• MV_EXPAND is GA
• VALUES aggregation is GA

Serverless (latest)

• SET time_zone with IANA timezone strings for query-wide timezone support (GA)
• LIMIT n BY field for grouped top-N queries
• URI_PARTS, USER_AGENT, REGISTERED_DOMAIN pipe commands for parsing structured strings
• FROM subqueries for combining results from multiple pipelines (tech preview)
• EARLIEST/LATEST aliases for FIRST/LAST aggregations
• JSON_EXTRACT on METADATA _source for accessing flattened field sub-keys

Version Detection

To check ES|QL availability and version:

# 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
• ES|QL Limitations
• Elasticsearch Release Notes
• ES|QL for Search
• LOOKUP JOIN Documentation

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 self-managed

("default"), use version.number for feature checks (strip -SNAPSHOT suffix on pre-release builds).

Table of Contents

• Critical Syntax Rules
• Step-by-Step Generation Process
• Field Name Conventions
• Query Optimization Tips
• Key Patterns
• Common Query Templates
• Handling Ambiguity
• 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".

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

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

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

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

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

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

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

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

TS metrics-*          // Time series source — enables RATE, AVG_OVER_TIME, etc.

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

// Multiple filters
| WHERE level == "error"
| WHERE environment == "production"
| WHERE service.name == "api-gateway"

Or combined:

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

| WHERE environment != "test" OR environment IS NULL

4. Determine Output Type

Question: Does the user want raw data or aggregated results?

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:

| KEEP @timestamp, host.name, message, level

For aggregations, the output fields are defined by STATS:

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

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

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.

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:

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

// Specific range is faster
| WHERE @timestamp > NOW() - 1 hour

// Than scanning all data
// (no time filter)

3. Limit Fields

Only keep fields you need:

// Good - specific fields
| KEEP @timestamp, message, host.name

// Less efficient - all fields
// (no KEEP command)

4. Use LIMIT

Prevent returning excessive rows:

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

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

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:

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

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

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:

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. Three critical syntax rules:

1. Use the data stream name, not the resolved backing index:

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

// WRONG — DATE_TRUNC-style syntax
| STATS avg_cpu = AVG(cpu) BY bucket = TBUCKET(@timestamp, 5 minutes)

// CORRECT — duration only, timestamp is implicit
| STATS avg_cpu = AVG(cpu) BY bucket = TBUCKET(5 minutes)

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:

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

TS metrics-tsds
| STATS avg_cpu = AVG(AVG_OVER_TIME(cpu)) BY TBUCKET(5 minutes), service.name

See Time Series Queries for the full inner/outer aggregation model.

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

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

Grouped Top-N with LIMIT BY (Serverless)

LIMIT n BY field keeps the top N rows per group after sorting. The number comes before BY.

// 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 (Serverless tech preview) combine results from different data sources (UNION ALL semantics). FORK runs different analyses on the same data source.

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

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

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

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

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

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

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:

FROM <index-pattern>  // Specify your index
| WHERE ...

Missing Time Range

Add a sensible default:

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

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