NOTE: You are looking at documentation for an older release. For the latest information, see the current release documentation.
Large queries may throw
Extremely large queries can consume too much memory during the parsing phase, in which case the Elasticsearch SQL engine will abort parsing and throw an error. In such cases, consider reducing the query to a smaller size by potentially simplifying it or splitting it into smaller queries.
Nested fields in
SYS COLUMNS and
Elasticsearch has a special type of relationship fields called
nested fields. In Elasticsearch SQL they can be used by referencing their inner
sub-fields. Even though
SYS COLUMNS in non-driver mode (in the CLI and in REST calls) and
DESCRIBE TABLE will still display
them as having the type
NESTED, they cannot be used in a query. One can only reference its sub-fields in the form:
SELECT dep.dep_name.keyword FROM test_emp GROUP BY languages;
Scalar functions on nested fields are not allowed in
ORDER BY clausesedit
Elasticsearch SQL doesn’t support the usage of scalar functions on top of nested fields in
ORDER BY clauses with the exception of comparison and logical operators.
SELECT * FROM test_emp WHERE LENGTH(dep.dep_name.keyword) > 5;
SELECT * FROM test_emp ORDER BY YEAR(dep.start_date);
are not supported but:
SELECT * FROM test_emp WHERE dep.start_date >= CAST('2020-01-01' AS DATE) OR dep.dep_end_date IS NULL;
Elasticsearch SQL doesn’t support multi-nested documents, so a query cannot reference more than one nested field in an index. This applies to multi-level nested fields, but also multiple nested fields defined on the same level. For example, for this index:
column | type | mapping ----------------------+---------------+------------- nested_A |STRUCT |NESTED nested_A.nested_X |STRUCT |NESTED nested_A.nested_X.text|VARCHAR |KEYWORD nested_A.text |VARCHAR |KEYWORD nested_B |STRUCT |NESTED nested_B.text |VARCHAR |KEYWORD
nested_B cannot be used at the same time, nor
For such situations, Elasticsearch SQL will display an error message.
Paginating nested inner hitsedit
When SELECTing a nested field, pagination will not work as expected, Elasticsearch SQL will return at least the page size records. This is because of the way nested queries work in Elasticsearch: the root nested field will be returned and it’s matching inner nested fields as well, pagination taking place on the root nested document and not on its inner hits.
keyword fields in Elasticsearch can be normalized by defining a
normalizer. Such fields are not supported in Elasticsearch SQL.
Array type of fieldsedit
Array fields are not supported due to the "invisible" way in which Elasticsearch handles an array of values: the mapping doesn’t indicate whether
a field is an array (has multiple values) or not, so without reading all the data, Elasticsearch SQL cannot know whether a field is a single or multi value.
When multiple values are returned for a field, by default, Elasticsearch SQL will throw an exception. However, it is possible to change this behavior through
field_multi_value_leniency parameter in REST (disabled by default) or
field.multi.value.leniency in drivers (enabled by default).
Sorting by aggregationedit
When doing aggregations (
GROUP BY) Elasticsearch SQL relies on Elasticsearch’s
composite aggregation for its support for paginating results.
However this type of aggregation does come with a limitation: sorting can only be applied on the key used for the aggregation’s buckets.
Elasticsearch SQL overcomes this limitation by doing client-side sorting however as a safety measure, allows only up to 512 rows.
It is recommended to use
LIMIT for queries that use sorting by aggregation, essentially indicating the top N results that are desired:
SELECT * FROM test GROUP BY age ORDER BY COUNT(*) LIMIT 100;
It is possible to run the same queries without a
LIMIT however in that case if the maximum size (10000) is passed,
an exception will be returned as Elasticsearch SQL is unable to track (and sort) all the results returned.
Moreover, the aggregation(s) used in the
ORDER BY must be only plain aggregate functions. No scalar
functions or operators can be used, and therefore no complex columns that combine two ore more aggregate
functions can be used for ordering. Here are some examples of queries that are not allowed:
SELECT age, ROUND(AVG(salary)) AS avg FROM test GROUP BY age ORDER BY avg; SELECT age, MAX(salary) - MIN(salary) AS diff FROM test GROUP BY age ORDER BY diff;
Using aggregation functions on top of scalar functionsedit
Using a sub-selectedit
Using sub-selects (
SELECT X FROM (SELECT Y)) is supported to a small degree: any sub-select that can be "flattened" into a single
SELECT is possible with Elasticsearch SQL. For example:
SELECT * FROM (SELECT first_name, last_name FROM emp WHERE last_name NOT LIKE '%a%') WHERE first_name LIKE 'A%' ORDER BY 1; first_name | last_name ---------------+--------------- Alejandro |McAlpine Anneke |Preusig Anoosh |Peyn Arumugam |Ossenbruggen
The query above is possible because it is equivalent with:
SELECT first_name, last_name FROM emp WHERE last_name NOT LIKE '%a%' AND first_name LIKE 'A%' ORDER BY 1;
But, if the sub-select would include a
GROUP BY or
HAVING or the enclosing
SELECT would be more complex than
FROM (SELECT ...) WHERE [simple_condition], this is currently un-supported.