Elasticsearch Esql

Elasticsearch ES|QL

Execute ES|QL queries against Elasticsearch: discover the schema, choose the right ES|QL feature for the task, generate the simplest correct query, and run it.

<!-- begin-partial: preamble -->

Environment Configuration

This skill executes Elasticsearch operations through the elastic CLI. If the elastic CLI is not installed, tell the user what it is needed for. Do not guess credentials, call the HTTP API directly, or attempt other workarounds.

This skill references operations in HTTP-shorthand form (e.g., GET /, GET /_cat/indices, GET /{index}/_mapping, GET /{index}/_settings/index.mode, POST /_query). The Operations table at the end of this document maps each shorthand to the equivalent elastic CLI command — always use the CLI rather than calling the HTTP API directly.

<!-- end-partial: preamble -->

What is ES|QL?

ES|QL (Elasticsearch Query Language) is a piped query language for Elasticsearch. It is NOT the same as:

• Elasticsearch Query DSL (JSON-based)
• SQL
• EQL (Event Query Language)

ES|QL uses pipes (|) to chain commands: FROM index | WHERE condition | STATS aggregation BY field | SORT field | LIMIT n

Prerequisite: ES|QL requires _source to be enabled on queried indices. Indices with _source disabled (e.g., "_source": { "enabled": false }) will cause ES|QL queries to fail.

Version Compatibility: ES|QL was introduced in 8.11 (tech preview) and became GA in 8.14. Features like LOOKUP JOIN (8.18+), MATCH (8.17+), and INLINE STATS (9.2+) were added in later versions. On pre-8.18 clusters, use ENRICH as a fallback for LOOKUP JOIN (see generation tips). INLINE STATS and counter-field RATE() have no fallback before 9.2. Check references/esql-version-history.md for feature availability by version.

Cluster Detection: Call GET / to determine the cluster type and version:

• build_flavor: "serverless" — Elastic Cloud Serverless. version.number tracks the stack line under active development (next minor from main), so clients that only semver-compare may treat Serverless as “latest.” Do not use version.number to gate features: if build_flavor is "serverless", assume all GA and preview ES|QL features are available.
• build_flavor: "default" — Stack (self-managed or Cloud-hosted). Use version.number for feature availability.
• Snapshot builds have version.number like 9.4.0-SNAPSHOT. Strip the -SNAPSHOT suffix and use the major.minor for version checks. Snapshot builds include all features from that version plus potentially unreleased features from development — if a query fails with an unknown function/command, it may simply not have landed yet. Elastic employees commonly use snapshot builds for testing.

Process

1. Verify the connection and detect the deployment type. Call GET / first. This confirms connectivity and detects whether the deployment is a Serverless project (all features available) or a versioned cluster (features depend on version). The build_flavor field is the authoritative signal — if it equals "serverless", ignore the reported version number and use all ES|QL features freely. If the call fails, stop and point the user at the CLI configuration instructions rather than guessing endpoints or credentials.

2. Discover the schema (required — never guess index or field names). List candidate indices with GET /_cat/indices (pass a pattern to narrow), then fetch field types for the chosen index with GET /{index}/_mapping.

   Always run schema discovery before generating queries. Index names and field names vary across deployments and cannot be reliably guessed. Even common-sounding data (e.g., "logs") may live in indices named logs-test, logs-app-*, or application_logs. Field names may use ECS dotted notation (source.ip, service.name) or flat custom names — the only way to know is to check.

   Prefer simplicity: Query a single index unless the user explicitly asks for data across multiple sources. Do not combine indices with different schemas using COALESCE unless specifically requested — pick the single most relevant index for the question. When multiple indices contain similar data, prefer the one with the most complete schema for the task at hand.

   Detect time series indices. Check the index mode with GET /{index}/_settings/index.mode. If it is time_series, use TS <data-stream> (not FROM), TBUCKET(interval) (not DATE_TRUNC), and wrap counter fields with SUM(RATE(...)). Read the full TS section in Generation Tips before writing any time series query. For TSDS indices on 9.4+, prefer the in-language discovery commands METRICS_INFO and TS_INFO (both GA) over inspecting mappings — they enumerate the metric catalogue and the dimension labels of each time series directly, and are run as ES|QL queries via POST /_query. Treat METRICS_INFO as authoritative for metric_type (counter/gauge/histogram) and field_type (histogram, tdigest, exponential_histogram for distribution metrics). Both must follow TS and must precede STATS/SORT/LIMIT. See Time Series Queries:

   TS metrics-tsds | METRICS_INFO | SORT metric_name
   TS metrics-tsds | TS_INFO | KEEP metric_name, dimensions | SORT metric_name

3. Choose the right ES|QL feature for the task. Before writing queries, match the user's intent to the most appropriate ES|QL feature. Prefer a single advanced query over multiple basic ones.

   • "find patterns," "categorize," "group similar messages" → CATEGORIZE(field)
   • "spike," "dip," "anomaly," "when did X change" → CHANGE_POINT value ON key
   • "trend over time," "time series" → STATS ... BY BUCKET(@timestamp, interval) or TS for TSDB
   • "PromQL", "Prometheus query/dashboard/alert", sum by (instance) (...), label matchers like {cluster="prod"} → PROMQL source command (9.4+ preview); see PROMQL Command. 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
   • "approximate," "estimate," "rough numbers," "fast/cheap stats on huge data" → SET approximation=true; before a STATS query (GA in 9.5+/Serverless, preview in 9.4); see Query Approximation

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, histogram metrics
   • 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)
   • Query Approximation — read before using SET approximation: output columns, sampling/confidence-level tuning, unsupported functions and patterns (GA in 9.5+/Serverless, preview in 9.4)

5. Generate the query following ES|QL syntax. Prefer the simplest query that answers the question — do not add extra indices, fields, or transformations unless the user asks for them. Only include fields in KEEP that directly answer the question. Do not add extra filter conditions beyond what the user specified (e.g., don't add OR level == "ERROR" when the user just said "errors").

   • Start with FROM index-pattern (or TS index-pattern for time series indices)
   • Add WHERE for filtering (use TRANGE for time ranges on 9.3+)
   • Use EVAL for computed fields
   • Use STATS ... BY for aggregations
   • For time series metrics: TS with SUM(RATE(...)) for counters, AVG(...) for gauges, standard aggregations (SUM, AVG, PERCENTILE, … — not *_OVER_TIME) for histogram metrics, and TBUCKET(interval) for time bucketing — see the TS section in Generation Tips and Histogram Metrics
   • For detecting spikes, dips, or anomalies, use CHANGE_POINT after time-bucketed aggregation
   • Add SORT and LIMIT as needed

6. Execute the query with POST /_query. Request tabular (TSV) output for clean, decoration-free results that are easy to read and post-process.

ES|QL Quick Reference

Version availability: This section omits version annotations for readability. Check ES|QL Version History 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