# FlatQL

> FlatQL is a SQL workbench for Elasticsearch and OpenSearch. You write Snowflake-style SQL (joins, CTEs, window functions, QUALIFY, scalar functions, JSON access); FlatQL pushes filters, sorts, limits and GROUP BY aggregations down to the cluster as Query DSL and runs the rest in an embedded DuckDB engine. Results show as a grid, chart or pivot and stream to CSV, TSV, JSON, Parquet or Excel. Teams get shared connections with per-index read/write access, roles and an audit log, all enforced in FlatQL rather than on the cluster.

## Product

- [Home](https://flatql.com/): what FlatQL does and who it is for
- [Pricing](https://flatql.com/pricing): Free (1 cluster, 1 worksheet, 1-day history, no export, no data loading), no card and no expiry. Paid options (unlimited clusters, saved queries, exports, data loading, full history) are offered inside the app after sign-in; there is no subscription.
- [Desktop app](https://flatql.com/#desktop): macOS app (Apple silicon), coming soon
- [Contact](https://flatql.com/contact): questions and quotes

## How it works

- Two ways in: the web app at flatql.com/app, or the desktop app (macOS coming soon; Windows and Linux after). The desktop app connects to clusters straight from the user's computer, so it reaches localhost, VPNs and private networks, and keeps cluster credentials on the machine, encrypted with a key in the OS keychain. A connection can optionally be synced to the user's FlatQL account to use it on the web too. Same account, plan, team and access rules in both.
- Connect with a URL plus basic auth or API key, an Elastic Cloud ID, or an OpenSearch / Amazon OpenSearch Service domain. Credentials used on the web are encrypted at rest; team members never see them.
- Worksheets hold SQL statements and raw Elasticsearch requests side by side. Run one statement or all, with autocomplete from the index mappings.
- The SQL engine discovers whether the cluster offers x-pack SQL, the OpenSearch SQL plugin or Open Distro SQL and can also use those directly ("Cluster SQL").
- Exports stream row by row from the cluster to the file, so downloads of millions of rows do not hold data in memory.
- Ask FlatQL: describe the question in plain words and the assistant writes the SQL or the Elasticsearch request, using the schema of the indices the user may see. Free includes 20 questions a day; paid plans have no displayed limit, with a fair-use cap that teams can ask to raise.
- SQL coverage: joins and ASOF JOIN, CTEs and recursive CTEs, CONNECT BY, window functions and QUALIFY, PIVOT / UNPIVOT, ROLLUP / CUBE / GROUPING SETS, SAMPLE, full-text MATCH / SEARCH, FLATTEN for arrays, JSON access with col:field, approximate COUNT DISTINCT / TOP_K / PERCENTILE, SHOW TABLES, DESCRIBE, EXPLAIN. Not available: MATCH_RECOGNIZE and Time Travel.

## Compatibility

Elasticsearch 6 through 9, OpenSearch 1 and 2, Elastic Cloud, Amazon OpenSearch Service, self-hosted clusters.

## Policies

- [Privacy](https://flatql.com/privacy)
- [Terms](https://flatql.com/terms)

## Documentation

- [Documentation index](https://flatql.com/docs/): Connect a cluster, learn what runs where, and find every function, limit and setting.
- [Ask FlatQL - AI that writes Elasticsearch SQL and Query DSL](https://flatql.com/docs/ask-flatql): Ask FlatQL turns an AI Elasticsearch query request into SQL or Query DSL from your real mappings and a sample document: what it sees, modes, fixes, limits.
- [Changelog](https://flatql.com/docs/changelog): What changed in FlatQL, newest first: web app 0.4.2 and desktop app 0.4.7, covering the SQL engine, Ask FlatQL, billing, teams and data loading.
- [Cluster SQL mode with x-pack SQL and the OpenSearch SQL plugin](https://flatql.com/docs/cluster-sql-mode): Cluster SQL sends a FlatQL statement to x-pack SQL, the OpenSearch SQL plugin or Open Distro SQL unchanged: how detection works, what differs, when to use it.
- [Connect an Elasticsearch or OpenSearch cluster](https://flatql.com/docs/connect-a-cluster): Connect Elasticsearch or OpenSearch to FlatQL with a URL and API key, basic auth, an Elastic Cloud ID or an Amazon OpenSearch domain, then set it read-only.
- [CTEs, UNION, HAVING and subqueries on Elasticsearch](https://flatql.com/docs/ctes-union-having): WITH and recursive CTEs, UNION, EXCEPT, INTERSECT, HAVING, subqueries, CONNECT BY, ROLLUP, CUBE and PIVOT on Elasticsearch indices with FlatQL's SQL engine.
- [FlatQL desktop app for macOS, Windows and Linux](https://flatql.com/docs/desktop-app): The FlatQL desktop app runs the SQL engine on your machine, reaches localhost and VPN clusters, and keeps credentials in the OS keychain behind a PIN.
- [Export Elasticsearch results to CSV, Excel and JSON](https://flatql.com/docs/export): Stream a SQL result from Elasticsearch or OpenSearch straight to CSV, TSV, JSON or Excel. Up to 5,000,000 rows, written to disk as they arrive, on the Pro plan.
- [FlatQL FAQ: SQL on Elasticsearch, answered](https://flatql.com/docs/faq): Short answers on SQL for Elasticsearch and OpenSearch with FlatQL: joins, pushdown, versions, exports, teams, security, the desktop app and the Pro licence.
- [Your first SQL query on Elasticsearch](https://flatql.com/docs/first-query): Open a worksheet, run a first SQL statement against an Elasticsearch index, read the Table, Chart, Pivot, JSON and DSL tabs, and see what the cluster executed.
- [SQL functions for Elasticsearch and OpenSearch](https://flatql.com/docs/functions): Elasticsearch SQL functions in FlatQL by category: IFF, NVL, DATE_TRUNC, DATEDIFF, SPLIT_PART, TRY_CAST, LISTAGG, APPROX_TOP_K, JSON access and FLATTEN.
- [Index patterns like logs-* as SQL tables](https://flatql.com/docs/index-patterns): Query an Elasticsearch index pattern such as logs-* as one SQL table in FlatQL: quoting names with dashes or dots, aliases, and what mixed mappings do.
- [Elasticsearch SQL joins across indices](https://flatql.com/docs/joins): Join two Elasticsearch indices with plain SQL: inner, left, right, full, cross and ASOF joins, and how join keys reach the other index as a terms filter.
- [Keyboard shortcuts in FlatQL](https://flatql.com/docs/keyboard-shortcuts): Every keyboard shortcut in the FlatQL worksheet and app shell: run the statement under the cursor with Cmd+Enter, save, comment, autocomplete and the palette.
- [Limits, caps and unsupported SQL](https://flatql.com/docs/limits): Elasticsearch SQL limitations in FlatQL: the 50,000-row fetch cap per index, bucket and join-key caps, 5,000,000-row exports, unsupported SQL and plan limits.
- [Load CSV, Excel, JSON and API data into Elasticsearch](https://flatql.com/docs/load-data): Import a CSV, Excel, JSON or NDJSON file, or a paginated JSON API with header auth, into an Elasticsearch or OpenSearch index. Preview, then load.
- [Full-text search in SQL with MATCH and SEARCH](https://flatql.com/docs/match): Full-text search in Elasticsearch SQL: MATCH(col, 'words'), phrase mode and SEARCH across columns, the match and multi_match DSL each becomes, keyword vs text.
- [Nested fields and arrays in Elasticsearch SQL](https://flatql.com/docs/nested-fields): Elasticsearch nested fields in SQL with FlatQL: object vs nested types, col:field access, and LATERAL FLATTEN with f.value and f.index to turn arrays into rows.
- [Pricing and licensing: Free, and Pro for $199 once](https://flatql.com/docs/pricing-and-licensing): FlatQL is free for one cluster. Pro is a $199 one-time lifetime licence per user: unlimited clusters, exports, full history and Ask FlatQL. No subscription.
- [Pushdown rules: what runs on the cluster and what runs in the engine](https://flatql.com/docs/pushdown): Rules FlatQL uses to send WHERE, ORDER BY, LIMIT and GROUP BY to Elasticsearch as Query DSL, the 50,000-row fetch cap, and how to write SQL that pushes down.
- [Query history and usage for Elasticsearch](https://flatql.com/docs/query-history): FlatQL records every SQL statement with user, connection, duration and rows, groups repeated statements by fingerprint and charts usage by user or connection.
- [Result views: grid, chart, pivot, JSON and DSL](https://flatql.com/docs/results): How FlatQL shows a SQL result from Elasticsearch: a sortable grid with column tools, bar, line, area and scatter charts, a pivot, JSON and the DSL.
- [Security: what FlatQL stores and where credentials live](https://flatql.com/docs/security): What FlatQL keeps (account, connections, query history, audit log, Ask FlatQL questions), how credentials are encrypted and what stays on your machine.
- [Show as DSL - the Query DSL behind every SQL statement](https://flatql.com/docs/show-as-dsl): Show as DSL maps SQL to Elasticsearch Query DSL: the exact request per index, what was pushed down, copying it to Kibana Dev Tools or curl, finding slow steps.
- [Elasticsearch SQL syntax supported by FlatQL](https://flatql.com/docs/sql-overview): The SQL dialect FlatQL runs on Elasticsearch and OpenSearch: table naming, quoting, supported clauses and functions, and the two statement kinds in a worksheet.
- [Teams, roles and per-index access for Elasticsearch](https://flatql.com/docs/teams): Share Elasticsearch connections without sharing credentials: owner, admin and member roles, read or write per index pattern, and an audit log.
- [Troubleshooting Elasticsearch connections and queries](https://flatql.com/docs/troubleshooting): Fix 401 and 403 errors, unreachable hosts, self-signed certificates, timeouts, mapping conflicts in index patterns, the 50,000-row fetch cap and failed exports.
- [Elasticsearch field types in SQL](https://flatql.com/docs/type-mapping): Elasticsearch field types in SQL: how keyword, text, long, double, date, boolean, ip, object, nested, geo_point appear in FlatQL and what the cluster filters.
- [Window functions on Elasticsearch data](https://flatql.com/docs/window-functions): Run ROW_NUMBER, RANK, LAG, LEAD and SUM OVER with frames on Elasticsearch and OpenSearch indices, filter on them with QUALIFY, and keep the fetch under the cap.

## Use cases

- [Use cases index](https://flatql.com/solutions/): One page per job, each with the SQL, the DSL it becomes, and the way you would do it without FlatQL.
- [Elasticsearch GROUP BY in SQL, run as a composite aggregation](https://flatql.com/solutions/elasticsearch-group-by-sql): Elasticsearch GROUP BY in SQL with FlatQL. Keys become a composite aggregation the cluster runs, after_key paging is automatic, HAVING runs in the engine.
- [An Elasticsearch GUI built around SQL](https://flatql.com/solutions/elasticsearch-gui): Elasticsearch GUI for engineers: browse indices and mappings, query with SQL or Query DSL, chart and export results, share a cluster without sharing passwords.
- [Elasticsearch index level access for a team, without cluster users](https://flatql.com/solutions/elasticsearch-index-level-access): Elasticsearch index level access in FlatQL. Grant read or write per index pattern per member, share a connection without sharing credentials, keep an audit log.
- [Elasticsearch query log with user, duration and the DSL that ran](https://flatql.com/solutions/elasticsearch-query-history): An Elasticsearch query log kept by FlatQL. Each statement with user, connection, duration, rows and the Query DSL it sent, grouped by shape, plus usage by user.
- [Elasticsearch SQL join across indices](https://flatql.com/solutions/elasticsearch-sql-joins): Elasticsearch SQL join, explained: why the cluster has no server-side join, how FlatQL joins two indices in plain SQL, the DSL each side gets, and the limits.
- [Elasticsearch SQL limitations, and what to do about each](https://flatql.com/solutions/elasticsearch-sql-limitations): Elasticsearch SQL limitations from the official docs: one table per FROM, limited sub-selects, no array fields, one nested field, and how FlatQL covers each.
- [Elasticsearch window functions with SQL](https://flatql.com/solutions/elasticsearch-window-functions): Elasticsearch window functions in plain SQL: ROW_NUMBER, RANK and LAG with QUALIFY over an index, what the cluster ran, and what you would build without them.
- [Export Elasticsearch to CSV, millions of rows at a time](https://flatql.com/solutions/export-elasticsearch-to-csv): Export Elasticsearch to CSV from a SQL statement: up to 5,000,000 rows streamed to disk, filters pushed to the cluster, columns you choose, plus TSV and JSON.
- [Import CSV to Elasticsearch without writing a script](https://flatql.com/solutions/load-csv-into-elasticsearch): Import CSV to Elasticsearch or OpenSearch from the browser. FlatQL reads CSV, Excel, JSON, NDJSON or a JSON API, previews the mapping, bulk-loads a new index.
- [OpenSearch GUI with full SQL](https://flatql.com/solutions/opensearch-gui): OpenSearch GUI for OpenSearch 1 and 2 and Amazon OpenSearch Service: full SQL with joins and windows, the OpenSearch SQL plugin one switch away, logs-* example.
- [Query Elasticsearch with SQL, joins and GROUP BY included](https://flatql.com/solutions/query-elasticsearch-with-sql): Query Elasticsearch with SQL in FlatQL. Filters, sorts and GROUP BY become Query DSL on the cluster; joins, CTEs and window functions run in an embedded engine.
- [AI Elasticsearch query assistant that knows your mappings](https://flatql.com/solutions/text-to-sql-elasticsearch): AI Elasticsearch query writing with Ask FlatQL. Describe the question, get SQL or Query DSL built from your live mappings and a sample document, then run it.

## Comparisons

- [Compare index](https://flatql.com/compare/): What each tool is for, what it does well, and where FlatQL differs. No scorecards.
- [FlatQL vs Cerebro for Elasticsearch](https://flatql.com/compare/flatql-vs-cerebro): FlatQL vs Cerebro: Cerebro is a cluster admin UI for nodes, shards, indices and aliases. FlatQL is a SQL query workbench for the data. Most teams run both.
- [FlatQL vs DBeaver for Elasticsearch](https://flatql.com/compare/flatql-vs-dbeaver): FlatQL vs DBeaver for Elasticsearch. DBeaver uses the Elasticsearch SQL JDBC driver and keeps its one-table limit. FlatQL adds joins, window functions, exports.
- [FlatQL vs Dejavu for Elasticsearch](https://flatql.com/compare/flatql-vs-dejavu): FlatQL vs Dejavu for Elasticsearch and OpenSearch. Dejavu browses, edits and imports documents in a web UI. FlatQL runs SQL with joins, exports and team roles.
- [FlatQL vs Elasticsearch Head: a 2010 cluster front end and a 2026 SQL workbench](https://flatql.com/compare/flatql-vs-elasticsearch-head): FlatQL vs elasticsearch-head from the repo's own README. Chrome extension, cluster overview, structured query and 5.x-era releases against a SQL workbench.
- [FlatQL vs Elasticsearch SQL: what x-pack SQL leaves out](https://flatql.com/compare/flatql-vs-elasticsearch-sql): FlatQL vs Elasticsearch SQL (x-pack SQL) compared. The REST API, CLI, JDBC and ODBC drivers and their documented limits against a workbench that adds joins.
- [FlatQL vs Elasticsearch Toolbox and elasticsearch-gui](https://flatql.com/compare/flatql-vs-elasticsearch-toolbox): FlatQL vs Elasticsearch Toolbox and elasticsearch-gui: two lightweight Elasticsearch 2.x browser tools against a SQL workbench with joins, exports and roles.
- [FlatQL vs ElasticVue: data browser or SQL workbench?](https://flatql.com/compare/flatql-vs-elasticvue): FlatQL vs ElasticVue side by side. The MIT-licensed cluster GUI with index management, REST console and snapshots against a SQL workbench with joins and export.
- [FlatQL vs Grafana for Elasticsearch](https://flatql.com/compare/flatql-vs-grafana): FlatQL vs Grafana for Elasticsearch. Grafana builds dashboards and alerts from Lucene queries and aggregations. FlatQL answers ad-hoc SQL with joins and files.
- [FlatQL vs Kibana: SQL workbench or bundled UI?](https://flatql.com/compare/flatql-vs-kibana): FlatQL vs Kibana, fact by fact. Dev Tools, Discover, ES|QL and the CSV row limit against a SQL workbench with joins, 5M-row exports and per-index access.
- [FlatQL vs OpenSearch SQL: the plugin, Query Workbench and where joins stop](https://flatql.com/compare/flatql-vs-opensearch-sql): FlatQL vs the OpenSearch SQL plugin and Query Workbench. Verified join rules, pagination, PPL, JDBC, ODBC and Amazon OpenSearch Service next to a SQL workbench.

## Alternatives

- [Alternatives index](https://flatql.com/alternatives/): Roundups by use case: what to use instead of Kibana, ElasticVue or Elasticsearch Head.
- [Elasticsearch GUI tools compared by use case](https://flatql.com/alternatives/elasticsearch-gui-tools): Ten Elasticsearch GUI tools compared by use case: SQL analysis and exports, cluster admin, document editing, dashboards, database IDEs and OpenSearch clusters.
- [Elasticsearch Head alternatives that are still maintained](https://flatql.com/alternatives/elasticsearch-head-alternatives): Elasticsearch Head alternatives, checked against each project: seven tools for cluster overview, document browsing, REST requests and SQL, plus status.
- [ElasticVue alternatives for Elasticsearch and OpenSearch](https://flatql.com/alternatives/elasticvue-alternatives): Eight ElasticVue alternatives compared on where they run, query language, exports and access control, from SQL workbenches to cluster admin consoles and Kibana.
- [Kibana alternatives for Elasticsearch and OpenSearch](https://flatql.com/alternatives/kibana-alternatives): Nine Kibana alternatives grouped by job: SQL analysis and exports, cluster administration, document browsing, dashboards, and staying inside Kibana with ES|QL.

## Blog

- [Blog index](https://flatql.com/blog/): Notes on how the engine works and why it is built this way.
- [Exporting 5 million rows from Elasticsearch without OOM](https://flatql.com/blog/export-5-million-rows-from-elasticsearch): Why from/size and buffered results fail on large Elasticsearch exports, how FlatQL streams scroll pages to a file on disk, and why the cap is 5,000,000 rows.
- [Per-index access control for Elasticsearch without creating cluster users](https://flatql.com/blog/per-index-access-control-elasticsearch): Give each engineer read or write access to specific Elasticsearch indices through one shared connection, checked before any request reaches the cluster.
- [Pushdown explained: what runs on the cluster vs the engine](https://flatql.com/blog/pushdown-explained): How FlatQL decides what Elasticsearch runs and what the embedded engine runs: the exact rules, three worked plans, and how to rewrite statements so more pushes.
- [Why Elasticsearch SQL can't do joins (and how FlatQL does)](https://flatql.com/blog/why-elasticsearch-sql-cant-do-joins): Elasticsearch SQL reads one index per statement and has no server-side join. Here is why, what the alternatives cost, and how FlatQL joins indices anyway.

## Glossary

- [Glossary](https://flatql.com/glossary): An Elasticsearch glossary for SQL users. Query DSL, composite aggregations, pushdown, QUALIFY, keyword vs text, x-pack SQL and more, each with an example.