ES|QL Pipeline Syntax, Sources, Filtering, Transformation, Aggregation, Enrichment, and Tabular Results

Learning outcomes

AtlasMart operations analysts can already express nested aggregations in Query DSL, but the JSON becomes cumbersome when the question is naturally a sequence: choose rows, derive columns, summarize, enrich, sort, and return a table. ES|QL provides that pipeline model. The crucial design point is that it is a distinct analytical interface—not a prettier spelling of _search.

1. Mental model: a table flows through a pipeline

An ES|QL query begins with a source command, commonly FROM, that produces a table. A processing command consumes the current table and emits another table. WHERE removes rows, EVAL computes columns, KEEP/DROP shape columns, STATS changes row cardinality by aggregating, and SORT/LIMIT shape the final result. This is not the same data model as Search API hits, where each hit retains _source, scoring metadata, highlights, and search-specific controls.

POST /_query?format=txt
{
  "query": """
    FROM atlasmart-telemetry-v23
    | WHERE environment == "prod"
    | EVAL error_rate = TO_DOUBLE(error_count) / TO_DOUBLE(request_count)
    | KEEP @timestamp, service, request_count, error_count, error_rate
    | SORT @timestamp
    | LIMIT 5
  """
}

The REST response can be requested in tabular/text-like formats for people or structured formats for applications. The privilege boundary remains index authorization: running /_query requires read access to the indices/data streams/aliases/views referenced by the ES|QL query. Monitoring already-running ES|QL queries is a separate privilege surface.

2. Reproducible fixture and observability contract

Use the same concrete index on an Elasticsearch 9.5.3 local node. The fixture is intentionally small enough to calculate exact sums by hand, but it contains a null latency so that null handling is visible.

PUT atlasmart-telemetry-v23
{
  "settings": {"number_of_shards": 1, "number_of_replicas": 0},
  "mappings": {
    "dynamic": "strict",
    "properties": {
      "@timestamp": {"type": "date"},
      "service": {"type": "keyword"},
      "environment": {"type": "keyword"},
      "tenant_id": {"type": "keyword"},
      "request_count": {"type": "long"},
      "error_count": {"type": "long"},
      "duration_ms": {"type": "double"}
    }
  }
}

POST atlasmart-telemetry-v23/_bulk?refresh=true
{"index":{"_id":"e01"}}
{"@timestamp":"2026-09-11T10:00:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":100,"error_count":2,"duration_ms":120}
{"index":{"_id":"e02"}}
{"@timestamp":"2026-09-11T10:01:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":120,"error_count":3,"duration_ms":140}
{"index":{"_id":"e03"}}
{"@timestamp":"2026-09-11T10:02:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":80,"error_count":4,"duration_ms":220}
{"index":{"_id":"e04"}}
{"@timestamp":"2026-09-11T10:03:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":90,"error_count":5,"duration_ms":260}
{"index":{"_id":"e05"}}
{"@timestamp":"2026-09-11T10:05:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":130,"error_count":1,"duration_ms":110}
{"index":{"_id":"e06"}}
{"@timestamp":"2026-09-11T10:06:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":140,"error_count":2,"duration_ms":130}
{"index":{"_id":"e07"}}
{"@timestamp":"2026-09-11T10:07:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":100,"error_count":2,"duration_ms":210}
{"index":{"_id":"e08"}}
{"@timestamp":"2026-09-11T10:08:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":110,"error_count":3,"duration_ms":250}
{"index":{"_id":"e09"}}
{"@timestamp":"2026-09-11T10:10:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":150,"error_count":1,"duration_ms":100}
{"index":{"_id":"e10"}}
{"@timestamp":"2026-09-11T10:11:00Z","service":"catalog-api","environment":"prod","tenant_id":"tenant-a","request_count":160,"error_count":1}
{"index":{"_id":"e11"}}
{"@timestamp":"2026-09-11T10:12:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":120,"error_count":6,"duration_ms":280}
{"index":{"_id":"e12"}}
{"@timestamp":"2026-09-11T10:13:00Z","service":"checkout-api","environment":"prod","tenant_id":"tenant-a","request_count":130,"error_count":7,"duration_ms":300}

Before the analysis, prove the dataset rather than trusting setup output:

GET atlasmart-telemetry-v23/_count
GET atlasmart-telemetry-v23/_search
{
  "size": 0,
  "aggs": {
    "requests": {"sum": {"field": "request_count"}},
    "errors": {"sum": {"field": "error_count"}},
    "duration_values": {"value_count": {"field": "duration_ms"}}
  }
}

# Expected invariant: docs=12, request sum=1430, error sum=37,
# duration_ms has 11 values because e10 is missing it.

3. Filtering, grouping, and transformed columns

The canonical analysis is naturally expressed as a sequence. A 5-minute bucket is a grouping expression. AVG ignores null input; PERCENTILE is usually approximate. Compute error rate from aggregated counters—not by averaging per-row error rates, because weighted denominators differ.

POST /_query?format=txt
{
  "query": """
    FROM atlasmart-telemetry-v23
    | WHERE environment == "prod"
      AND @timestamp >= "2026-09-11T10:00:00Z"
      AND @timestamp <  "2026-09-11T10:15:00Z"
    | STATS
        requests = SUM(request_count),
        errors = SUM(error_count),
        avg_ms = AVG(duration_ms),
        p95_ms = PERCENTILE(duration_ms, 95)
      BY bucket = BUCKET(@timestamp, 5 minutes), service
    | EVAL error_rate = TO_DOUBLE(errors) / TO_DOUBLE(requests)
    | SORT bucket, service
  """
}

