Elasticsearch GROUP BY in SQL, run as a composite aggregation

Aggregations are what Elasticsearch is good at and what Query DSL makes hardest to write. A GROUP BY with two keys and three metrics is one line of SQL and forty lines of composite aggregation, plus a loop to page through buckets. FlatQL does that translation on every Run and leaves the request body open for inspection.

GROUP BY country, as the cluster saw it#

SELECT country, COUNT(*) AS orgs, MIN(founded_year) AS oldest
FROM organizations
WHERE status = 'active'
GROUP BY country;

country is a keyword, COUNT(*) and MIN are on the pushable list, and status = 'active' is an exact term filter. The three conditions in the engine's pushdown rule are met: "GROUP BY: pushed as a composite aggregation when every grouping key is a plain groupable field, every aggregate is COUNT / SUM / AVG / MIN / MAX (plus approximate COUNT DISTINCT) on a plain field, and the WHERE was pushed in full." FlatQL sends POST /organizations/_search with this body:

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        { "term": { "status": "active" } }
      ]
    }
  },
  "aggs": {
    "g": {
      "composite": {
        "size": 1000,
        "sources": [
          { "k0": { "terms": { "field": "country", "missing_bucket": true } } }
        ]
      },
      "aggs": {
        "m1": { "min": { "field": "founded_year" } }
      }
    }
  }
}

A few details worth knowing about that body:

• size: 0 means no documents travel, only buckets.
• Each grouping key is a terms source named k0, k1 and so on. missing_bucket: true keeps documents with no country as a NULL group, the way SQL does.
• COUNT(*) needs no sub-aggregation; it is read from each bucket's doc_count. Other metrics become min, max, sum, avg, value_count or cardinality sub-aggregations named by position.
• Composite aggregations page. FlatQL reads 1,000 buckets per request and sends the response's after_key back as after until the cluster returns none, so you never see the paging.
• If a text field is used as a key, the DSL shows its .keyword sub-field; you never write that suffix in SQL.

Grouped statements on an index pattern such as "logs-*" work the same way, since the request goes to the pattern and the cluster merges shards across indices.

Time buckets with DATE_TRUNC#

The usual log question groups by time and service:

SELECT DATE_TRUNC('hour', "@timestamp") AS hour, service, COUNT(*) AS errors
FROM "logs-*"
WHERE "@timestamp" >= '2026-11-03' AND level = 'ERROR'
GROUP BY 1, 2
ORDER BY 1;

The WHERE is two exact filters (a range on @timestamp and a term on level) and both are pushed, so the fetch is only the error rows of the last day. The DATE_TRUNC key is a function over a field rather than a plain field, so the grouping itself runs in the engine over the fetched rows, up to the 50,000-row cap per index, and the result carries a note saying so. Keep the time range tight and the fetch stays small.

Grouping on the raw date field instead (GROUP BY "@timestamp") is pushed, because date is a groupable type, but it gives one bucket per distinct millisecond, which is rarely the question. The date histogram glossary entry explains the aggregation this maps to in Query DSL.

HAVING, ROLLUP and the engine#

SELECT country, status, COUNT(*) AS n
FROM organizations
GROUP BY country, status
HAVING COUNT(*) > 10
ORDER BY n DESC;

The grouping is pushed exactly as before, with two composite sources. HAVING is not something Elasticsearch understands for composite buckets, so FlatQL applies it in the engine to the bucket rows that came back, then sorts them. This is cheap: the engine is filtering groups, not documents, and the groups are already capped at 50,000. ORDER BY and LIMIT on any aggregated statement work the same way, since "LIMIT / ORDER BY: pushed only for single-table statements with no aggregation, DISTINCT, window or QUALIFY."

ROLLUP, CUBE and GROUPING SETS are different. Their grouping keys are not plain fields, so the whole statement runs in the engine over a scan of the filtered index. GROUP BY ROLLUP (country, status) on a few thousand active organizations is fine; on an unfiltered index of millions it hits the cap and the result says the scan was truncated. Push a selective WHERE first. The CTEs, UNION and HAVING page has more patterns, and limits lists every cap the engine applies.

Bucket caps and approximations#

Two places where the SQL answer differs from a relational database, both flagged on the result:

The pushdown page lists every rule, and the Show as DSL tab on each result tells you which path a given statement took.

Without FlatQL#

Composite aggregations by hand. You write the sources array, name every metric, set a page size, and send the request. Then you read after_key from the response, add it as after, and send again until no after_key comes back, collecting buckets as you go. A HAVING clause is a filter you apply to the collected buckets in your own code; a ROLLUP is a second and third request with fewer sources, or a client-side sum. None of this is difficult, but it is the same forty lines every time the question changes, and a typo in a field name returns an empty bucket list rather than an error.

Terms aggregations. Simpler than composite and fine for "top 10 by count", but they are approximate across shards and limited by size, so they are not a substitute for a full GROUP BY.

Kibana Lens or a visualisation. Kibana builds the aggregation for you from a point-and-click layout, which is the right answer when the output is a dashboard panel. Getting the grouped rows into a spreadsheet, or joining them to another index, means leaving the tool. The Kibana comparison goes through those differences.

x-pack SQL or the OpenSearch SQL plugin. Both accept GROUP BY with the common aggregates on a single index and translate it to aggregations; see x-pack SQL and the OpenSearch SQL plugin for what each dialect accepts. Neither gives you the composite body to inspect, and grouped output cannot be joined to a second index in the same statement.

Run a GROUP BY on your cluster#

The Free plan includes the SQL engine and Show as DSL for one cluster; Pro, a one-time $199 licence, adds charts and pivots over the same grouped results, exports and unlimited clusters. The pricing page has the full list. Create an account, connect a cluster and group by anything you like.