SQL functions for Elasticsearch and OpenSearch

FlatQL parses Snowflake-style SQL and runs the parts the cluster cannot in an embedded engine. This page lists the functions by category and says which ones keep a statement off the fast path.

Where a function runs#

Every statement is split in two. The cluster gets what translates exactly to Query DSL; the engine gets the rest. The rules from the engine source:

WHERE: a conjunct is pushed only when it maps 1:1 to a filter (column vs literal on a field we can filter exactly). The full WHERE still runs in DuckDB; pushing just shrinks the fetch. GROUP BY: pushed as a composite aggregation when every grouping key is a plain groupable field, every aggregate is COUNT / SUM / AVG / MIN / MAX (plus approximate COUNT DISTINCT) on a plain field, and the WHERE was pushed in full.

So a function wrapped around a column in WHERE (DATE_TRUNC('day', placed_at) = '2026-11-01') is not a column-vs-literal comparison and is not pushed. The same function in SELECT costs nothing extra. Three exceptions are pushed on purpose: UPPER(col) = 'X' and LOWER(col) LIKE 'x%' become case-insensitive term and wildcard queries on keyword fields, and MATCH / SEARCH become match queries (see full-text MATCH). The full set of pushdown rules has the details.

Conditional#

IFF, NVL, COALESCE, DECODE, CASE. All run in the engine; none affect the fetch when used in SELECT.

SELECT name,
       IFF(rating >= 4, 'preferred', 'standard') AS tier,
       NVL(category, 'uncategorised') AS category,
       DECODE(country, 'GBR', 'United Kingdom', 'DEU', 'Germany', country) AS country_name
FROM suppliers
WHERE country IN ('GBR', 'DEU')
LIMIT 50;

The IN list and the LIMIT go to the cluster as a terms filter and size.

Dates#

DATE_TRUNC, DATEDIFF, DATEADD, TIME_SLICE, TO_VARCHAR(date, 'YYYY-MM-DD'), CURRENT_DATE, plus YEAR, MONTH, DAYNAME, LAST_DAY and the other Snowflake date parts. Snowflake format strings (YYYY, MM, DD, HH24, MI) are translated for you. Date fields stored as epoch milliseconds or ISO strings are converted before the function sees them.

SELECT DATE_TRUNC('month', placed_at) AS month,
       COUNT(*) AS orders,
       SUM(amount) AS revenue,
       TO_VARCHAR(MIN(placed_at), 'YYYY-MM-DD') AS first_order
FROM orders
WHERE placed_at >= '2026-05-01' AND status = 'paid'
GROUP BY 1
ORDER BY 1;

The WHERE is a range and a term, so it is pushed. The grouping key is a function, so the rows are scanned (up to the 50,000-row cap) and grouped in the engine. Compare WHERE placed_at >= DATEADD('month', -6, CURRENT_DATE): the right-hand side is not a literal, so that conjunct stays in the engine and the scan covers the whole index. Write the date out when the index is large.

Strings#

SPLIT_PART, REGEXP_REPLACE, ILIKE, CONTAINS, STARTSWITH, ENDSWITH, LISTAGG, UPPER, LOWER, TRIM, SUBSTR, INSERT, HEX_ENCODE.

SELECT SPLIT_PART(primary_domain, '.', -1) AS tld,
       COUNT(*) AS orgs,
       LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS examples
FROM organizations
WHERE primary_domain ILIKE '%.co.%'
GROUP BY 1
ORDER BY orgs DESC
LIMIT 20;

ILIKE on a keyword field is pushed as a wildcard query with case_insensitive: true; CONTAINS, STARTSWITH and ENDSWITH on keyword fields become wildcards too. On a text field they need a .keyword sub-field, which FlatQL picks for you. REGEXP_REPLACE and SPLIT_PART run in the engine.

Casts#

TRY_CAST(x AS type), x::type, TRY_TO_NUMBER, TO_VARCHAR, and the Snowflake AS_INTEGER / AS_VARCHAR / IS_INTEGER / IS_ARRAY family for JSON values.

SELECT order_id,
       TRY_CAST(amount AS INTEGER) AS whole_units,
       placed_at::date AS day
FROM orders
WHERE currency = 'EUR'
LIMIT 100;

Use TRY_CAST when some documents hold a value that will not convert; it returns NULL instead of failing the statement.

Aggregates#

COUNT, COUNT(DISTINCT), APPROX_COUNT_DISTINCT / HLL, SUM, AVG, MIN, MAX, MEDIAN, APPROX_PERCENTILE, APPROX_TOP_K, PERCENTILE_CONT, LISTAGG, ARRAY_AGG, OBJECT_AGG.

SELECT service,
       COUNT(*) AS requests,
       AVG(duration_ms) AS avg_ms,
       APPROX_PERCENTILE(duration_ms, 0.95) AS p95_ms
FROM "logs-*"
WHERE level = 'ERROR' AND "@timestamp" >= '2026-11-01'
GROUP BY service
ORDER BY requests DESC;

Only COUNT, SUM, AVG, MIN, MAX and COUNT(DISTINCT) are run by the cluster as a composite aggregation. COUNT(DISTINCT) becomes a cardinality aggregation and is approximate (about 1 percent off at large counts; the result carries a note). Any other aggregate in the list, APPROX_PERCENTILE above included, sends the whole statement down the scan path. Drop the percentile and the same statement becomes one aggregation request.

Arrays and JSON#

col:field, col['field'], col:a.b, ARRAY_SIZE, ARRAY_CONTAINS, ARRAY_POSITION, ARRAYS_OVERLAP, OBJECT_CONSTRUCT, OBJECT_PICK, OBJECT_DELETE, PARSE_JSON, TO_VARIANT, LATERAL FLATTEN(input => col).

SELECT order_id,
       ARRAY_SIZE(items) AS line_count,
       f.value:sku::varchar AS sku,
       f.value:qty::integer AS qty
FROM orders, LATERAL FLATTEN(input => items) f
WHERE status = 'paid'
LIMIT 100;

Object and nested fields arrive as JSON columns. A field that holds a scalar in one document and an array in another is treated as a list of one. See nested fields for f.index, f.this and aggregation over flattened rows.

Meta statements#

SHOW TABLES;
DESCRIBE orders;
EXPLAIN SELECT country, COUNT(*) FROM organizations GROUP BY country;

SHOW TABLES lists indices (hidden . indices are skipped). DESCRIBE lists fields and mapping types from _field_caps, without .keyword sub-fields. EXPLAIN returns the plan without running it: one row per index with the Query DSL that would be sent, one row for the engine with the translated SQL, and one row per note. It is the quickest way to check whether a function changed the fetch.

Functions that stop pushdown#

Also translated from Snowflake spelling: WIDTH_BUCKET, RATIO_TO_REPORT, CONDITIONAL_TRUE_EVENT, EPOCH_SECOND, UUID_STRING, SYSDATE, CHECK_JSON, GET_IGNORE_CASE, STRIP_NULL_VALUE, SPLIT_TO_TABLE. If you need something outside this list, Ask FlatQL will tell you whether the engine has it. For the full picture of what SQL on Elasticsearch looks like, start with querying Elasticsearch with SQL.