For the first 5-minute bucket the exact request/error evidence is catalog-api: 220/5 and checkout-api: 170/9. The exact averages are 130 ms and 240 ms respectively. Do not hard-code a p95 value as a cross-engine oracle: both Elasticsearch and OpenSearch percentile implementations can approximate quantiles and may use different execution details.

4. Nulls are semantics, not cleanup noise

ES|QL has three-valued logic. A missing field appears as NULL; comparisons involving null commonly produce null, and WHERE keeps only rows whose predicate is true. COUNT(*) counts rows while COUNT(duration_ms) counts only non-null values. This matters because e10 participates in request/error sums but not the duration average or percentile.

POST /_query?format=txt
{
  "query": """
    FROM atlasmart-telemetry-v23
    | WHERE @timestamp >= "2026-09-11T10:10:00Z"
      AND @timestamp <  "2026-09-11T10:15:00Z"
      AND service == "catalog-api"
    | STATS rows = COUNT(*), duration_values = COUNT(duration_ms),
            requests = SUM(request_count), avg_ms = AVG(duration_ms)
  """
}

# Semantic invariant: rows=2, duration_values=1, requests=310, avg_ms=100.

5. Enrichment: ENRICH and LOOKUP JOIN are not aliases

ENRICH uses an executed Elasticsearch enrich policy and an internal enrich index. It is useful when a governed policy snapshot is desirable. LOOKUP JOIN joins the current table to a concrete lookup-mode index and behaves more like a left join: no match retains the left row with nulls; multiple matches can create multiple output rows. Current Elasticsearch guidance often favors LOOKUP JOIN when reference data changes frequently, but the operational contract differs.

PUT atlasmart-service-directory-v23
{
  "settings": {"index.mode": "lookup"},
  "mappings": {
    "properties": {
      "service": {"type": "keyword"},
      "owner": {"type": "keyword"},
      "tier": {"type": "keyword"}
    }
  }
}
POST atlasmart-service-directory-v23/_doc/catalog?refresh=true
{"service":"catalog-api","owner":"search-platform","tier":"tier-1"}
POST atlasmart-service-directory-v23/_doc/checkout?refresh=true
{"service":"checkout-api","owner":"commerce","tier":"tier-1"}

POST /_query?format=txt
{
  "query": """
    FROM atlasmart-telemetry-v23
    | STATS errors = SUM(error_count) BY service
    | LOOKUP JOIN atlasmart-service-directory-v23 ON service
    | KEEP service, owner, tier, errors
    | SORT errors DESC
  """
}

Do not translate this directly into OpenSearch PPL. OpenSearch has its own lookup command and does not use Elasticsearch's index.mode=lookup contract.

6. Result-shape and resource boundaries

By default, ES|QL returns up to 1,000 rows; the normal configurable upper limit is 10,000 rows. The limit constrains output rows, not how many source documents the query can process. That makes ES|QL suitable for analysis and tabular application workflows but not a replacement for scroll/search-after style export or every latency-critical hit-retrieval API.

7. Production judgment and measurement

Profile correctness first, then measure. Record query text/hash, mappings, shards, cache/warm state, processed data size, returned rows, concurrency, p50/p95/p99 client latency, CPU/heap, and whether the same requirement through Search API has materially different cost. ES|QL's readability is valuable, but developer ergonomics are not evidence that it is the cheapest execution path.

interface=esql
query_hash=MEASURED
docs=12
shards=1
returned_rows=6
warmup_runs=MEASURED
measured_runs=MEASURED
client_p50_ms=MEASURED
client_p95_ms=MEASURED
client_p99_ms=MEASURED
node_cpu_delta=MEASURED
heap_delta=MEASURED
result_semantics=VERIFIED
null_semantics=VERIFIED
p95_is_approximate=true

8. Verification and cleanup

DELETE atlasmart-telemetry-v23
DELETE atlasmart-service-directory-v23

Next, use the same pipeline model across multiple indices and remote clusters, then decide when ES|QL complements Query DSL instead of replacing it.

References

• Elastic ES|QL reference — Current language model, commands, functions, and limitations.
• Elastic ES|QL REST API — /_query, formats, privileges, and async-query surface.
• Elastic ES|QL syntax — Source and processing-command pipeline semantics.
• Elastic ES|QL STATS — Grouping, filtered aggregates, and null handling.
• Elastic ES|QL ENRICH — Query-time use of executed enrich policies.
• Elastic ES|QL LOOKUP JOIN — Lookup-index joins and current cross-cluster behavior.
• Elastic ES|QL across clusters — Remote-index syntax, enrich/lookup placement, and security considerations.
• Elastic ES|QL limitations — Result-size, type, and feature boundaries.
• OpenSearch SQL and PPL — Current SQL/PPL mental models and entry points.
• OpenSearch PPL — Pipeline syntax and SQL-plugin requirement.
• OpenSearch SQL — Relational syntax and response formats.
• OpenSearch SQL/PPL API — Query, explain, cursor, and format APIs.
• OpenSearch Query Workbench — Interactive SQL/PPL workflow and read-only boundary.
• OpenSearch Dashboards notebooks — Markdown, SQL, PPL, visualization, and reporting workflow.
• OpenSearch PPL lookup — Dimension-table enrichment semantics.
• OpenSearch PPL join — Join forms, limits, and performance-sensitive modes.
• Elasticsearch release notes — verify the pinned server baseline and current ES|QL status.
• OpenSearch version history — verify the pinned OpenSearch/Dashboards baseline.