Conceptual Mapping
Elasticsearch's aggregation framework and SQL's GROUP BY solve the same basic problem, summarizing rows by group, but structure it differently: a terms aggregation is roughly equivalent to GROUP BY on a column, metric aggregations like sum and avg correspond to SQL's SUM() and AVG() aggregate functions, and nesting further bucket aggregations inside a terms aggregation is similar to grouping by multiple columns (GROUP BY category, month) or running a windowed breakdown. The bucket_selector pipeline aggregation, covered below, plays the role SQL's HAVING clause plays: filtering groups after aggregation rather than filtering rows before it.
Cricket analogy: It's like grouping a batter's innings by opposition team, terms aggregation, then computing their average against each, metric aggregation, the same logic ESPNcricinfo's Statsguru uses for a SQL-backed query.
Key Differences in Execution Model
SQL GROUP BY runs against a row-oriented, transactionally consistent relational engine that produces exact, deterministic results and supports joins across tables within the query itself. Elasticsearch aggregations run over a distributed, near-real-time search engine reading columnar doc_values across potentially many shards, and while most aggregations are exact, cardinality and percentiles are intentionally approximate to bound memory at scale, and there's no native cross-index join, so denormalization or application-side joining is typically required.
Cricket analogy: It's like the difference between an official, verified scorecard, exact and final, and a live in-stadium estimate of run rate that updates instantly but gets finalized later, the trade-off between exactness and real-time speed.
-- SQL
SELECT category, AVG(price) AS avg_price, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;// Equivalent Elasticsearch aggregation
GET /products/_search
{
"size": 0,
"aggs": {
"by_category": {
"terms": { "field": "category.keyword", "size": 100, "order": { "avg_price": "desc" } },
"aggs": {
"avg_price": { "avg": { "field": "price" } },
"min_count_filter": {
"bucket_selector": {
"buckets_path": { "count": "_count" },
"script": "params.count > 5"
}
}
}
}
}
}Filtering Buckets and Post-Processing
The bucket_selector pipeline aggregation evaluates a script against the outputs of sibling aggregations within each bucket and drops buckets that don't satisfy it, directly mirroring HAVING's role of filtering groups after aggregation rather than filtering rows before it with WHERE. The related bucket_script pipeline aggregation computes new values from existing bucket metrics, similar to a computed column in a SQL SELECT, for example deriving a profit_margin bucket value from existing revenue and cost metric aggregations already computed per bucket.
Cricket analogy: It's like a selector script dropping any bowler's bucket where economy rate exceeds 9, mirroring how a coach filters a post-match report to only bowlers who conceded under a threshold, after stats are computed.
Elasticsearch aggregations have no equivalent to a SQL JOIN across separate indices within a single request. If your grouped report needs data from two different indices, you must either denormalize the related data into one document at index time, use the join field type for parent-child relationships within a single index, or perform the join in application code across two separate queries.
When to Prefer Each
Elasticsearch aggregations shine when you need real-time, faceted analytics layered directly on top of full-text search results, filtering by a free-text query and instantly seeing grouped breakdowns of the matching set, which a traditional SQL warehouse can't do as fluidly since it isn't built around relevance-scored search. Conversely, SQL and dedicated OLAP systems remain the better choice when you need exact, auditable results, complex multi-table joins, and strict transactional consistency, such as financial reconciliation reports where an approximate cardinality count would be unacceptable.
Cricket analogy: It's like using instant, filterable ball-by-ball search during a live broadcast to slice stats by any situation on the fly, versus the exact, audited scorecard published after the match for the official record.
- terms aggregation ~ GROUP BY column; metric aggregations (avg, sum, min, max) ~ SQL aggregate functions.
- bucket_selector plays the role of HAVING, filtering groups after aggregation rather than rows before it.
- bucket_script computes derived values from existing bucket metrics, similar to a computed SELECT column.
- SQL GROUP BY produces exact, deterministic, transactionally consistent results; Elasticsearch aggregations are exact except for cardinality and percentiles, which are approximate by design.
- Elasticsearch has no native cross-index JOIN; denormalization, the join field type, or application-side joining fill that gap.
- Elasticsearch aggregations excel at real-time faceted analytics layered on full-text search results.
- SQL and OLAP systems remain preferable for exact, auditable, multi-table analytical reporting like financial reconciliation.
Practice what you learned
1. Which Elasticsearch aggregation is most conceptually similar to SQL's GROUP BY?
2. Which Elasticsearch pipeline aggregation plays the role of SQL's HAVING clause?
3. What is a key accuracy difference between SQL GROUP BY and Elasticsearch aggregations?
4. How does Elasticsearch typically handle data that would require a SQL JOIN across two tables?
5. When is SQL/OLAP generally preferable to Elasticsearch aggregations?
Was this page helpful?
You May Also Like
Bucket Aggregations
Understand how Elasticsearch's bucket aggregations group documents by terms, ranges, and dates, and how they nest with metric aggregations for multi-level breakdowns.
Metric Aggregations
Learn how Elasticsearch's metric aggregations compute single- and multi-value numeric summaries like avg, sum, cardinality, and percentiles over matched documents.
Aggregation Performance
Learn what drives aggregation performance in Elasticsearch, doc_values, shard sizing, caching, and approximate algorithms, and how to keep large aggregations fast.