Query Elasticsearch with SQL, joins and GROUP BY included

Elasticsearch stores documents and answers Query DSL. Most of the people who need answers from it think in SQL. FlatQL sits between the two: you type SQL in a worksheet, FlatQL decides which parts of the statement the cluster can run exactly, sends those as Query DSL, and runs whatever is left in an embedded engine. The result grid tells you which half did what.

What "SQL on Elasticsearch" means here#

Three steps happen on every Run, and they are the same three steps whether you query Elasticsearch or OpenSearch.

1. Parse. The statement is parsed as Snowflake-style SQL. Tables are index names, aliases or index patterns such as "logs-*". Columns are mapping fields; nested paths use col:field.
2. Push down. For every index named in FROM or JOIN, FlatQL pulls rows from the cluster and pushes what translates exactly. In the engine's own words: "WHERE: a conjunct is pushed only when it maps 1:1 to a filter (column vs literal on a field we can filter exactly). The full WHERE still runs in DuckDB; pushing just shrinks the fetch." LIMIT and ORDER BY are "pushed only for single-table statements with no aggregation, DISTINCT, window or QUALIFY, and only when the whole WHERE was pushed." GROUP BY is "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."
3. Finish in DuckDB. Rows land in an in-process DuckDB and the statement runs there, transpiled from Snowflake to DuckDB SQL. That is where joins, CTEs, window functions and QUALIFY, UNION, HAVING and the long list of scalar functions come from.

When a statement cannot be pushed, "the index is scanned up to the fetch cap and the response carries a warning." The cap is about 50,000 rows per index per statement. The pushdown page walks through each rule with examples.

Three statements and what the cluster saw#

Filter and sort#

SELECT name, country, founded_year
FROM organizations
WHERE country = 'DEU' AND founded_year >= 2015
ORDER BY founded_year DESC
LIMIT 50;

Both conjuncts map to filters, the statement touches one index with no aggregation, so the sort and the limit are pushed as well. FlatQL sends a POST /organizations/_search with this body and the engine has nothing left to do:

{
  "query": {
    "bool": {
      "filter": [
        { "term": { "country": "DEU" } },
        { "range": { "founded_year": { "gte": 2015 } } }
      ]
    }
  },
  "_source": ["country", "founded_year", "name"],
  "size": 50,
  "sort": [{ "founded_year": { "order": "desc", "missing": "_last" } }],
  "track_total_hits": false
}

GROUP BY as a composite aggregation#

SELECT status, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE currency = 'EUR'
GROUP BY status;

status is a keyword, both aggregates are on the allowed list and the WHERE was pushed in full, so the whole statement becomes one aggregation request with size: 0. The grouping key is a composite source named k0; COUNT(*) is read from doc_count, and SUM(amount) becomes a sum sub-aggregation. FlatQL follows after_key in pages of 1,000 buckets until the groups run out. The GROUP BY page shows the full body and the bucket cap.

A join across two indices#

SELECT o.name, s.name AS supplier, s.rating
FROM organizations o
JOIN suppliers s ON s.domain = o.primary_domain
WHERE o.country = 'FRA' AND s.rating >= 4;

The cluster cannot join, so this is a two-fetch plan. organizations is fetched with {"term": {"country": "FRA"}}. Once those rows are in, their primary_domain values are sent to the other side as a terms filter on domain next to the {"range": {"rating": {"gte": 4}}} filter, so suppliers only returns candidates that can match. The result panel notes it as "suppliers was filtered to the N join keys found in organizations." DuckDB does the join. Up to 50,000 join keys travel this way; above that the second side is scanned to the cap.

Show as DSL on every result#

Each result carries a DSL tab. It lists one request per index with the exact body sent, whether the WHERE was pushed in full, how many rows came back and how long the cluster took. Statements that reached the engine also show the SQL as the engine executed it. This is what makes the hybrid model inspectable: when a query is slow, the tab tells you whether the cluster or the engine spent the time, and whether a different WHERE would have been pushed. Show as DSL explains how to read it, and EXPLAIN <select> shows the plan without running anything.

If you would rather hand the statement to the cluster's own SQL endpoint, Cluster SQL mode sends it to x-pack SQL or the OpenSearch SQL plugin as-is. Useful when you want to compare dialects or when a cluster-side feature is exactly what you need.

What else works#

Full-text search is a function call: MATCH(description, 'battery storage') becomes a match query, with a phrase form and a multi-field SEARCH(...). See MATCH. Nested arrays like orders.items flatten with LATERAL FLATTEN, covered in nested fields. IFF, NVL, DATEDIFF, DATE_TRUNC, SPLIT_PART and TRY_CAST behave the way Snowflake users expect. SHOW TABLES and DESCRIBE orders answer the first two questions anyone asks of a new cluster, and type mapping explains how Elasticsearch types appear in the grid.

Without FlatQL#

Query DSL by hand. The filter-and-sort example above is a bool query with two filter clauses, a sort array and a size. That is manageable. The grouped example is a composite aggregation with a sources array, a sub-aggregation for the sum, and a loop over after_key because composite buckets page. The join has no DSL form at all: you run two searches and merge in a script. Query DSL is precise and expressive, and it is the right tool for relevance tuning; it is simply verbose for the analytical questions people ask every day.

x-pack SQL and the OpenSearch SQL plugin. Both give you a SELECT over a single index with WHERE, GROUP BY and ORDER BY, and both can translate SQL to DSL. The engine's docstring sums up why FlatQL does not stop there: the clusters' own SQL endpoints "differ in dialect and refuse joins, subqueries, window functions and most scalar functions." The comparisons with x-pack SQL and the OpenSearch SQL plugin list what each dialect accepts, and Elasticsearch SQL limitations goes through the gaps one by one.

Export and query elsewhere. Dumping an index to CSV and loading it into a database gives you full SQL at the cost of a stale copy and a pipeline to maintain. FlatQL's engine does the same thing per statement, on a filtered fetch, with no copy left behind.

Start with one cluster#

The Free plan includes the full SQL engine and Show as DSL for one cluster, with no card and no expiry; Pro is a one-time $199 licence for unlimited clusters, exports and full history, listed on the pricing page. Create an account, connect a cluster with your existing credentials, and run your first SELECT within a minute.