Elasticsearch SQL syntax supported by FlatQL

FlatQL parses Snowflake-style SQL with sqlglot and executes it in an embedded DuckDB engine over rows pulled from the cluster. If you have written SQL for a warehouse, most of it runs unchanged.

The dialect#

Statements are parsed as Snowflake SQL, with a fallback to generic SQL when a statement does not parse that way. The parsed statement is transpiled to DuckDB SQL and executed there. Rows come from the cluster with filters, sorts, limits and GROUP BY pushed down wherever the translation is exact; the pushdown rules say when that happens.

A statement that uses nothing but pushable clauses is cheap:

SELECT country, COUNT(*) AS orgs
FROM organizations
WHERE status = 'active' AND founded_year BETWEEN 2010 AND 2020
GROUP BY country
ORDER BY orgs DESC
LIMIT 20;

The cluster runs the filter and the grouping as a composite aggregation. The engine sorts the buckets and applies the limit.

Tables, columns and quoting#

• A table is an index name, an alias, or an index pattern such as "logs-*".
• Quote names that contain - or . with double quotes: "logs-2026.11". Plain identifiers need no quotes.
• schema.index is read as the literal name schema.index, not as a schema qualifier.
• Columns are mapping fields. Never write .keyword sub-fields in SQL (country, not country.keyword); FlatQL picks the sub-field for =, IN, GROUP BY and ORDER BY on text fields. The keyword vs text distinction still matters for what the cluster can filter.
• Object paths are JSON access: col:field or col['field']. Arrays expand with LATERAL FLATTEN(input => col) f and are read as f.value and f.index; see nested fields.
• A column named @timestamp or any other name with special characters goes in double quotes.
• _id is available as a column and filters as an ids query.

Supported clauses#

Functions follow Snowflake naming: SPLIT_PART, REGEXP_REPLACE, CONTAINS, LISTAGG, DATE_TRUNC, DATEDIFF, DATEADD, TIME_SLICE, TO_VARCHAR(date, 'YYYY-MM-DD'), CURRENT_DATE, ARRAY_SIZE, ARRAY_CONTAINS, OBJECT_CONSTRUCT, PARSE_JSON, SEQ4, GENERATOR. Aggregates include COUNT, COUNT(DISTINCT), APPROX_COUNT_DISTINCT, HLL, SUM, AVG, MIN, MAX, MEDIAN, APPROX_PERCENTILE, APPROX_TOP_K and PERCENTILE_CONT. The functions reference lists them with examples.

Not available: MATCH_RECOGNIZE, Time Travel (AT / BEFORE), stored procedures, and INSERT / UPDATE / DELETE. A Time Travel clause returns a clear error: Elasticsearch keeps only the current version of a document.

Full-text search#

Text fields are searched with MATCH, which becomes an Elasticsearch match query on the cluster:

SELECT name, country
FROM organizations
WHERE MATCH(description, 'battery recycling') AND country = 'DEU'
LIMIT 50;

MATCH(col, 'exact phrase', 'phrase') sends a match_phrase. SEARCH((description, name), 'battery recycling', SEARCH_MODE => 'AND') searches several fields at once with every word required. Details are on the MATCH page.

Two statement kinds in one worksheet#

A worksheet mixes SQL with raw Elasticsearch requests. An Elasticsearch request is a line of METHOD /path followed by an optional JSON body, ended by a blank line or the next request:

GET /organizations/_search
{
  "size": 0,
  "aggs": {"by_country": {"terms": {"field": "country", "size": 20}}}
}

SQL statements end at a ; or a blank line. Lines starting with --, // or # are comments. Use a request for anything SQL does not express: _mapping, _cat, nested queries, scripts, update_by_query, or an aggregation you want the cluster to run exactly as written. In Query DSL you do write field.keyword where a text field has one.

Two execution modes#

Full SQL (default) is everything on this page. Cluster SQL sends the statement to x-pack SQL or the OpenSearch SQL plugin instead. If the cluster's parser rejects a statement in Cluster SQL mode, FlatQL runs it with Full SQL and says so in the execution line. See Cluster SQL mode.

Where the engine does the work#

The engine holds at most about 50,000 rows per index per statement. Statements that filter on keyword, numeric, date and boolean fields, and that group on plain fields with COUNT, SUM, AVG, MIN or MAX, finish on the cluster. Joins, windows, CTEs, set operations and expressions on columns run in the engine over whatever rows the pushable part of the statement selected. The pushdown page has the exact rules; joins, window functions and CTEs, UNION and HAVING have worked examples. For why a SQL layer is needed at all, see query Elasticsearch with SQL.