DQL — Queries

The SQL Gateway provides a full Data Query Language on top of Elasticsearch, translating SQL into search, aggregation, and scroll APIs.

DQL includes cross-index JOINs (INNER / LEFT / RIGHT / FULL OUTER) across indices and clusters — a capability Elasticsearch has no native support for. See Cross-Index JOIN for the full guide; this page covers the single-index query language, including JOIN UNNEST on ARRAY<STRUCT> columns.

SELECT

SELECT [DISTINCT] expr1, expr2, ...
FROM table_name [alias]
[WHERE condition]
[GROUP BY expr1, expr2, ...]
[HAVING condition]
[ORDER BY expr1 [ASC|DESC] [NULLS FIRST|NULLS LAST], ...]
[LIMIT n]
[OFFSET m];

Nested Fields and Aliases

SELECT id,
       name AS full_name,
       profile.city AS city,
       profile.followers AS followers
FROM dql_users
ORDER BY id ASC;

• profile is a STRUCT column
• Dot notation accesses nested fields
• Aliases (AS) are returned as column names

---

WHERE

Supports comparison operators (=, !=, <, <=, >, >=), logical operators (AND, OR, NOT), IN, NOT IN, BETWEEN, IS NULL, IS NOT NULL, LIKE, RLIKE (regex), and conditions on nested fields.

A function in a WHERE predicate, and documents that do not carry the field. A predicate that applies a function to a column (WHERE UPPER(status) = 'A', WHERE ABS(amount) > 10) is executed by Elasticsearch as a Painless script. Since engine 0.23.0 such a predicate follows ANSI three-valued logic for a document in which the field is absent: the comparison is NULL, so the document does not match — and it does not match the negated form either (WHERE NOT UPPER(status) = 'A' leaves it out, because NOT NULL is NULL, not TRUE). Before 0.23.0 the emitted script did not compile at all and Elasticsearch rejected the whole query (script_exception: compile error, caused by class_cast_exception: Cannot cast from [boolean] to [java.lang.Object]), so no such predicate ever ran.

This holds for the comparisons listed here, not only =: <, >, <>, LIKE, NOT LIKE, IN, NOT IN, BETWEEN and NOT BETWEEN over a function all follow the same rule, and a NOT written after AND / OR (WHERE ABS(amount) > 10 AND NOT UPPER(status) = 'A') negates the criterion it qualifies, not the whole composite — so the same predicate returns the same rows whichever way round you write it. Before 0.23.0 several of these did not run at all: LIKE over a function produced an uncompilable script, NOT LIKE failed inside the engine, and IN / BETWEEN over a function were sent to Elasticsearch with an empty field name and rejected.

A predicate with no function is not scripted — it becomes a term/range query — and NOT over it is Elasticsearch’s must_not, which does return documents that lack the field. The two routes therefore differ for absent fields; use IS NULL / IS NOT NULL when that distinction matters.

A projected function keeps its NULL: SELECT UPPER(status) AS u returns u = NULL for a document with no status, and a GROUP BY UPPER(status) has no bucket for it. The collapse to “no match” applies to a condition, never to a value.

🔴 ORDER BY over a function of a column some documents do not carry LOSES ROWS SILENTLY. The engine emits a null-preserving sort script; Elasticsearch then fails the shard while building the comparator (null_pointer_exception). What you see depends on the shard count, and the dangerous case is the normal one:

• on a single-shard index the whole search is rejected — you get an error;
• on a multi-shard index the search returns HTTP 200 and the failing shard’s documents are simply absent from the result. MEASURED on Elasticsearch 8.18.3, 3 shards, 7 documents with one lacking the field: _shards.failed: 1, hits.total: 5 — two rows gone, no error anywhere. The engine does not surface _shards.failures, so nothing reaches the caller.

Until that is fixed, sort by the bare column, or keep the field present on every document. Do not rely on getting an error. The same applies to ORDER BY over a CASE … END with no ELSE, which is NULL-valued for the rows no branch matches.

⚠️ Two limits of the rule above, stated rather than implied. NOT <function>(x) IS NULL is a PARSE rejection — the grammar takes a bare name after NOT there — so the rule covers the comparisons listed, not literally every clause you can write. And on a multi-valued field the scripted and non-scripted routes differ for a reason that has nothing to do with NULL: the native query matches if ANY value matches, while the script reads a single value.

