Elasticsearch SQL limitations, and what to do about each

What x-pack SQL is#

x-pack SQL is the SQL interface shipped with Elasticsearch, reachable at POST /_sql and through JDBC and ODBC drivers. It translates one SELECT into one search request and returns the hits or the aggregation buckets as rows. That design is the source of both its speed and its limits: anything a single request cannot express is not available. Elastic documents the edges on the SQL limitations page and in the SELECT reference. The sections below quote them.

The limitations, from the official docs#

Joins. The SELECT reference is one sentence: "Currently FROM supports exactly one table." There is no JOIN keyword to reject; the grammar has no second table. Why Elasticsearch SQL can't do joins explains the shard-level reason.

Sub-selects. The limitations page says sub-selects are "supported to a small degree": those that can be flattened into a single SELECT work, while a sub-select with GROUP BY or HAVING, or an enclosing SELECT more complex than SELECT X FROM (SELECT ...) WHERE [simple_condition], is unsupported. In practice that rules out the "aggregate, then filter or rank the aggregates" shape.

Array and nested fields. "Array fields are not supported"; returning a multi-valued field throws an exception unless the field_multi_value_leniency parameter is set, and then only one value is returned. For nested types, "a query cannot reference more than one nested field in an index", scalar functions cannot be applied to nested fields in WHERE or ORDER BY, and pagination counts root documents rather than inner hits.

Sorting by an aggregate. Sorting in a composite aggregation applies to the bucket key, so an ORDER BY COUNT(*) is done client-side and capped at 65,535 rows; the docs recommend a LIMIT. The ORDER BY must also use a plain aggregate with no scalar function or operator around it.

Other items. FIRST and LAST are not allowed in HAVING, and MIN/MAX on a keyword or number column are excluded with them because they translate to FIRST/LAST internally. TIME cannot be a grouping key. PIVOT accepts one aggregation and a literal IN list. Keyword fields with a normalizer are not supported, and a query longer than xpack.sql.max_query_length (default 1,000,000 characters) is rejected before parsing. Window functions do not appear on the limitations page or in the SELECT grammar.

How FlatQL covers the gaps#

FlatQL parses the statement itself and splits it in two. Clauses that translate exactly become Query DSL: WHERE predicates on filterable fields, ORDER BY and LIMIT on single-table scans, and GROUP BY with COUNT, SUM, AVG, MIN, MAX and approximate COUNT(DISTINCT) as a composite aggregation. Everything else runs in an embedded DuckDB engine over the rows the cluster returned: joins, subqueries and CTEs, window functions and QUALIFY, UNION, HAVING on anything, PIVOT, array flattening and JSON access. The engine reads at most about 50,000 rows per index per statement, so the filters you push decide whether the result is complete; every result lists what the cluster did and what the engine did, with the DSL one tab away.

One statement that touches four rows of the table above, a CTE feeding a join, an aggregate and a ranking:

WITH recent AS (
  SELECT primary_domain, country, founded_year
  FROM organizations
  WHERE founded_year >= 2020 AND status = 'active'
)
SELECT r.country, s.category,
       COUNT(*) AS pairs,
       RANK() OVER (PARTITION BY r.country ORDER BY COUNT(*) DESC) AS rnk
FROM recent r
JOIN suppliers s ON s.domain = r.primary_domain
GROUP BY r.country, s.category
QUALIFY rnk <= 3;

Two indices are fetched. organizations has pushed filters, so it goes first as a POST /organizations/_search?scroll=2m, requesting only the four fields the CTE uses:

{
  "query": {
    "bool": {
      "filter": [
        { "range": { "founded_year": { "gte": 2020 } } },
        { "term": { "status": "active" } }
      ]
    }
  },
  "_source": ["country", "founded_year", "primary_domain", "status"],
  "size": 5000,
  "sort": ["_doc"],
  "track_total_hits": true
}

suppliers has no predicate of its own, so without help it would be scanned to the cap. Instead the distinct primary_domain values from the first fetch are sent as a terms filter on domain, and the POST /suppliers/_search?scroll=2m body is {"query": {"bool": {"filter": [{"terms": {"domain": [...]}}]}}, "_source": ["category", "domain"], "size": 5000, "sort": ["_doc"], "track_total_hits": true}. The join, GROUP BY, RANK and QUALIFY run in the engine; the notes say "suppliers was filtered to the N join keys found in organizations". The joins, window functions and nested fields pages go clause by clause, and limits lists every cap.

When Cluster SQL is still the right choice#

FlatQL keeps x-pack SQL one switch away for good reasons. Set a statement to Cluster SQL and it is sent to /_sql unchanged; FlatQL detects per cluster whether /_sql, /_plugins/_sql or /_opendistro/_sql is served and pages through the cursor for you. Prefer it when:

• You want the cluster's answer, byte for byte. An application uses x-pack SQL through JDBC and you are debugging what it sees. Running the same dialect against the same endpoint removes a variable.
• A single-index scan is bigger than the engine's cap and needs no join. x-pack SQL streams a plain SELECT through its cursor; so does a FlatQL export, but for interactive work on a few hundred thousand rows of one index with simple predicates, the native path is direct.
• You rely on its scalar functions. x-pack SQL has date, string, math and geo functions evaluated on the cluster, and a statement already written for them runs as is.
• Privileges are already arranged. The connection's user has indices:data/read/sql and you would rather not change anything.

Full SQL and Cluster SQL share the worksheet and the history, so switching back is one click. FlatQL vs Elasticsearch SQL compares them row by row, and FlatQL vs OpenSearch SQL does the same for the plugin.

Without FlatQL#

Query DSL by hand. Everything x-pack SQL cannot express, the cluster can still do in its own language, within one request. A bool query with three filters, a composite aggregation with two sources and two metrics, and paging on after_key is about thirty lines of JSON for the five lines of SQL above, and nested queries, scripts and every aggregation type are available. DSL remains the complete language of the cluster, and FlatQL lets you send it as written in the same worksheet; Show as DSL shows the translation for each SQL result, which is a fair way to learn it.

x-pack SQL plus client code. Run the single-index parts through /_sql or the JDBC driver, pull the rows into Python or Java, and do the join, the ranking and the array handling there. Reliable, and a program per question. A SQL client such as DBeaver over JDBC gives you a grid for the first half of that; FlatQL vs DBeaver explains where it stops.

Change the data. Denormalise supplier fields into organisation documents, or use an enrich processor, so that a single-index SELECT answers the question. This moves the work to index time and is the right call for a production read path that repeats the same question.

See the split on your own cluster#

The Free plan includes the full engine, Cluster SQL and Show as DSL on one cluster; the pricing page describes the one-time Pro licence. Register, connect with your credentials and run the CTE above to watch which parts the cluster took.