elasticsearch-esql

Files (11)

• SKILL.md
• dsl-to-esql-migration.md · references/
• esql-reference.md · references/
• esql-search-strategy.md · references/
• esql-search.md · references/
• esql-version-history.md · references/
• generation-tips.md · references/
• promql-command.md · references/
• query-approximation.md · references/
• query-patterns.md · references/
• time-series-queries.md · references/

SKILL.md (first lines)

# Elasticsearch ES|QL

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

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

## Environment Configuration

This skill executes Elasticsearch operations through the `elastic` CLI. If the
[`elastic` CLI](https://github.com/elastic/cli#configuration) is not installed, tell the user what it is needed for. Do
not guess credentials, call the HTTP API directly, or attempt other workarounds.

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

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

## What is ES|QL?

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

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

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

> **Prerequisite:** ES|QL requires `_source` to be enabled on queried indices. Indices with `_source` disabled (e.g.,
> `"_source": { "enabled": false }`) will cause ES|QL queries to fail.
>
> **Version Compatibility:** ES|QL was introduced in 8.11 (tech preview) and became GA in 8.14. Features like
> `LOOKUP JOIN` (8.18+), `MATCH` (8.17+), and `INLINE STATS` (9.2+) were added in later versions. On pre-8.18 clusters,
> use `ENRICH` as a fallback for `LOOKUP JOIN` (see generation tips). `INLINE STATS` and counter-field `RATE()` have
> **no fallback** before 9.2. Check [references/esql-version-history.md](references/esql-version-history.md) for feature
> availability by version.
>
> **Cluster Detection:** Call `GET /` to determine the cluster type and version:
>
> - `build_flavor: "serverless"` — Elastic Cloud Serverless. `version.number` tracks the stack line under active
>   development (next minor from main), so clients that only semver-compare may treat Serverless as “latest.” **Do not**
>   use `version.number` to gate features: if `build_flavor` is `"serverless"`, assume all GA and preview ES|QL features
>   are available.
> - `build_flavor: "default"` — Stack (self-managed or Cloud-hosted). Use `version.number` for feature availability.
> - **Snapshot builds** have `version.number` like `9.4.0-SNAPSHOT`. Strip the `-SNAPSHOT` suffix and use the
>   major.minor for version checks. Snapshot builds include all features from that version plus potentially unreleased
>   features from development — if a query fails with an unknown function/command, it may simply not have landed yet.
>   Elastic employees commonly use snapshot builds for testing.

## Process

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

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

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

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

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

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

3. **Choose the right ES|QL feature for the task.** **Version gate (mandatory on Stack):** before writing any query,
   compare `version.number` to the version tag of every command and function you plan to use. For each feature newer
   than the cluster (for example a 9.5 feature on 9.4), use its fallback from the table below or tell the user it is
   unavailable on their version — never emit it, and never silently drop the part of the question it answers. Serverless
   has every released GA and preview feature. Then match the user's intent to the most appropriate ES|QL feature,
   preferring 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 (GA in 9.5+/Serverless, preview in 9.4); see
     [PROMQL Command](references/promql-command.md). Prefer `TS` for native ES|QL phrasing.
   - "search," "find documents matching" → `MATCH` (default), `QSTR` (advanced boolean), `KQL` (Kibana migration). For
     content/document relevance search, follow the [ES|QL Search Strategy](references/esql-search-strategy.md)
   - "count," "average," "breakdown" → `STATS` with aggregation functions
   - "approximate," "estimate," "rough numbers," "fast/cheap stats on huge data" → `SET approximation=true;` before a
     `STATS` query (GA in 9.5+/Serverless, preview in 9.4); see [Query Approximation](references/query-approximation.md)
   - "random sample," "random subset of rows" → `| SAMPLE 0.1` (row probability; preview 8.19/9.1, GA 9.5), then
     `LIMIT n` if a row count is asked; there is no `RANDOM()` function
   - "latest/first value per group," "most recent status per host" → `STATS LAST(field, @timestamp) BY group` (or
     `FIRST`), not `SORT` + `LIMIT 1`
   - allow list, deny list, "exclude known IPs/users" from another index → `WHERE field [NOT] IN (FROM idx | KEEP col)`
     (preview 9.5); use `LOOKUP JOIN` only when you need the other index's columns
   - "unique combinations," "no duplicates" → `STATS BY a, b`; Serverless only: `KEEP a, b | DEDUP` (preview)
   - IPs mapped as `keyword` → convert with `TO_IP()` before `CIDR_MATCH`, IP comparisons, `IN (FROM …)`, or
     `LOOKUP JOIN` on an `ip` field; for the last two `EVAL ip = TO_IP(src)` first (the left side must be a column)
   - "country/city of an IP," "geolocate IPs," ASN → `| IP_LOCATION geo = ip_field` pipe command (preview 9.5)
   - parse URLs (domain, path, query) → `| URI_PARTS parts = url_field` pipe command (preview 9.4), not `DISSECT`/`GROK`
   - "in my local time," "per day in New York" → `SET time_zone = "America/New_York";` (9.4+), not manual offsets
   - `flattened` field sub-keys (e.g. OTel `resource.attributes`) → `FIELD_EXTRACT(field, "host.name")` (preview 9.5),

From elastic/agent-skills, Apache-2.0.

More from elastic/agent-skills