⚠️ When a LIKE over a function needs a regular expression. The engine compiles such a predicate to whitelisted string operations when the pattern contains no _ and uses % only at the ends ('A%', '%A', '%A%', 'A', '', '%'). Every other pattern — including one made only of %, such as 'A%B' — compiles to a Painless regular expression, and Elasticsearch 6.8 disables those by default (script.painless.regex.enabled), answering Regexes are disabled. On 7.x and later every pattern works.

🔴 Changed in 0.23.0 — LIKE reads only % and _ as wildcards. Every other character in a pattern is now matched literally, on the scripted and the native path. WHERE status LIKE 'A.B%' previously matched AXB1, because . reached Elasticsearch as a regular-expression wildcard; it now matches only values that really begin with A.B. Patterns that relied on the old reading must be rewritten with _ (any single character) or % (any sequence). RLIKE is unaffected — its operand is a regular expression by definition.

SELECT id, name, age
FROM dql_users
WHERE (age > 20 AND profile.followers >= 100)
   OR (profile.city = 'Lyon' AND age < 50)
ORDER BY age DESC;

SELECT id, age + 10 AS age_plus_10, name
FROM dql_users
WHERE age BETWEEN 20 AND 50
  AND name IN ('Alice', 'Bob', 'Chloe')
  AND name IS NOT NULL
  AND (name LIKE 'A%' OR name RLIKE '.*o.*');

Temporal literals against date columns

A string literal compared to a column mapped as date (with =, <>, !=, <, <=, >, >=, BETWEEN or IN) is resolved against the column’s mapping format before the query is sent to Elasticsearch, so the SQL-standard spelling a BI tool emits selects the same rows as the ISO one:

WHERE event_ts >= '2026-06-04 00:00:00.000000'   -- what Superset / SQLAlchemy render
WHERE event_ts >= '2026-06-04 00:00:00'
WHERE event_ts >= '2026-06-04T00:00:00'          -- what Elasticsearch's default format accepts

• Under a format that accepts ISO dates (the default strict_date_optional_time||epoch_millis, date_optional_time, strict_date_optional_time_nanos) the space separator is rewritten to T; fraction digits and a trailing zone are preserved. ISO literals, date-only literals, epoch numbers and date math (now-1d/d, 2026-06-04||/M — whose date part is normalised the same way) are forwarded verbatim.
• A column with a custom format (for example yyyy-MM-dd HH:mm:ss) keeps working as before: a literal its format already parses is never rewritten.
• Under the default (strict) format a literal that cannot be a date at all ('not-a-date') or carries an invalid calendar or time value ('2026-02-30', '2026-06-04 24:00:00') fails with an error naming the literal and the field (HTTP 400) instead of a raw Elasticsearch search_phase_execution_exception. A literal that starts like a date but has a shape the resolver does not model (a zone id, a signed year, 2026-6-4) is forwarded verbatim and Elasticsearch decides; under date_optional_time or a custom format nothing is ever rejected.
• The same resolution applies to the WHERE clause of UPDATE and DELETE.
• keyword / text columns, LIKE / RLIKE patterns, function-wrapped columns (YEAR(event_ts)), date_nanos columns, columns qualified with a JOIN alias (the FROM table’s own columns are resolved) and HAVING conditions are never touched.
• The resolution needs the index mapping, loaded through the schema cache (one lookup per index per TTL — elastic.schema-cache.ttl, 5 minutes by default, and a table may set its own with ALTER TABLE … SET SCHEMA CACHE TTL). It does not apply when the statement reads several indices or a wildcard, or when the mapping cannot be loaded — the literal is then forwarded verbatim as in previous releases, and a failed mapping lookup is remembered for the default TTL (a miss has no index metadata to read a per-index one from) so it is not retried on every statement. A mapping reached through a single-index alias is resolved like the index it points at; a multi-index alias has no single mapping and is reported as not found.

---

ORDER BY

Supports multiple sort keys, ASC/DESC, NULLS FIRST / NULLS LAST, expressions, and nested fields.

SELECT id, name, age
FROM dql_users
ORDER BY age DESC, name ASC
LIMIT 2 OFFSET 1;

NULLS FIRST / NULLS LAST

Each sort key may declare where NULL values appear:

SELECT id, name, bonus
FROM dql_users
ORDER BY bonus DESC NULLS LAST;

Mapped to Elasticsearch’s sort.missing parameter:

• NULLS FIRST → "missing": "_first"
• NULLS LAST → "missing": "_last"

