FlatQL vs OpenSearch SQL: the plugin, Query Workbench and where joins stop

OpenSearch gives you two query languages besides Query DSL. The docs introduce SQL for "users with SQL experience who want to query OpenSearch data using familiar relational database syntax" and PPL, the Piped Processing Language, for log and trace analysis with a | between steps (SQL and PPL). Both come from the opensearch-sql plugin, which is bundled "with all OpenSearch distributions except for the minimal distribution" (plugins) and is Apache-2.0 licensed (GitHub). Query Workbench is the editor for it inside OpenSearch Dashboards. FlatQL is a different kind of tool: a SQL workbench that lives outside the cluster, pushes what it can into the cluster as Query DSL, and runs joins, CTEs and window functions in an embedded engine. It also has a Cluster SQL mode that hands a statement straight to _plugins/_sql, so you can use both dialects from one editor.

The plugin, the Workbench and FlatQL in one table#

Where OpenSearch SQL is the better fit#

The plugin is already there. If your query is one index, a filter, a GROUP BY and a sort, POST _plugins/_sql returns rows in jdbc, csv or raw format with no account anywhere (API). Query Workbench adds an editor, a Run button, an Explain button that shows the translated request, and a download of the result. On Amazon OpenSearch Service the same workbench saves results "as text, JSON, JDBC, or CSV" (AWS).

PPL is the clearer win. There is no PPL in FlatQL. For log triage where each step is a filter, a stats and a sort, the piped form reads well and runs on the cluster.

Drivers are the other reason. The JDBC driver "lets you integrate OpenSearch with your favorite business intelligence (BI) applications" (JDBC), and the ODBC driver is "a read-only ODBC driver for Windows and macOS" for Excel, Power BI and Tableau (ODBC). Neither has a licence tier to clear.

Where FlatQL is the better fit#

The plugin's own limitations page is specific about joins: "JOIN queries are supported on a best-effort basis", they do "not support aggregations on the joined result", they end automatically after 60 seconds, and under the V2 engine "JOINs and sub-queries are not supported" (limitations). The complex-queries page adds the grammar rules: two indexes only, aliases required, AND-only ON, and "You can't use GROUP BY or ORDER BY for results" (complex queries).

That last rule is the one that bites. A join whose result you cannot group or sort is a join you then post-process somewhere else. FlatQL does the post-processing in place:

SELECT s.category, l.region,
       COUNT(*) AS errors,
       AVG(l.duration_ms) AS avg_ms,
       RANK() OVER (PARTITION BY s.category ORDER BY COUNT(*) DESC) AS rank_in_category
FROM "logs-*" AS l
JOIN suppliers AS s ON s.domain = l.service
WHERE l.level = 'ERROR' AND l."@timestamp" >= '2026-10-01'
GROUP BY s.category, l.region
QUALIFY rank_in_category <= 3
ORDER BY s.category, errors DESC;

The logs-* filter on level and @timestamp is pushed to the cluster as Query DSL. The join, the grouping over joined columns, the RANK() and the QUALIFY run in FlatQL's engine. Show as DSL displays the two requests that went to the cluster. The engine works on at most about 50,000 rows per index per statement, which is why the date filter is there; the pushdown rules list exactly which predicates qualify.

Pagination is the second difference. The plugin's cursor "is supported by the V1 engine only", needs the jdbc format, and "The query with aggregation and join does not support pagination for now" (limitations). FlatQL streams any result, grouped or joined, to CSV, TSV, JSON, NDJSON, Excel or Parquet on disk, up to 5,000,000 rows.

Window functions, CTEs and QUALIFY are the third; the window functions guide covers ROW_NUMBER, LAG and RANK with the pushdown each allows. Add a query history with user, connection and duration, Ask FlatQL to draft a statement from the mappings, and team roles that grant read or write per index pattern without a single OpenSearch security role being created.

Using both#

Keep the plugin for PPL, for BI drivers, and for anything you script against the endpoint. Keep Query Workbench for a quick Explain inside Dashboards. Open FlatQL when the statement needs a join you can group, a window function, or an export bigger than size_limit.

Cluster SQL mode ties them together. It sends your statement to _plugins/_sql on OpenSearch, or to _opendistro/_sql on legacy Amazon Elasticsearch domains, and shows the plugin's own result and error messages. When the plugin says no, switch the mode off and the same worksheet runs in the engine. How the two modes differ is in Cluster SQL mode.

Elastic's x-pack SQL has a different dialect and a stricter single-table rule; the Elasticsearch SQL comparison covers it. For the Dashboards-versus-workbench question in general, read FlatQL vs Kibana, whose points apply to OpenSearch Dashboards too. The Free plan includes one cluster and the full engine; see pricing.