Query languages & scripting

This section documents the current Elasticsearch 9.x line (with 8.19 as the final 8.x release) as published at the Elasticsearch documentation, which is the reference these pages are written and verified against. No specific patch version is pinned. Some capabilities (Kibana-only UIs, the ML/NLP model-management workflow, cross-cluster replication, and parts of the paid / serverless-only surface) are linked, not documented in depth.

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production, as Elasticsearch iterates quickly.

This section’s bibliography lists the reference material consulted while preparing these pages.

Query DSL is the JSON query language, but it is not the only way to ask Elasticsearch a question. This page covers the alternatives — ES|QL for piped exploratory analytics, EQL for ordered event sequences, SQL for BI tools and ad-hoc reporting, KQL and Lucene syntax for Kibana search bars — and Painless, the scripting language that plugs custom logic into scoring, updates, runtime fields and ingest.

Which language for which job

Language Shape Reach for it when

Query DSL

JSON request body, full control over scoring, highlighting, aggregations, knn, collapse

You are building an application query and want every feature and the fastest path

ES|QL

Piped: FROM …​ | WHERE …​ | STATS …​ | SORT …​ | LIMIT

Exploratory analysis, aggregation, transformation, alerting — one expression, read top to bottom

EQL

Event-oriented: process where …​, sequence by …​ with maxspan=…​

Security and log analytics: "these events, in this order, within this window"

SQL

POST /_sql, standard SELECT …​ FROM …​ GROUP BY

BI tools, JDBC/ODBC clients, and people who already speak SQL

KQL

Kibana search bar: status:error and host.name:web-*

Filtering in Kibana Discover, dashboards and alert rules (the default bar syntax)

Lucene

Kibana search bar (toggle): status:error AND host.name:web-*, exists:user

Kibana searches that need Lucene-only operators (fuzzy ~, regex /…​/, boosts ^)

KQL and Lucene are Kibana-side bar syntaxes that compile down to Query DSL; they are covered alongside the match/query-string family in Full-text queries. The rest of this page is the server-side languages.

ES|QL

The Elasticsearch Query Language (ES|QL) is a piped language: a source command names the data, then each | feeds its rows into the next processing command. It has its own compute engine, so aggregation and transformation happen in one expression instead of a nested aggs tree. See ES|QL for the language reference.

The _query endpoint

Run ES|QL through POST /_query. format (or an Accept header) picks the response shape — txt for a readable table, json (default) for columns + values, plus csv and tsv.

POST /_query?format=txt
{
  "query": """
    FROM employees
    | WHERE still_hired == true
    | STATS avg_salary = AVG(salary) BY department
    | SORT avg_salary DESC
    | LIMIT 5
  """
}
// https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-rest.html

Parameters keep literals out of the query text so it compiles once:

POST /_query
{
  "query": "FROM logs-* | WHERE http.response.status_code >= ? | STATS c = COUNT(*) BY host.name",
  "params": [ 500 ]
}

The core pipeline

FROM logs-*                                         // source: one or more indices, data streams or aliases
| WHERE @timestamp > NOW() - 1 hour                 // row filter, applied early
    AND http.response.status_code >= 500
| EVAL path = SPLIT(url.original, "?")              // derive a new column
| STATS errors = COUNT(*) BY host.name, path        // aggregate; BY sets the grouping keys
| SORT errors DESC                                  // order the result rows
| LIMIT 20                                          // cap the rows returned

Other commands you reach for often: KEEP / DROP / RENAME to shape columns, DISSECT and GROK to parse strings, ENRICH to join a lookup dataset by key, LOOKUP JOIN for a left join against a lookup index. See Commands and Functions and operators.

A few worked examples

// Top 10 clients by traffic in the last day
FROM logs-*
| WHERE @timestamp > NOW() - 1 day
| STATS bytes = SUM(http.response.body.bytes) BY source.ip
| SORT bytes DESC
| LIMIT 10
// 95th percentile response time per service, only where it breaches an SLO
FROM traces-*
| STATS p95 = PERCENTILE(duration_ms, 95) BY service.name
| WHERE p95 > 300
| SORT p95 DESC
// Bucket by hour and count, using DATE_TRUNC as the grouping key
FROM events
| EVAL hour = DATE_TRUNC(1 hour, @timestamp)
| STATS hits = COUNT(*) BY hour
| SORT hour ASC

Current known limits

  • A query returns at most 10,000 rows; without an explicit LIMIT a default (500) is applied, and a larger LIMIT is still capped at 10,000. Page by adding a WHERE on a sort key, not by offset.

  • No access to nested sub-documents — nested fields are not queryable from ES|QL. Model the data flat or query those fields with Query DSL nested.

  • text fields are loaded from _source and are not aggregatable directly; group by the keyword multi-field instead. Unsupported field types (for example dense_vector for arithmetic) are returned as null or rejected.

  • Full ES|QL runs on the coordinating node’s compute engine; cross-cluster search support has been arriving incrementally by version.

The authoritative, version-specific list is ES|QL limitations.

EQL for event sequences

The Event Query Language (EQL) is built for ordered sequences over timestamped, categorised events — the "did A then B then C happen on the same host within N minutes" question that underlies most detection rules. Query it with GET /<target>/_eql/search. See EQL.

Each index must have a timestamp field and an event category field; EQL defaults to @timestamp and event.category, overridable with timestamp_field and event_category_field in the request.

