Translate One Analytical Requirement Between Query DSL+Aggregations, ES|QL, and PPL and Compare Semantics/Cost

Learning outcomes

The chapter ends with a controlled translation exercise. The goal is not to declare a universal winner. It is to prove that the same AtlasMart business question can produce equivalent domain facts through three different interfaces while exposing different response shapes, planning surfaces, feature gaps, and resource costs.

1. One fixture, one semantic oracle

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}

The deterministic oracle covers exact fields only. Across all six 5-minute/service buckets, request/error sums can be calculated exactly. e10 is missing duration and must not be invented as zero. Percentiles are deliberately outside the exact oracle.

2. Query DSL + aggregations implementation

Query DSL expresses the source filter plus nested bucket/metric aggregations. The client receives an aggregation tree; error rate can be computed with a pipeline aggregation or in domain code. The date histogram explicitly uses a fixed five-minute interval.

POST atlasmart-telemetry-v23/_search
{
  "size": 0,
  "query": {"bool": {"filter": [
    {"term": {"environment": "prod"}},
    {"range": {"@timestamp": {"gte": "2026-09-11T10:00:00Z", "lt": "2026-09-11T10:15:00Z"}}}
  ]}},
  "aggs": {
    "time": {
      "date_histogram": {"field": "@timestamp", "fixed_interval": "5m", "min_doc_count": 0},
      "aggs": {
        "service": {
          "terms": {"field": "service", "size": 20},
          "aggs": {
            "requests": {"sum": {"field": "request_count"}},
            "errors": {"sum": {"field": "error_count"}},
            "avg_ms": {"avg": {"field": "duration_ms"}},
            "p95_ms": {"percentiles": {"field": "duration_ms", "percents": [95]}},
            "error_rate": {"bucket_script": {"buckets_path": {"e":"errors","r":"requests"}, "script":"params.r == 0 ? null : params.e / params.r"}}
          }
        }
      }
    }
  }
}

Terms aggregation can be approximate for high-cardinality distributed data when buckets are truncated. Our two-service fixture avoids that edge case; production tests must not infer global exactness from this toy dataset.

3. ES|QL implementation

POST /_query?format=json
{
  "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
  """
}

The result is a column schema plus rows. Normalize by column name, not by position hard-coded from a screenshot. The query's readability is a real developer benefit, but row caps and different search-feature coverage remain part of the interface decision.

4. OpenSearch PPL implementation

POST /_plugins/_ppl?format=jdbc
{
  "query": "source=atlasmart-telemetry-v23 | where environment='prod' AND @timestamp >= timestamp('2026-09-11 10:00:00') AND @timestamp < timestamp('2026-09-11 10:15:00') | stats sum(request_count) as requests, sum(error_count) as errors, avg(duration_ms) as avg_ms, percentile(duration_ms,95) as p95_ms by span(@timestamp,5m), service | eval error_rate=errors/requests | sort + `span(@timestamp,5m)`, + service"
}

PPL's span, aggregation functions, and response metadata are OpenSearch contracts. Do not copy the ES|QL BUCKET expression or assume identical type names. If PPL explains to a terms/date-histogram plan, that helps explain why distributed approximation boundaries can resemble native aggregations—but runtime behavior still needs measurement.

5. Normalize before comparing

Create one domain shape: {bucket, service, requests, errors, error_rate, avg_ms, p95_ms}. Compare exact fields with a strict oracle. Compare floating error rates within a declared numeric tolerance. Compare percentiles only for plausibility/algorithm tolerance, not bitwise identity.

for row in normalized_rows:
    assert row.requests == oracle[row.key].requests
    assert row.errors == oracle[row.key].errors
    assert abs(row.error_rate - oracle[row.key].error_rate) < 1e-9
    assert abs(row.avg_ms - oracle[row.key].avg_ms) < 1e-9
    assert row.p95_ms is not null
    # Do NOT require identical approximate percentile output across engines/interfaces.

assert normalized[('10:10','catalog-api')].avg_ms == 100.0
# This proves missing duration_ms was ignored rather than converted to 0.

6. Controlled edge cases

7. Fair benchmark protocol

Run Elasticsearch Query DSL and ES|QL against the same Elasticsearch node/data; run OpenSearch Query DSL and PPL against the same OpenSearch node/data. Do not compare Elastic-vs-OpenSearch latency as if language alone caused the difference. For interface comparisons within a product, keep mappings, shard count, cache state, hardware, network, authentication, concurrency, and output cardinality fixed.

product=Elasticsearch_9.5.3
interfaces=QueryDSL,ESQL
product=OpenSearch_3.8.0
interfaces=QueryDSL,PPL

docs=MEASURED
index_bytes=MEASURED
shards=1
concurrency=MEASURED
warmup_runs=MEASURED
measured_runs=MEASURED
cache_state=DOCUMENTED
result_rows=6
client_p50_ms=MEASURED
client_p95_ms=MEASURED
client_p99_ms=MEASURED
cpu=MEASURED
heap=MEASURED
bytes_received=MEASURED
semantic_test=PASS|FAIL

8. Decision record

A practical AtlasMart decision might use Query DSL for latency-sensitive product search, ES|QL for Elastic operational investigations and analyst tables, and PPL for OpenSearch observability/notebook work. That is a workload allocation, not a language ranking.

9. Chapter release gate and bridge to Chapter 24

DELETE atlasmart-telemetry-v23
# Delete any temporary lookup/service-directory indices created during the chapter.
DELETE atlasmart-service-directory-v23

Chapter 24 turns the measurements used here into an operational evidence model: cluster/node/index metrics, dashboards, alerts, incidents, and search SLOs.

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.