Export Elasticsearch to CSV, millions of rows at a time

A CSV is a SELECT with a file on the end#

Most export problems are query problems. Which rows, which columns, which date range, which status values: those decisions belong in a WHERE and a SELECT list, not in flags on a dump tool. In FlatQL the export starts from the statement in your worksheet. You run it, look at the grid, and when it is right you choose Download and a format. Larger downloads re-run the statement and stream rows into the file as the cluster returns them.

Three things follow from that design:

• Filters are pushed to the cluster. A conjunct that compares a field with a literal becomes a term, terms or range filter, so the cluster only ships the rows you asked for. Rules on the pushdown page.
• Columns are chosen by you. Only the fields in the SELECT list are requested through _source, which keeps the file and the transfer small.
• Rows stream. A plain projection over one index, with the whole WHERE pushed, streams straight from a scroll over the index with no cap other than the 5,000,000-row ceiling. Nothing accumulates on the server or in the browser.

The statement that shapes the file#

Paid and shipped euro orders from the first half of 2026, six columns:

SELECT order_id, customer_id, amount, currency, status, placed_at
FROM orders
WHERE placed_at >= '2026-01-01'
  AND placed_at <  '2026-07-01'
  AND status IN ('paid', 'shipped')
  AND currency = 'EUR';

Every conjunct maps to a filter and every selected column is a plain field, so this is a direct projection. The export opens a POST /orders/_search?scroll=3m and then follows /_search/scroll page after page, 5,000 rows at a time, writing each page before fetching the next:

{
  "query": {
    "bool": {
      "filter": [
        { "range": { "placed_at": { "gte": "2026-01-01" } } },
        { "range": { "placed_at": { "lt": "2026-07-01" } } },
        { "terms": { "status": ["paid", "shipped"] } },
        { "term": { "currency": "EUR" } }
      ]
    }
  },
  "_source": ["amount", "currency", "customer_id", "order_id", "placed_at", "status"],
  "size": 5000,
  "sort": ["_doc"]
}

Two range clauses rather than one is deliberate: each conjunct is translated on its own. BETWEEN produces a single range with gte and lte. Nested fields such as items are not in the list here; if you select items:sku through JSON access or flatten the array with LATERAL FLATTEN, the statement is no longer a plain projection and runs through the engine, which is covered next. See nested fields.

Formats and where each one has a limit#

The "CSV for Excel" variant exists because Excel guesses the encoding of a plain CSV; a byte-order mark and Windows line ends make it open with accented names intact. Cells that hold objects or arrays are written as JSON text.

Statements that are not plain projections#

A join, a window function, a GROUP BY with an expression key, a DISTINCT or an ORDER BY on something the cluster cannot sort all go through the embedded engine. The export still streams: the engine executes the statement and the file is written from its result cursor in pages. What changes is the input ceiling. The engine works on at most about 50,000 rows per index per statement, so an export of a join is an export of the join over those rows. The result notes say when an index was scanned to the cap. A pushed GROUP BY behaves differently again: the cluster aggregates and the export is the buckets, which rarely number more than a few thousand.

If you need millions of rows with a derived column, consider exporting the plain projection and deriving in the tool that reads the file; the limits page has the full set of caps.

Without FlatQL#

Kibana CSV reports. Discover can generate a CSV report from a saved session. The limits are set by Kibana: in the reporting settings, xpack.reporting.csv.maxRows defaults to 10,000 rows and xpack.reporting.csv.maxSizeBytes to 250 MB, and reports beyond either are truncated. The Discover documentation adds that from Kibana 9.3 the default is "10,000 rows or 250 MB by default, whichever comes first", while 9.0 to 9.2 applied the 250 MB size limit with no row limit, and that a serverless project's limits cannot be changed. Self-managed and Elastic Cloud Hosted deployments can raise the settings, with the note that higher limits can affect performance. For a few thousand rows out of a dashboard, it is the right tool. FlatQL vs Kibana covers the rest of the comparison.

A scroll or point-in-time script. Open a PIT, search with search_after and a sort on _shard_doc, write each page to a file with the csv module, keep the PIT alive, close it at the end. Forty lines of Python that you will own, including the part where a field is sometimes an array and sometimes a string.

elasticdump. A command-line tool that copies an index to a file or to another cluster. Good for backups and migrations where you want every document as JSON; the shaping into columns happens afterwards in another tool.

A SQL client over JDBC. Elasticsearch SQL's JDBC driver lets DBeaver and similar clients run single-index SELECTs and export the grid. FlatQL vs DBeaver explains where that path stops, including joins.

What Pro gives you here#

Export is a Pro feature. The licence is $199 once per user, lifetime, with no subscription and no annual plan; it also removes the one-cluster and one-worksheet limits, keeps full query history and enables charts, pivots and data loading. The Free plan has everything else on this page except the Download button, which is enough to get the statement right first. The pricing page has the detail and pricing and licensing answers the usual questions.

Start with the statement#

Register, connect the cluster, write the SELECT that describes the file you want and check the DSL tab. When the grid looks right, the download is one click, and the export page walks through the dialog.