When omitted, defaults follow the Elasticsearch convention: ASC → nulls last, DESC → nulls first.

Different null orderings can be combined within a single query:

SELECT id, name, bonus, hire_date
FROM dql_users
ORDER BY bonus DESC NULLS LAST, hire_date ASC NULLS FIRST;

Caveat (ES6 Jest client): scroll / search_after queries in the ES6 Jest client do not propagate NULLS FIRST / NULLS LAST reliably across batches. For ES6 scroll/search_after, prefer client-side null-bucketing or upgrade to ES7+.

---

LIMIT / OFFSET

• LIMIT n restricts returned rows
• OFFSET m skips the first m rows
• Translated to Elasticsearch from + size

---

UNION ALL

Combines results of multiple SELECT queries without removing duplicates. All SELECT statements must have the same number of columns with the same names.

Note

Bare UNION (with row de-duplication) is not supported and is rejected at parse time — the two are not synonyms.

SELECT id, name FROM dql_users WHERE age > 30
UNION ALL
SELECT id, name FROM dql_users WHERE age <= 30;

Executed using Elasticsearch Multi-Search (_msearch). ORDER BY and LIMIT apply per SELECT, not globally.

---

JOIN UNNEST

The Gateway supports JOIN UNNEST on ARRAY<STRUCT> columns.

SELECT
  o.id,
  items.product,
  items.quantity,
  SUM(items.price * items.quantity) OVER (PARTITION BY o.id) AS total_price
FROM dql_orders o
JOIN UNNEST(o.items) AS items
WHERE items.quantity >= 1
ORDER BY o.id ASC;

JOIN UNNEST produces one output row per array element, with parent fields duplicated — exactly like a standard SQL UNNEST. It supports expressions, filtering, and aggregations via window functions. Multi-level nesting is handled recursively.

---

Aggregations

Supported aggregate functions: COUNT(*), COUNT(expr), SUM, AVG, MIN, MAX, STDDEV / STDDEV_SAMP / STDDEV_POP, VARIANCE / VAR_SAMP / VAR_POP.

STDDEV defaults to sample standard deviation (STDDEV ≡ STDDEV_SAMP, Bessel-corrected) and VARIANCE defaults to sample variance (VARIANCE ≡ VAR_SAMP). This matches PostgreSQL and Snowflake; users coming from MySQL 5.5 or earlier should note that those releases defaulted STDDEV to population.

SELECT department,
       STDDEV(salary)   AS sd,
       VAR_POP(salary)  AS vp
FROM emp
GROUP BY department;

All six map to a single Elasticsearch extended_stats aggregation per call. Sample variants require Elasticsearch 7.7+; population variants work on Elasticsearch 6+.

Over a transformed operand (STDDEV(YEAR(hire_date)), VARIANCE(ABS(salary)), STDDEV_POP(DATE_TRUNC(ts, MONTH)), plain or windowed) the behaviour depends on the Elasticsearch major, because the client library the driver builds on drops the aggregation script of an extended_stats on the oldest line:

Elasticsearch	STDDEV(f(x)) / VARIANCE(f(x))
7.x, 8.x, 9.x	Computed over the transform.
6.x	Refused with a 400 naming the release — permanently, on an unmaintained library line.

Before this rule, those releases silently returned the statistic of the raw field. A raw-field operand (STDDEV(salary)) is unaffected on every release. See Known Limitations.

Percentiles — PERCENTILE_CONT / PERCENTILE_DISC

-- p99 request latency per endpoint (SRE latency analysis)
SELECT endpoint,
       PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration_ms) AS p99
FROM requests
GROUP BY endpoint;

Forms accepted (for both PERCENTILE_CONT and PERCENTILE_DISC):

• PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY column) — ANSI ordered-set aggregate (optionally with a top-level GROUP BY)
• PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY column) OVER (PARTITION BY ...) — value column from WITHIN GROUP, partition from OVER
• PERCENTILE_CONT(p) OVER (PARTITION BY ... ORDER BY column) — value column from the OVER ORDER BY
• PERCENTILE_CONT(column, p) — column-first shorthand (many BI tools emit it)

The percentile literal p is a value in [0, 1] (e.g. 0.99 for p99); out-of-range values are rejected at parse time. The value column comes from the ORDER BY clause or the shorthand’s first argument; grouping comes from OVER (PARTITION BY ...) or a top-level GROUP BY. Both functions map to the Elasticsearch percentiles aggregation (TDigest). Elasticsearch has no native discrete percentile, so PERCENTILE_DISC is continuous-backed — it returns the same interpolated value as PERCENTILE_CONT rather than the nearest actual data point. All forms work on Elasticsearch 6+.

