Query DSL#
Query DSL is the JSON language Elasticsearch and OpenSearch use for search requests. A request body combines a query (a bool with filter, must, should and must_not clauses wrapping term, range, match and other leaf queries), optional sort, size, _source and aggs sections. Everything FlatQL sends to a cluster is Query DSL; the SQL is translated. The Show as DSL panel displays the exact body for every result, and the first query guide shows SQL and its DSL side by side.
{ "query": { "bool": { "filter": [ { "term": { "status": "paid" } } ] } }, "size": 50 }
See also query Elasticsearch with SQL.
Composite aggregation#
A composite aggregation is the Elasticsearch bucket aggregation that groups documents by one or more keys and pages through the groups with an after_key cursor, so a GROUP BY with many distinct values can be read in full rather than cut at a bucket limit. FlatQL turns a pushable GROUP BY into a composite aggregation with one terms source per key and missing_bucket: true, then reads buckets 1,000 at a time up to 50,000 groups. COUNT(*) comes from doc_count; SUM, AVG, MIN and MAX become sub-aggregations. The pushdown reference lists when this applies, and the group by page has examples.
Date histogram#
A date histogram is the Elasticsearch aggregation that buckets documents by a time interval on a date field (calendar_interval: "1d", fixed_interval: "15m"). It is the native way to produce a time series from @timestamp. In SQL the same shape is written with DATE_TRUNC:
SELECT DATE_TRUNC('day', placed_at) AS day, COUNT(*) AS orders
FROM orders
GROUP BY day
ORDER BY day;
DATE_TRUNC and the other date functions are listed in functions; the pushdown reference explains which groupings the cluster runs.
Pushdown#
Pushdown is the step where FlatQL sends part of a SQL statement to the cluster as Query DSL instead of running it in the embedded engine. A WHERE conjunct is pushed only when it maps 1:1 to a filter; ORDER BY and LIMIT are pushed only for single-table statements whose whole WHERE was pushed; GROUP BY is pushed as a composite aggregation under the same condition. Everything else runs in the engine, over at most 50,000 rows per index. More pushdown means fewer rows fetched and faster answers. The pushdown reference has the full rules and the post Pushdown explained walks through three plans.
QUALIFY#
QUALIFY is a SQL clause that filters on the result of a window function, the way HAVING filters on an aggregate. It saves the subquery you would otherwise need to filter on ROW_NUMBER() or RANK(). FlatQL's engine supports it directly:
SELECT customer_id, order_id, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) = 1;
That returns each customer's most recent order. Statements with QUALIFY are never pushed as a whole; the WHERE conjuncts still are. See window functions and the Elasticsearch window functions page.
Index pattern#
An index pattern is an index name with wildcards, such as logs-* or orders-2026.*, that a request expands to every matching index. Daily and monthly indices are usually queried this way. In FlatQL you write the pattern as the table name, quoted because of the hyphen:
SELECT service, COUNT(*) AS errors
FROM "logs-*"
WHERE level = 'ERROR'
GROUP BY service;
The cluster resolves the pattern and the result spans every matching index. Team grants are also expressed as patterns, and a pattern in a statement is allowed only when a grant covers it. The index patterns page covers mapping conflicts and the teams reference covers grants.
Nested field#
A nested field is an Elasticsearch object array mapped with type: nested, so each element is indexed as its own hidden document and a query can match several properties of the same element. An order's items array (sku, qty, price) is a typical case. Plain SQL has no row-per-element view of such a column; FlatQL provides one with LATERAL FLATTEN:
SELECT o.order_id, f.value:sku AS sku, f.value:qty AS qty
FROM orders o, LATERAL FLATTEN(input => o.items) f
WHERE o.status = 'paid';
Flattening runs in the engine, so filter the parent documents with pushable predicates first. See nested fields and type mapping.
Keyword vs text#
keyword and text are the two string types in an Elasticsearch mapping. A keyword field stores the value as one token for exact matching, sorting and grouping. A text field is analysed into terms for full-text search and cannot be sorted or grouped on directly. Many mappings give a text field a .keyword sub-field for both uses. In FlatQL SQL you never write .keyword; the engine chooses the sub-field for =, IN, ORDER BY and GROUP BY, and sends MATCH(col, 'words') to the text field as a match query. Equality on a text field with no sub-field is narrowed with match_phrase and finished in the engine. See type mapping and MATCH.
x-pack SQL#
x-pack SQL is Elasticsearch's own SQL endpoint, /_sql, available since 6.3 on the basic licence, with JDBC and ODBC drivers and a translate API that shows the Query DSL behind a statement. It reads exactly one table per statement, supports sub-selects only when they flatten into a single SELECT, and has no join. FlatQL's Cluster SQL mode sends statements to it natively when you want the cluster's dialect; the default FlatQL engine translates instead and adds joins, CTEs and window functions. See Cluster SQL mode, the FlatQL vs Elasticsearch SQL comparison and the limitations page.
OpenSearch SQL plugin#
The OpenSearch SQL plugin is the SQL and PPL query layer shipped with OpenSearch, served at /_plugins/_sql (and /_opendistro/_sql on Open Distro and older Amazon OpenSearch Service domains). It accepts SQL over REST with a JDBC-style response format. Its legacy engine runs joins on a best-effort basis with no aggregation over the joined result; the V2 engine does not support joins or subqueries. FlatQL detects which endpoint a cluster exposes and uses it in Cluster SQL mode, while the FlatQL engine works on any OpenSearch 1 or 2 cluster without the plugin. See Cluster SQL mode, FlatQL vs OpenSearch SQL and the OpenSearch GUI page.