Elasticsearch field types in SQL
FlatQL reads the mapping with _field_caps and decides per field whether a filter, GROUP BY or ORDER BY can run on the cluster. This table says what each type gets.
The table#
"SQL type" is the type label shown on the result column. "On the cluster" means the operation is translated to Query DSL under the pushdown rules; otherwise it still works, in the engine, on fetched rows.
| Elasticsearch type | SQL type | Filter on cluster | GROUP BY on cluster | ORDER BY on cluster |
|---|---|---|---|---|
keyword, constant_keyword, wildcard |
keyword | yes: =, !=, IN, </>, BETWEEN, LIKE, ILIKE, CONTAINS, IS NULL |
yes | yes |
text, match_only_text, search_as_you_type |
keyword | MATCH / SEARCH always; = as match_phrase; other operators only through a .keyword sub-field |
only through .keyword |
only through .keyword |
long, integer, short, byte, unsigned_long |
long | yes, with numeric literals | yes | yes |
double, float, half_float, scaled_float |
double | yes, with numeric literals | yes | yes |
boolean |
boolean | yes: =, IS TRUE, IS FALSE |
yes | yes |
date, date_nanos |
date | yes, with ISO string literals | yes | yes |
ip |
keyword | yes | yes | yes |
version |
keyword | yes | yes | yes |
object |
object (JSON) | not on the root; each child path by its own type | not on the root | not on the root |
nested |
object (JSON) | see note below | no | no |
flattened |
object (JSON) | no | no | no |
geo_point, geo_shape, binary, range types, others |
by value: keyword or object | no | no | no |
_id |
keyword | =, !=, IN as an ids query |
no | no |
What the result column types mean#
The engine works in DuckDB types and maps them back to familiar labels when it returns columns: BIGINT becomes long, DOUBLE becomes double, VARCHAR becomes keyword, BOOLEAN stays boolean, TIMESTAMP becomes date, JSON becomes object. A computed column such as COUNT(*) is long; AVG(x) is double.
Keyword and text#
The keyword versus text distinction decides more than anything else. On a keyword field the whole value is indexed as one term, so equality, lists, ranges and wildcards are exact and cheap. On a text field the value is analysed into words; only MATCH and SEARCH use that index directly.
FlatQL fills the gap with the .keyword sub-field when the mapping has one: country = 'GBR', GROUP BY country and ORDER BY country on a text country field are rewritten to country.keyword in the request. You never write the sub-field in SQL; if you do, it is stripped. Without a sub-field, = on a text field becomes a match_phrase query (which matches documents containing the phrase, not equal to it), and grouping or sorting happens in the engine.
SELECT country, status, COUNT(*) AS orgs
FROM organizations
WHERE status IN ('active', 'acquired') AND founded_year >= 2000
GROUP BY country, status
ORDER BY orgs DESC;
country and status are keyword, founded_year is integer: one aggregation request, no scan. The GROUP BY on Elasticsearch page shows the request and the alternative in raw DSL.
Numbers#
Numeric fields take numeric literals. Two comparisons are deliberately kept in the engine because the cluster would answer differently from SQL:
keyword_field > 50compares as text in arangequery but as a number in SQL, so it is not pushed.long_field > '50'with a string literal is not pushed either.
Values that arrive as numeric strings in _source (a long field fed "42") are converted on load; strings with leading zeros and anything in a field whose name ends in id, code, zip, phone and similar stay text so identifiers keep their form.
Dates#
A date field compares with an ISO string: placed_at >= '2026-10-01' becomes a range filter. In the engine the column is a TIMESTAMP in UTC; epoch-millisecond values in _source are converted, and a custom date format that will not parse is kept as raw text rather than failing. DATE_TRUNC, DATEDIFF and the other date functions accept the column directly. A date field holding epoch seconds in a numeric field also works: FlatQL detects the magnitude and converts.
Booleans and NULLs#
active = true, active IS TRUE and active IS FALSE are pushed as term queries. IS NULL becomes must_not exists. != and NOT (...) are pushed with an added exists filter, because SQL drops rows where the comparison is NULL while a plain must_not would keep documents missing the field. This is spelled out in the engine source:
SQL NOT over NULL yields NULL (row dropped); ES must_not keeps docs missing the field.
Objects, nested and flattened#
The root of an object field is a JSON column; its children are regular fields with their own types, so address.city = 'Berlin' is a pushed term filter when address.city is keyword. Nested fields and flattened fields also arrive as JSON. Use col:key to read a value and LATERAL FLATTEN to expand arrays; the nested fields page covers both.
Unknown fields#
A field that is not in _field_caps, for example one created by dynamic mapping that the cached capabilities have not picked up yet (the cache lasts five minutes), is tried as written: equality and ranges are pushed, LIKE is pushed as a wildcard, but a case-insensitive UPPER(col) = 'X' is not, since FlatQL cannot confirm the field is keyword. If a statement behaves unexpectedly right after a mapping change, run DESCRIBE index and try again a few minutes later.