GROUP BY and HAVING

SELECT profile.city AS city,
       COUNT(*) AS cnt,
       AVG(age) AS avg_age
FROM dql_users
GROUP BY profile.city
HAVING COUNT(*) >= 1
ORDER BY COUNT(*) DESC;

• GROUP BY supports nested fields
• HAVING filters groups based on aggregate conditions
• Translated to Elasticsearch aggregations
• An aggregate referenced only in HAVING or ORDER BY needs no alias and no SELECT item: it is computed for the filter or the sort and kept out of the result columns. Distinct aggregates over the same column stay distinct (HAVING COUNT(age) >= 1 AND MAX(age) > 45), and the aggregate may wrap a transform (HAVING MAX(YEAR(birthdate)) > 1990, ORDER BY MAX(ABS(age)) DESC)
• Arithmetic over aggregates is computed per group (MAX(price) - MIN(price) AS price_range); the operands are computed as hidden aggregations of the group
• HAVING may reference a SELECT aggregate by its alias (COUNT(*) AS cnt ... HAVING cnt > 1), including the alias of an arithmetic expression over aggregates (... AS price_range ... HAVING price_range > 10); BETWEEN, IN and NOT apply to aggregates as to columns
• Rejected with an explicit error: arithmetic over aggregates written inline in HAVING (HAVING MAX(price) - MIN(price) > 10 — alias it in SELECT and reference the alias), an aggregate function inside WHERE (use HAVING), and an alias that names one aggregate in SELECT and a different one in HAVING / ORDER BY
• A group whose compared metric has no value (for instance MAX(age) over a group whose documents all lack age) never passes a HAVING comparison, in either direction: the generated filter script null-checks every metric before comparing it

---

Parent-Level Aggregations on Nested Arrays

Compute aggregations over nested arrays while keeping one row per parent document (the original nested array is preserved):

SELECT
  o.id,
  o.items,
  SUM(items.price * items.quantity) OVER (PARTITION BY o.id) AS total_price
FROM dql_orders o
JOIN UNNEST(o.items) AS items
WHERE items.quantity >= 1
ORDER BY o.id ASC;

Returns one row per parent with the original nested array preserved and the aggregated value added as a top-level field.

---

Window Functions

Window functions operate over a logical window defined by OVER (PARTITION BY ... ORDER BY ...).

Supported: SUM, AVG, MIN, MAX, COUNT (including COUNT(DISTINCT ...)), STDDEV / STDDEV_SAMP / STDDEV_POP, VARIANCE / VAR_SAMP / VAR_POP, PERCENTILE_CONT / PERCENTILE_DISC, FIRST_VALUE, LAST_VALUE, ARRAY_AGG, ROW_NUMBER, RANK, DENSE_RANK.

PERCENTILE_CONT / PERCENTILE_DISC accept four equivalent spellings — OVER (... ORDER BY column), WITHIN GROUP (ORDER BY column), the two combined, and the (column, p) shorthand. All four normalize to the same canonical rendering, PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY column) [OVER (PARTITION BY ...)], so a statement round-tripped through the engine comes back in that form rather than the one you typed.

SELECT
  product,
  customer,
  amount,
  SUM(amount) OVER (PARTITION BY product) AS sum_per_product,
  COUNT(_id) OVER (PARTITION BY product) AS cnt_per_product,
  FIRST_VALUE(amount) OVER (PARTITION BY product ORDER BY ts ASC) AS first_amount,
  LAST_VALUE(amount) OVER (PARTITION BY product ORDER BY ts ASC) AS last_amount,
  ARRAY_AGG(amount) OVER (PARTITION BY product ORDER BY ts ASC LIMIT 10) AS amounts_array
FROM dql_sales
ORDER BY product, ts;

Ranking windows (ROW_NUMBER, RANK, DENSE_RANK)

ORDER BY is REQUIRED inside OVER for ranking functions (ANSI). PARTITION BY is optional — when absent, the entire result set is treated as one partition.

SELECT name, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS r,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr
FROM emp;

Tie semantics:

• ROW_NUMBER — sequential within partition; no ties (1, 2, 3, 4, …)
• RANK — ties share rank, next rank skips (1, 2, 2, 4, …)
• DENSE_RANK — ties share rank, next rank does NOT skip (1, 2, 2, 3, …)