GET /logs-endpoint-*/_eql/search
{
  "query": """
    sequence by host.name with maxspan=10m
      [ process where process.name == "explorer.exe" ]
      [ process where process.parent.name == "explorer.exe" and process.name == "powershell.exe" ]
      [ network where destination.port == 443 and not cidrmatch(destination.ip, "10.0.0.0/8") ]
    until [ process where event.type == "end" ]
  """
}
// https://www.elastic.co/guide/en/elasticsearch/reference/current/eql-syntax.html

  • sequence by <field> requires each bracketed event to share the same value for that field (the join key); with maxspan=10m bounds the wall-clock time from the first matched event to the last.

  • until […​] aborts a partially matched sequence when an "expiry" event fires first.

  • A single condition without sequence is a plain event filter: process where process.name == "cmd.exe".

  • Pipes | head 20, | tail 5 limit results; sample by <field> (non-sequential) groups unordered events.

The comparison operators, functions (cidrmatch, wildcard, endsWith, …​) and pipe list are in EQL syntax reference.

SQL over the _sql endpoint

Elasticsearch answers a useful subset of SQL against a single index, data stream or alias — SELECT, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, most scalar and aggregate functions, and PIVOT. There are no arbitrary `JOIN`s (the data is denormalised, as in document databases generally). Contrast the full language on SQL Queries. See SQL.

POST /_sql?format=txt
{
  "query": "SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ORDER BY avg_salary DESC",
  "fetch_size": 100
}
// https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-rest.html
SELECT host, COUNT(*) AS errors
FROM "logs-*"
WHERE status >= 500 AND "@timestamp" > NOW() - INTERVAL 1 HOUR
GROUP BY host
HAVING COUNT(*) > 10
ORDER BY errors DESC

Cursors, translate, and drivers

When a result exceeds fetch_size, the response carries a cursor; post it back to fetch the next page, and close it when done.

POST /_sql?format=txt
{ "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAA..." }

POST /_sql/close
{ "cursor": "sDXF1ZXJ5QW5kRmV0Y2gBAAAAAAAA..." }

_sql/translate shows the Query DSL a statement compiles to — useful for learning the mapping and for lifting a generated query into an application:

POST /_sql/translate
{ "query": "SELECT host, status FROM \"logs-*\" WHERE status >= 500 ORDER BY \"@timestamp\" DESC LIMIT 20" }

For BI and reporting tools, Elastic ships a JDBC driver and an ODBC driver that speak the same endpoint; the elasticsearch-sql-cli gives an interactive shell.

Painless scripting

Painless is the built-in scripting language: Java-like syntax, sandboxed, compiled to bytecode and cached. A script always runs in a context that fixes which variables are in scope and what a return value means. See Scripting and Painless scripting language.

Script contexts

Context Script sees / returns

script_score query

doc[…​], _score; returns the new double score. Prefer over the older function_score script_score

Runtime field

doc[…​], params._source; calls emit(…​) to produce the field value at search time

_update / update-by-query

ctx._source (mutable), ctx.op; mutates the document in place

script_fields in search

doc[…​], params._source; returns a computed value per hit

ingest script processor

ctx (the source map, mutable) before indexing — see Ingest pipelines

Aggregations

scripted_metric, bucket_script, and script-valued terms/stats sources

 
GET /products/_search
{
  "query": {
    "script_score": {
      "query": { "match": { "name": "laptop" } },
      "script": {
        "source": "_score * saturation(doc['sales'].value, params.pivot)",
        "params": { "pivot": 100 }
      }
    }
  }
}
// https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-script-score-query.html

params and doc-value access

Pass every literal through params, never string-concatenate it into source: Elasticsearch caches a compiled script per unique source text, and a per-node circuit breaker rejects excessive recompilation.

doc['field'].value reads the column-oriented doc values — fast, but requires doc_values (on by default; absent on text). params._source.field reads the raw _source — slower (full JSON parse per hit) but the only option for text and for fields with doc_values disabled. In scoring and sorting, use doc[…​].

GET /events/_search
{
  "runtime_mappings": {
    "day_of_week": {
      "type": "keyword",
      "script": "emit(doc['@timestamp'].value.dayOfWeekEnum.getDisplayName(TextStyle.FULL, Locale.ROOT))"
    }
  },
  "fields": [ "day_of_week" ],
  "_source": false
}
// https://www.elastic.co/guide/en/elasticsearch/reference/current/runtime.html

Stored scripts

Store a script once under _scripts/<id> and reference it by id from any request — handy when several queries share logic or a client should not ship script text.

PUT /_scripts/recency-boost
{
  "script": {
    "lang": "painless",
    "source": "_score + params.weight / (1 + doc['@timestamp'].value.millis / 8.64e7)"
  }
}

GET /articles/_search
{
  "query": {
    "script_score": {
      "query": { "match_all": {} },
      "script": { "id": "recency-boost", "params": { "weight": 3.0 } }
    }
  }
}

GET /_scripts/recency-boost
DELETE /_scripts/recency-boost

See Create or update stored script API.

Prefer a query or runtime field first

A script is the last resort in the hot path, not the first:

  • If a plain term-level or full-text query can express it, use that — it uses the inverted index; a script filter evaluates every candidate document.

  • For a derived value you filter or aggregate on, define a runtime field (schema-on-read, no reindex) — and once the shape settles, index it as a real field or compute it in an ingest pipeline so the cost is paid once at write time.

  • Reserve script_score and scripted_metric for genuinely custom maths that has no declarative form.

Further reading

For the JSON query language these complement, see Full-text queries, Term-level queries and Aggregations.