FlatQL vs Elasticsearch SQL: what x-pack SQL leaves out

Elasticsearch SQL, often called x-pack SQL after the module it lives in, is Elastic's own answer to "can I just write SQL". The docs describe it as "a feature that allows SQL-like queries to be executed in real-time against Elasticsearch", exposed through a REST API, a translate API, a command-line client, and JDBC and ODBC drivers (overview). It runs inside the cluster and needs nothing installed. FlatQL is a SQL workbench that sits outside the cluster. It speaks to the same HTTP API with your credentials, shows every statement's Query DSL, and adds the pieces of SQL that the built-in layer does not have: joins across indices, CTEs, window functions and warehouse-style functions. The two are not rivals so much as two layers, and FlatQL can drive x-pack SQL directly when that is what you want.

What the docs say, and what FlatQL does with it#

Where Elasticsearch SQL is the better fit#

Pick x-pack SQL when the consumer is a program, not a person. A JDBC driver is a JDBC driver: Elastic ships x-pack-sql-jdbc on Maven Central as "a platform independent, stand-alone, Direct to Database, pure Java driver" (JDBC docs), and the ODBC driver plugs Windows BI tools into the cluster once "the server must have Elasticsearch SQL installed and running with the valid license" (ODBC docs). Both need Platinum or above. If you have that licence and a BI tool that expects a driver, use it.

Pick it also when the query fits one index and you want nothing between you and the cluster. A SELECT with a WHERE, a GROUP BY on keyword fields and an ORDER BY on the bucket key translates cleanly, and the translate API shows the Query DSL it became. Scripted pipelines that curl -XPOST /_sql?format=csv work without any account anywhere.

And pick it for the CLI. ./bin/elasticsearch-sql-cli ships with Elasticsearch and runs as "a stand alone Java application" (CLI docs). On a bastion host with no browser, that is the tool.

Where FlatQL is the better fit#

The limitations page is the honest guide here, and FlatQL exists for the lines on it. Three show up constantly.

One table. Every join a warehouse user reaches for is out of scope. FlatQL fetches each side with its own pushed-down filter, sends the join keys of the smaller side as a terms filter to the larger, and joins in DuckDB. The rule set is in the joins guide; the short version is that the engine sees at most about 50,000 rows per index per statement, so filter first.

WITH recent AS (
  SELECT customer_id, amount, placed_at,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS rn
  FROM orders
  WHERE placed_at >= '2026-09-01' AND status = 'paid'
)
SELECT s.name AS supplier, s.country, COUNT(*) AS customers, SUM(r.amount) AS latest_total
FROM recent AS r
JOIN suppliers AS s ON s.domain = r.customer_id
WHERE r.rn = 1
GROUP BY s.name, s.country
ORDER BY latest_total DESC
LIMIT 20;

x-pack SQL rejects this on the CTE, the window function and the join. In FlatQL the orders filter is pushed as a Query DSL bool filter, the rest runs in the engine, and the result panel labels which was which.

Sub-selects and sorting. "Using sub-selects (SELECT X FROM (SELECT Y)) is supported to a small degree", and sorting an aggregation by anything other than its bucket key falls back to a client-side sort capped at 65,535 rows (limitations). FlatQL's engine has no such split: ORDER BY revenue DESC on a grouped result is an ordinary sort.

Nested and array fields. The docs are direct: nested fields "cannot be used in a query", only one nested field may appear per statement, and "Array fields are not supported". FlatQL flattens orders.items with LATERAL FLATTEN and reads JSON with col:field; see nested fields.

Beyond the grammar, FlatQL adds what a workbench adds: a grid with charts and pivots, exports that stream to disk, Ask FlatQL to draft or explain a statement from the mappings, and a history of every statement with user, connection and duration. Teams get per-index read or write roles enforced by FlatQL on every request, without creating cluster users.

Using both#

Cluster SQL mode is the bridge. Flip it on and FlatQL posts your statement to /_sql exactly as written, then calls /_sql/translate so Show as DSL still displays the Query DSL the cluster built. Use it to confirm that a statement destined for a JDBC pipeline parses the way Elasticsearch expects, or to compare the cluster's plan with FlatQL's. Flip it off and the same worksheet gains joins and window functions. The mode is explained in Cluster SQL mode, and a worked list of what the built-in layer refuses is on the Elasticsearch SQL limitations page.

The same split applies to OpenSearch, whose plugin has a different dialect and a different set of join rules; see FlatQL vs OpenSearch SQL. If your team already lives in Kibana, the Kibana comparison covers how FlatQL fits beside it. Plans, including the Free tier with the full engine, are on the pricing page.