Top-N per group: inline LIMIT N inside OVER to keep only the top-N rows per partition. The engine pushes N into the Elasticsearch top_hits.size parameter, so only the top-N rows per partition are materialised:

SELECT name, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC LIMIT 3) AS r
FROM emp;

Without an explicit LIMIT, top_hits.size defaults to 100 — the Elasticsearch index.max_inner_result_window default. For larger partitions either supply LIMIT N inline or raise the index setting.

---

Functions

Numeric & Trigonometric

Function	Description
ABS(x)	Absolute value
CEIL(x) / CEILING(x)	Round up
FLOOR(x)	Round down
ROUND(x, n)	Round to n decimals
SQRT(x)	Square root
POW(x, y) / POWER(x, y)	Power
EXP(x)	Exponential
LOG(x) / LN(x)	Natural logarithm
LOG10(x)	Base-10 logarithm
SIGN(x) / SGN(x)	Sign of x
SIN(x), COS(x), TAN(x)	Trigonometric
ASIN(x), ACOS(x), ATAN(x)	Inverse trigonometric
ATAN2(y, x)	Arc-tangent of y/x
PI()	Pi constant
RADIANS(x), DEGREES(x)	Angle conversion

String

Function	Description
CONCAT(a, b, ...)	Concatenate strings
SUBSTRING(str, start, len)	Extract substring
LOWER(str) / LCASE(str)	Lowercase
UPPER(str) / UCASE(str)	Uppercase
TRIM(str), LTRIM(str), RTRIM(str)	Trim whitespace
LENGTH(str) / LEN(str)	String length
REPLACE(str, from, to)	Replace substring
LEFT(str, n), RIGHT(str, n)	Left/right n chars
REVERSE(str)	Reverse string
POSITION(substr IN str) / STRPOS(str, substr)	Position of substring
REGEXP_LIKE(str, pattern)	Regex match
MATCH(str) AGAINST (query)	Full-text search

Date & Time

Current:

Function	Description
CURRENT_DATE / TODAY() / CURDATE()	Current date (UTC)
CURRENT_TIMESTAMP / NOW() / CURRENT_DATETIME	Current timestamp (UTC)
CURRENT_TIME / CURTIME()	Current time (UTC)

Extraction:

Function	Description
YEAR(date), MONTH(date), DAY(date)	Date components
HOUR(ts), MINUTE(ts), SECOND(ts)	Time components
MILLISECOND(ts), MICROSECOND(ts), NANOSECOND(ts)	Sub-second components
EXTRACT(unit FROM date)	Extract any date/time unit

Arithmetic:

Function	Description
DATE_ADD(date, INTERVAL n unit)	Add interval
DATE_SUB(date, INTERVAL n unit)	Subtract interval
DATETIME_ADD(ts, INTERVAL n unit)	Add interval to timestamp
DATETIME_SUB(ts, INTERVAL n unit)	Subtract from timestamp
DATE_DIFF(date1, date2, unit)	Difference in units
DATE_TRUNC(date, unit)	Truncate to unit

Formatting & Parsing:

Function	Description
DATE_FORMAT(ts, pattern)	Format date as string
DATE_PARSE(str, pattern)	Parse string into date
DATETIME_FORMAT(ts, pattern)	Format timestamp as string
DATETIME_PARSE(str, pattern)	Parse string into timestamp

Special:

Function	Description
LAST_DAY(date)	Last day of month
EPOCHDAY(date)	Days since epoch (1970-01-01)
OFFSET_SECONDS(date)	Epoch seconds

Geospatial

-- Create point
POINT(latitude, longitude)

-- Calculate distance (Paris)
ST_DISTANCE(location, POINT(48.8566, 2.3522))

Latitude comes first and longitude second — not the GeoJSON order. Latitude runs from -90 to 90, longitude from -180 to 180, in the WGS84 (EPSG:4326) reference system.

POINT has no standalone form: it is only accepted as an argument to ST_DISTANCE. Each coordinate must be written with a decimal point — POINT(1.0, 2.0) is accepted, POINT(1, 2) is not.

Supported distance units: km, m, cm, mm, mi, yd, ft, in, nmi.

Conditional

