Your first SQL query on Elasticsearch

A worksheet is a text file of statements. Put the cursor on one, press Run, and the result appears underneath with a line that says what the cluster did and what the engine did.

Open a worksheet#

Pick a connection, open Workspaces and press New worksheet. Worksheets open as tabs next to Home; Home lists every worksheet grouped by project. Double-click an active tab to rename it, middle-click to close it. From an index page, New worksheet pre-fills the sheet for that index with a SELECT * ... LIMIT 100 and a matching GET /<index>/_search request so you have both statement kinds in front of you.

Worksheets save on their own. The status in the top right reads Saved, Saving… or Unsaved changes; ⌘S (Ctrl+S on Windows and Linux) saves now.

Write the first statement#

Type this against the organizations index:

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

Tables are index names. Columns are mapping fields. Write country, never country.keyword; FlatQL picks the keyword sub-field for equality, grouping and sorting on its own (see keyword vs text). Quote names that contain - or ., such as "logs-2026.11".

Statements end at a ; or a blank line. A worksheet can also hold raw Elasticsearch requests, written as a method and path line followed by a JSON body:

GET /organizations/_search
{"size": 10, "query": {"term": {"country": "DEU"}}}

Both kinds run from the same editor. The SQL overview covers what the two statement kinds accept.

Run it#

Place the cursor anywhere inside the statement, or select part of the sheet, and press Run or ⌘↵. The Run menu also offers Run all statements, which executes the sheet top to bottom. Stop cancels a running statement.

The control bar has a switch between Full SQL and Cluster SQL. Full SQL is the default and runs the statement in FlatQL's engine with pushdown to the cluster. Cluster SQL hands the statement to x-pack SQL or the OpenSearch SQL plugin unchanged; see Cluster SQL mode for when that is the better choice.

Read the result#

The result pane shows the statement kind (SQL or ES), the first 80 characters of the statement, the row count and a badge with the time taken. Five views sit above the grid:

For an Elasticsearch request the Table view flattens _source and adds _id, _score and _index. The footer of an ES result shows the HTTP status and response size.

The execution line#

Under an SQL result the footer tells you how the statement ran. With the query above it reads:

Up to the requested row count is shown; raise LIMIT or download more rows.

That is the quiet case: the filter, sort and limit went to the cluster as a single search. When FlatQL had to do work itself the line changes. Examples you will meet:

GROUP BY was computed by Elasticsearch aggregations.
Part of the WHERE clause on organizations could not be sent to Elasticsearch, so up to 50,000 rows were scanned and filtered here.
organizations has 1,240,000 matching documents; only the first 50,000 were scanned. Add WHERE filters that Elasticsearch can apply (=, IN, ranges, LIKE on keyword fields) or a LIMIT to get complete results.

The second and third lines mean the result may be incomplete. The pushdown rules explain why a statement falls into that case and how to rewrite it.

Show as DSL#

Open the DSL tab to see the exact Query DSL FlatQL sent for each index, with a chip naming the mode: search for a bounded search, aggregation for a composite aggregation, or scroll (up to 50,000 rows) when the index had to be scanned. For the query above the body is:

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

Below the DSL, Statement as executed by the engine expands to the SQL that ran inside FlatQL, and open in query history jumps to the run's record. Copy the body into a GET /organizations/_search request in the same worksheet to run it natively. Show as DSL has more on reading this tab.

Copy, download, run again#

The toolbar on the right copies the result as JSON, downloads it (CSV, TSV, JSON, NDJSON, Excel, Parquet on paid plans; see Export), maximises the grid, and re-runs the last statement. Ask FlatQL opens the assistant beside the sheet; it drafts SQL or a request from a question, and nothing runs until you press Run.

Next: learn the keyboard shortcuts, then read how FlatQL decides what runs on the cluster. For an overview of querying Elasticsearch with SQL, see query Elasticsearch with SQL.