Function	Description
CASE WHEN ... THEN ... ELSE ... END	Conditional expression
COALESCE(a, b, c)	First non-null value
NULLIF(a, b)	NULL if a = b
GREATEST(e1, e2, ...)	Largest non-null numeric value (NULL only when every arg is NULL)
LEAST(e1, e2, ...)	Smallest non-null numeric value (NULL only when every arg is NULL)
ISNULL(expr)	TRUE if NULL
ISNOTNULL(expr)	TRUE if NOT NULL

Type Conversion

Function	Description
CAST(value AS TYPE)	Convert type (error on failure)
TRY_CAST(value AS TYPE) / SAFE_CAST(...)	Convert type (NULL on failure)
CONVERT(value, TYPE)	Alias for CAST
value::TYPE	PostgreSQL-style cast

System

Function	Description
VERSION()	Engine version string

---

Scroll & Pagination

For large result sets, the Gateway uses Elasticsearch scroll or search-after mechanisms depending on backend capabilities.

• LIMIT and OFFSET are applied after retrieving documents from Elasticsearch
• Deep pagination may require scroll
• search_after requires an explicit ORDER BY clause

---

SHOW and DESCRIBE Commands

-- Tables
SHOW TABLES [LIKE 'pattern'];
SHOW TABLE table_name;
SHOW CREATE TABLE table_name;
DESCRIBE TABLE table_name;

-- Pipelines
SHOW PIPELINES;
SHOW PIPELINE pipeline_name;
SHOW CREATE PIPELINE pipeline_name;
DESCRIBE PIPELINE pipeline_name;

-- Watchers
SHOW WATCHERS;
SHOW WATCHER STATUS watcher_name;

-- Enrich Policies
SHOW ENRICH POLICIES;
SHOW ENRICH POLICY policy_name;

-- Cluster
SHOW CLUSTER NAME;

-- License
SHOW LICENSE;
REFRESH LICENSE;

---

SHOW LICENSE

SHOW LICENSE;

Returns the current license type, quota values, expiration date, and grace status.

Column	Description
license_type	Current license tier (Community, Pro, Enterprise). Shows “(trial)” suffix for trial licenses, “(degraded)” suffix if degraded.
trial	true if the license is a Pro trial, false otherwise
platform	Platform scope of the current license key (PRODUCTION, STAGING, DEVELOPMENT, INTEGRATION). Defaults to “PRODUCTION” when not platform-scoped.
max_materialized_views	Maximum materialized views allowed, or “unlimited”
max_clusters	Maximum federated clusters allowed, or “unlimited”
max_result_rows	Maximum rows returned per query, or “unlimited”
max_joins	Maximum number of JOIN operations allowed per query, or “unlimited”
expires_at	License expiration timestamp, or “never” for Community
days_remaining	Days until expiration, or -1 for Community (no expiry)
status	”Active”, or grace period details if expired

---

REFRESH LICENSE

REFRESH LICENSE;

Forces an immediate license refresh from the backend (API key fetch). Returns the previous and new tier information.

Column	Description
previous_tier	License tier before refresh
new_tier	License tier after refresh
trial	true if the new license is a Pro trial, false otherwise
expires_at	New expiration timestamp
status	”Refreshed” on success, “Failed” on error
message	Error details (empty on success)

Requires API key configuration. Without an API key, returns an informational failure message.

---

Version Compatibility

Feature	ES6	ES7	ES8	ES9
Basic SELECT	Yes	Yes	Yes	Yes
Nested fields	Yes	Yes	Yes	Yes
UNION ALL	Yes	Yes	Yes	Yes
Cross-index JOINs	Yes	Yes	Yes	Yes
JOIN UNNEST	Yes	Yes	Yes	Yes
Aggregations	Yes	Yes	Yes	Yes
Parent-level nested array aggs	Yes	Yes	Yes	Yes
Window functions	Yes	Yes	Yes	Yes
Geospatial functions	Yes	Yes	Yes	Yes
Date/time functions	Yes	Yes	Yes	Yes
String / math functions	Yes	Yes	Yes	Yes

---

Limitations

• Cross-index JOINs (INNER / LEFT / RIGHT / FULL OUTER) are supported across indices and clusters — see Cross-Index JOIN. JOIN UNNEST on ARRAY\<STRUCT\> is handled natively inside a single index.
• No correlated subqueries
• No arbitrary subqueries in SELECT or WHERE
• No GROUPING SETS, CUBE, ROLLUP
• No DISTINCT ON
• No explicit window frame clauses (ROWS BETWEEN ...)