ES|QL Query Builder

Commands

class elasticsearch.esql.ESQL

The static methods of the ESQL class provide access to the ES|QL source commands, used to create ES|QL queries.

These methods return an instance of class ESQLBase, which provides access to the ES|QL processing commands.

static branch()

This method can only be used inside a FORK command to create each branch.

Examples:

query = ESQL.from_("employees").fork(
    ESQL.branch().where("emp_no == 10001"),
    ESQL.branch().where("emp_no == 10002"),
)

Return type:: Branch

static from_(*indices)

The FROM source command returns a table with data from a data stream, index, or alias.

Parameters:: indices (Type[DocumentBase] | str) – A list of indices, data streams or aliases. Supports wildcards and date math.
Return type:: From

Examples:

query1 = ESQL.from_("employees")
query2 = ESQL.from_("<logs-{now/d}>")
query3 = ESQL.from_("employees-00001", "other-employees-*")
query4 = ESQL.from_("cluster_one:employees-00001", "cluster_two:other-employees-*")
query5 = ESQL.from_("employees").metadata("_id")

static row(**params)

The ROW source command produces a row with one or more columns with values that you specify. This can be useful for testing.

Parameters:: params (Any) – the column values to produce, given as keyword arguments.
Return type:: Row

Examples:

query1 = ESQL.row(a=1, b="two", c=None)
query2 = ESQL.row(a=[1, 2])
query3 = ESQL.row(a=functions.round(1.23, 0))

static show(item)

The SHOW source command returns information about the deployment and its capabilities.

Parameters:: item (str) – Can only be INFO.
Return type:: Show

Examples:

query = ESQL.show("INFO")

static ts(*indices)

The TS source command is similar to FROM, but for time series indices.

Parameters:: indices (Type[DocumentBase] | str) – A list of indices, data streams or aliases. Supports wildcards and date math.
Return type:: TS

Examples:

query = (
    ESQL.ts("metrics")
    .where("@timestamp >= now() - 1 day")
    .stats("SUM(AVG_OVER_TIME(memory_usage)").by("host", "TBUCKET(1 hour)")
)

class elasticsearch.esql.esql.ESQLBase

The methods of the ESQLBase class provide access to the ES|QL processing commands, used to build ES|QL queries.

change_point(value)

CHANGE_POINT detects spikes, dips, and change points in a metric.

Parameters:: value (InstrumentedField | str) – The column with the metric in which you want to detect a change point.
Return type:: ChangePoint

Examples:

query = (
    ESQL.row(key=list(range(1, 26)))
    .mv_expand("key")
    .eval(value=functions.case("key<13", 0, 42))
    .change_point("value")
    .on("key")
    .where("type IS NOT NULL")
)

completion(*prompt, **named_prompt)

The COMPLETION command allows you to send prompts and context to a Large Language Model (LLM) directly within your ES|QL queries, to perform text generation tasks.

Parameters:

prompt (Any) – The input text or expression used to prompt the LLM. This can be a string literal or a reference to a column containing text.
named_prompt (Any) – The input text or expresion, given as a keyword argument. The argument name is used for the column name. If the prompt is given as a positional argument, the results will be stored in a column named completion. If the specified column already exists, it will be overwritten with the new results.

Return type:

Completion

Examples:

query1 = (
    ESQL.row(question="What is Elasticsearch?")
    .completion("question").with_("test_completion_model")
    .keep("question", "completion")
)
query2 = (
    ESQL.row(question="What is Elasticsearch?")
    .completion(answer="question").with_("test_completion_model")
    .keep("question", "answer")
)
query3 = (
    ESQL.from_("movies")
    .sort("rating DESC")
    .limit(10)
    .eval(prompt="""CONCAT(
        "Summarize this movie using the following information: \n",
        "Title: ", title, "\n",
        "Synopsis: ", synopsis, "\n",
        "Actors: ", MV_CONCAT(actors, ", "), "\n",
    )""")
    .completion(summary="prompt").with_("test_completion_model")
    .keep("title", "summary", "rating")
)

dissect(input, pattern)

DISSECT enables you to extract structured data out of a string.

Parameters:

input (InstrumentedField | str) – The column that contains the string you want to structure. If the column has multiple values, DISSECT will process each value.
pattern (str) – A dissect pattern. If a field name conflicts with an existing column, the existing column is dropped. If a field name is used more than once, only the rightmost duplicate creates a column.

Return type:

Dissect

Examples:

query = (
    ESQL.row(a="2023-01-23T12:15:00.000Z - some text - 127.0.0.1")
    .dissect("a", "%{date} - %{msg} - %{ip}")
    .keep("date", "msg", "ip")
    .eval(date="TO_DATETIME(date)")
)

drop(*columns)

The DROP processing command removes one or more columns.

Parameters:: columns (InstrumentedField | str) – The columns to drop, given as positional arguments. Supports wildcards.
Return type:: Drop

Examples:

query1 = ESQL.from_("employees").drop("height")
query2 = ESQL.from_("employees").drop("height*")

enrich(policy)

ENRICH enables you to add data from existing indices as new columns using an enrich policy.

Parameters:: policy (str) – The name of the enrich policy. You need to create and execute the enrich policy first.
Return type:: Enrich

Examples:

query1 = (
    ESQL.row(a="1")
    .enrich("languages_policy").on("a").with_("language_name")
)
query2 = (
    ESQL.row(a="1")
    .enrich("languages_policy").on("a").with_(name="language_name")
)

eval(*columns, **named_columns)

The EVAL processing command enables you to append new columns with calculated values.

Parameters:

columns (Any) – The values for the columns, given as positional arguments. Can be literals, expressions, or functions. Can use columns defined left of this one.
named_columns (Any) – The values for the new columns, given as keyword arguments. The name of the arguments is used as column name. If a column with the same name already exists, the existing column is dropped. If a column name is used more than once, only the rightmost duplicate creates a column.

Return type:

Eval

Examples:

query1 = (
    ESQL.from_("employees")
    .sort("emp_no")
    .keep("first_name", "last_name", "height")
    .eval(height_feet="height * 3.281", height_cm="height * 100")
)
query2 = (
    ESQL.from_("employees")
    .eval("height * 3.281")
    .stats(avg_height_feet=functions.avg("`height * 3.281`"))
)

fork(fork1, fork2=None, fork3=None, fork4=None, fork5=None, fork6=None, fork7=None, fork8=None)

The FORK processing command creates multiple execution branches to operate on the same input data and combines the results in a single output table.

Parameters:

fork<n> – Up to 8 execution branches, created with the ESQL.branch() method.
fork1 (Branch)
fork2 (Branch | None)
fork3 (Branch | None)
fork4 (Branch | None)
fork5 (Branch | None)
fork6 (Branch | None)
fork7 (Branch | None)
fork8 (Branch | None)

Return type:

Fork

Examples:

query = (
    ESQL.from_("employees")
    .fork(
        ESQL.branch().where("emp_no == 10001"),
        ESQL.branch().where("emp_no == 10002"),
    )
    .keep("emp_no", "_fork")
    .sort("emp_no")
)

fuse(method=None)

The FUSE processing command merges rows from multiple result sets and assigns new relevance scores.

Parameters:: method (str | None) – Defaults to RRF. Can be one of RRF (for Reciprocal Rank Fusion) or LINEAR (for linear combination of scores). Designates which method to use to assign new relevance scores.
Return type:: Fuse

Examples:

query1 = (
    ESQL.from_("books").metadata("_id", "_index", "_score")
    .fork(
        ESQL.branch().where('title:"Shakespeare"').sort("_score DESC"),
        ESQL.branch().where('semantic_title:"Shakespeare"').sort("_score DESC"),
    )
    .fuse()
)
query2 = (
    ESQL.from_("books").metadata("_id", "_index", "_score")
    .fork(
        ESQL.branch().where('title:"Shakespeare"').sort("_score DESC"),
        ESQL.branch().where('semantic_title:"Shakespeare"').sort("_score DESC"),
    )
    .fuse("linear")
)
query3 = (
    ESQL.from_("books").metadata("_id", "_index", "_score")
    .fork(
        ESQL.branch().where('title:"Shakespeare"').sort("_score DESC"),
        ESQL.branch().where('semantic_title:"Shakespeare"').sort("_score DESC"),
    )
    .fuse("linear").by("title", "description")
)
query4 = (
    ESQL.from_("books").metadata("_id", "_index", "_score")
    .fork(
        ESQL.branch().where('title:"Shakespeare"').sort("_score DESC"),
        ESQL.branch().where('semantic_title:"Shakespeare"').sort("_score DESC"),
    )
    .fuse("linear").with_(normalizer="minmax")
)

grok(input, pattern)

GROK enables you to extract structured data out of a string.

Parameters:

input (InstrumentedField | str) – The column that contains the string you want to structure. If the column has multiple values, GROK will process each value.
pattern (str) – A grok pattern. If a field name conflicts with an existing column, the existing column is discarded. If a field name is used more than once, a multi-valued column will be created with one value per each occurrence of the field name.

Return type:

Grok

Examples:

query1 = (
    ESQL.row(a="2023-01-23T12:15:00.000Z 127.0.0.1 some.email@foo.com 42")
    .grok("a", "%{TIMESTAMP_ISO8601:date} %{IP:ip} %{EMAILADDRESS:email} %{NUMBER:num}")
    .keep("date", "ip", "email", "num")
)
query2 = (
    ESQL.row(a="2023-01-23T12:15:00.000Z 127.0.0.1 some.email@foo.com 42")
    .grok(
        "a",
        "%{TIMESTAMP_ISO8601:date} %{IP:ip} %{EMAILADDRESS:email} %{NUMBER:num:int}",
    )
    .keep("date", "ip", "email", "num")
    .eval(date=functions.to_datetime("date"))
)
query3 = (
    ESQL.from_("addresses")
    .keep("city.name", "zip_code")
    .grok("zip_code", "%{WORD:zip_parts} %{WORD:zip_parts}")
)

inline_stats(*expressions, **named_expressions)

The INLINE STATS processing command groups rows according to a common value and calculates one or more aggregated values over the grouped rows.

The command is identical to STATS except that it preserves all the columns from the input table.

Parameters:

expressions (Any) – A list of expressions, given as positional arguments.
named_expressions (Any) – A list of expressions, given as keyword arguments. The argument names are used for the returned aggregated values.

Return type:

Stats

Note that only one of expressions and named_expressions must be provided.

Examples:

query1 = (
    ESQL.from_("employees")
    .keep("emp_no", "languages", "salary")
    .inline_stats(max_salary=functions.max(E("salary"))).by("languages")
)
query2 = (
    ESQL.from_("employees")
    .keep("emp_no", "languages", "salary")
    .inline_stats(max_salary=functions.max(E("salary")))
)
query3 = (
    ESQL.from_("employees")
    .where("still_hired")
    .keep("emp_no", "languages", "salary", "hire_date")
    .eval(tenure=functions.date_diff("year", E("hire_date"), "2025-09-18T00:00:00"))
    .drop("hire_date")
    .inline_stats(
        avg_salary=functions.avg(E("salary")),
        count=functions.count(E("*")),
    )
    .by("languages", "tenure")
)
query4 = (
    ESQL.from_("employees")
    .keep("emp_no", "salary")
    .inline_stats(
        avg_lt_50=functions.round(functions.avg(E("salary"))).where(E("salary") < 50000),
        avg_lt_60=functions.round(functions.avg(E("salary"))).where(E("salary") >= 50000, E("salary") < 60000),
        avg_gt_60=functions.round(functions.avg(E("salary"))).where(E("salary") >= 60000),
    )
)

keep(*columns)

The KEEP processing command enables you to specify what columns are returned and the order in which they are returned.

Parameters:: columns (InstrumentedField | str) – The columns to keep, given as positional arguments. Supports wildcards.
Return type:: Keep

Examples:

query1 = ESQL.from_("employees").keep("emp_no", "first_name", "last_name", "height")
query2 = ESQL.from_("employees").keep("h*")
query3 = ESQL.from_("employees").keep("h*", "*")

limit(max_number_of_rows)

The LIMIT processing command enables you to limit the number of rows that are returned.

Parameters:: max_number_of_rows (int) – The maximum number of rows to return.
Return type:: Limit

Examples:

query1 = ESQL.from_("employees").sort("emp_no ASC").limit(5)
query2 = ESQL.from_("index").stats(functions.avg("field1")).by("field2").limit(20000)

lookup_join(lookup_index)

LOOKUP JOIN enables you to add data from another index, AKA a ‘lookup’ index, to your ES|QL query results, simplifying data enrichment and analysis workflows.

Parameters:: lookup_index (Type[DocumentBase] | str) – The name of the lookup index. This must be a specific index name - wildcards, aliases, and remote cluster references are not supported. Indices used for lookups must be configured with the lookup index mode.
Return type:: LookupJoin

Examples:

query1 = (
    ESQL.from_("firewall_logs")
    .lookup_join("threat_list").on("source.IP")
    .where("threat_level IS NOT NULL")
)
query2 = (
    ESQL.from_("system_metrics")
    .lookup_join("host_inventory").on("host.name")
    .lookup_join("ownerships").on("host.name")
)
query3 = (
    ESQL.from_("app_logs")
    .lookup_join("service_owners").on("service_id")
)
query4 = (
    ESQL.from_("employees")
    .eval(language_code="languages")
    .where("emp_no >= 10091 AND emp_no < 10094")
    .lookup_join("languages_lookup").on("language_code")
)

mv_expand(column)

The MV_EXPAND processing command expands multivalued columns into one row per value, duplicating other columns.

Parameters:: column (InstrumentedField | str) – The multivalued column to expand.
Return type:: MvExpand

Examples:

query = ESQL.row(a=[1, 2, 3], b="b", j=["a", "b"]).mv_expand("a")

rename(**columns)

The RENAME processing command renames one or more columns.

Parameters:: columns (InstrumentedField | str) – The old and new column name pairs, given as keyword arguments. If a name conflicts with an existing column name, the existing column is dropped. If multiple columns are renamed to the same name, all but the rightmost column with the same new name are dropped.
Return type:: Rename

Examples:

query = (
    ESQL.from_("employees")
    .keep("first_name", "last_name", "still_hired")
    .rename(still_hired="employed")
)

rerank(*query, **named_query)

The RERANK command uses an inference model to compute a new relevance score for an initial set of documents, directly within your ES|QL queries.

Parameters:

query (Any) – The query text used to rerank the documents. This is typically the same query used in the initial search.
named_query (Any) – The query text used to rerank the documents, given as a keyword argument. The argument name is used for the column name. If the query is given as a positional argument, the results will be stored in a column named _score. If the specified column already exists, it will be overwritten with the new results.

Return type:

Rerank

Examples:

query1 = (
    ESQL.from_("books").metadata("_score")
    .where('MATCH(description, "hobbit")')
    .sort("_score DESC")
    .limit(100)
    .rerank("hobbit").on("description").with_(inference_id="test_reranker")
    .limit(3)
    .keep("title", "_score")
)
query2 = (
    ESQL.from_("books").metadata("_score")
    .where('MATCH(description, "hobbit") OR MATCH(author, "Tolkien")')
    .sort("_score DESC")
    .limit(100)
    .rerank(rerank_score="hobbit").on("description", "author").with_(inference_id="test_reranker")
    .sort("rerank_score")
    .limit(3)
    .keep("title", "_score", "rerank_score")
)
query3 = (
    ESQL.from_("books").metadata("_score")
    .where('MATCH(description, "hobbit") OR MATCH(author, "Tolkien")')
    .sort("_score DESC")
    .limit(100)
    .rerank(rerank_score="hobbit").on("description", "author").with_(inference_id="test_reranker")
    .eval(original_score="_score", _score="rerank_score + original_score")
    .sort("_score")
    .limit(3)
    .keep("title", "original_score", "rerank_score", "_score")
)

sample(probability)

The SAMPLE command samples a fraction of the table rows.

Parameters:: probability (float) – The probability that a row is included in the sample. The value must be between 0 and 1, exclusive.
Return type:: Sample

Examples:

query = ESQL.from_("employees").keep("emp_no").sample(0.05)

sort(*columns)

The SORT processing command sorts a table on one or more columns.

Parameters:: columns (Any) – The columns to sort on.
Return type:: Sort

Examples:

query1 = (
    ESQL.from_("employees")
    .keep("first_name", "last_name", "height")
    .sort("height")
)
query2 =  (
    ESQL.from_("employees")
    .keep("first_name", "last_name", "height")
    .sort("height DESC")
)
query3 = (
    ESQL.from_("employees")
    .keep("first_name", "last_name", "height")
    .sort("height DESC", "first_name ASC")
)
query4 = (
    ESQL.from_("employees")
    .keep("first_name", "last_name", "height")
    .sort("first_name ASC NULLS FIRST")
)

stats(*expressions, **named_expressions)

The STATS processing command groups rows according to a common value and calculates one or more aggregated values over the grouped rows.

Parameters:

expressions (Any) – A list of expressions, given as positional arguments.
named_expressions (Any) – A list of expressions, given as keyword arguments. The argument names are used for the returned aggregated values.

Return type:

Stats

Note that only one of expressions and named_expressions must be provided.

Examples:

query1 = (
    ESQL.from_("employees")
    .stats(count=functions.count("emp_no")).by("languages")
    .sort("languages")
)
query2 = (
    ESQL.from_("employees")
    .stats(avg_lang=functions.avg("languages"))
)
query3 = (
    ESQL.from_("employees")
    .stats(
        avg_lang=functions.avg("languages"),
        max_lang=functions.max("languages")
    )
)
query4 = (
    ESQL.from_("employees")
    .stats(
        avg50s=functions.avg("salary").where('birth_date < "1960-01-01"'),
        avg60s=functions.avg("salary").where('birth_date >= "1960-01-01"'),
    ).by("gender")
    .sort("gender")
)
query5 = (
    ESQL.from_("employees")
    .eval(Ks="salary / 1000")
    .stats(
        under_40K=functions.count("*").where("Ks < 40"),
        inbetween=functions.count("*").where("40 <= Ks AND Ks < 60"),
        over_60K=functions.count("*").where("60 <= Ks"),
        total=f.count("*")
    )
)
query6 = (
    ESQL.row(i=1, a=["a", "b"])
    .stats(functions.min("i")).by("a")
    .sort("a ASC")
)
query7 = (
    ESQL.from_("employees")
    .eval(hired=functions.date_format("hire_date", "yyyy"))
    .stats(avg_salary=functions.avg("salary")).by("hired", "languages.long")
    .eval(avg_salary=functions.round("avg_salary"))
    .sort("hired", "languages.long")

)

where(*expressions)

The WHERE processing command produces a table that contains all the rows from the input table for which the provided condition evaluates to true.

Parameters:: expressions (Any) – A list of boolean expressions, given as positional arguments. These expressions are combined with an AND logical operator.
Return type:: Where

Examples:

query1 = (
    ESQL.from_("employees")
    .keep("first_name", "last_name", "still_hired")
    .where("still_hired == true")
)
query2 = (
    ESQL.from_("sample_data")
    .where("@timestamp > NOW() - 1 hour")
)
query3 = (
    ESQL.from_("employees")
    .keep("first_name", "last_name", "height")
    .where("LENGTH(first_name) < 4")
)

class elasticsearch.esql.esql.From

Implementation of the FROM source command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

metadata(*fields)

Continuation of the FROM and TS source commands.

Parameters:: fields (InstrumentedField | str) – metadata fields to retrieve, given as positional arguments.
Return type:: From

class elasticsearch.esql.esql.Row

Implementation of the ROW source command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.Show

Implementation of the SHOW source command.

This class inherits from ESQLBase, which makes it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.ChangePoint

Implementation of the CHANGE POINT processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

as_(type_name, pvalue_name)

Continuation of the CHANGE_POINT command.

Parameters:

type_name (str) – The name of the output column with the change point type. If not specified, type is used.
pvalue_name (str) – The name of the output column with the p-value that indicates how extreme the change point is. If not specified, pvalue is used.

Return type:

ChangePoint

on(key)

Continuation of the CHANGE_POINT command.

Parameters:: key (InstrumentedField | str) – The column with the key to order the values by. If not specified, @timestamp is used.
Return type:: ChangePoint

class elasticsearch.esql.esql.Completion

Implementation of the COMPLETION processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

with_(inference_id)

Continuation of the COMPLETION command.

Parameters:: inference_id (str) – The ID of the inference endpoint to use for the task. The inference endpoint must be configured with the completion task type.
Return type:: Completion

class elasticsearch.esql.esql.Dissect

Implementation of the DISSECT processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

append_separator(separator)

Continuation of the DISSECT command.

Parameters:: separator (str) – A string used as the separator between appended values, when using the append modifier.
Return type:: Dissect

class elasticsearch.esql.esql.Drop

Implementation of the DROP processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.Enrich

Implementation of the ENRICH processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

on(match_field)

Continuation of the ENRICH command.

Parameters:: match_field (InstrumentedField | str) – The match field. ENRICH uses its value to look for records in the enrich index. If not specified, the match will be performed on the column with the same name as the match_field defined in the enrich policy.
Return type:: Enrich

with_(*fields, **named_fields)

Continuation of the ENRICH command.

Parameters:

fields (InstrumentedField | str) – The enrich fields from the enrich index that are added to the result as new columns, given as positional arguments. If a column with the same name as the enrich field already exists, the existing column will be replaced by the new column. If not specified, each of the enrich fields defined in the policy is added. A column with the same name as the enrich field will be dropped unless the enrich field is renamed.
named_fields (InstrumentedField | str) – The enrich fields from the enrich index that are added to the result as new columns, given as keyword arguments. The name of the keyword arguments are used as column names. If a column has the same name as the new name, it will be discarded. If a name (new or original) occurs more than once, only the rightmost duplicate creates a new column.

Return type:

Enrich

class elasticsearch.esql.esql.Eval

Implementation of the EVAL processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.Fork

Implementation of the FORK processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.Grok

Implementation of the GROK processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.Keep

Implementation of the KEEP processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.Limit

Implementation of the LIMIT processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.LookupJoin

Implementation of the LOOKUP JOIN processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

on(field)

Continuation of the LOOKUP JOIN command.

Parameters:: field (InstrumentedField | str) – The field to join on. This field must exist in both your current query results and in the lookup index. If the field contains multi-valued entries, those entries will not match anything (the added fields will contain null for those rows).
Return type:: LookupJoin

class elasticsearch.esql.esql.MvExpand

Implementation of the MV_EXPAND processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.Rename

Implementation of the RENAME processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.Sample

Implementation of the SAMPLE processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.Sort

Implementation of the SORT processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

class elasticsearch.esql.esql.Stats

Implementation of the STATS processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

by(*grouping_expressions)

Continuation of the STATS and INLINE STATS commands.

Parameters:: grouping_expressions (Any) – Expressions that output the values to group by. If their names coincide with one of the computed columns, that column will be ignored.
Return type:: Stats

class elasticsearch.esql.esql.Where

Implementation of the WHERE processing command.

This class inherits from ESQLBase, to make it possible to chain all the commands that belong to an ES|QL query in a single expression.

Functions

elasticsearch.esql.functions.abs(number)

Returns the absolute value.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.absent(field)

Returns true if the input expression yields no non-null values within the current aggregation context.

Parameters:: field (Any) – Expression that outputs values to be checked for absence.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.absent_over_time(field)

Calculates the absence of a field in the output result over time range.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.acos(number)

Returns the arccosine of n as an angle, expressed in radians.

Parameters:: number (Any) – Number between -1 and 1. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.asin(number)

Returns the arcsine of the input numeric expression as an angle, expressed in radians.

Parameters:: number (Any) – Number between -1 and 1. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.atan(number)

Returns the arctangent of the input numeric expression as an angle, expressed in radians.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.atan2(y_coordinate, x_coordinate)

The angle between the positive x-axis and the ray from the origin to the point (x , y) in the Cartesian plane, expressed in radians.

Parameters:

y_coordinate (Any) – y coordinate. If null, the function returns null.
x_coordinate (Any) – x coordinate. If null, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.avg(number)

The average of a numeric field.

Parameters:: number (Any) – Expression that outputs values to average.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.avg_over_time(number)

The average over time of a numeric field.

Parameters:: number (Any) – Expression that outputs values to average.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.bit_length(string)

Returns the bit length of a string.

Parameters:: string (Any) – String expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.bucket(field, buckets, from_, to)

Creates groups of values - buckets - out of a datetime or numeric input. The size of the buckets can either be provided directly, or chosen based on a recommended count and values range.

Parameters:

field (Any) – Numeric or date expression from which to derive buckets.
buckets (Any) – Target number of buckets, or desired bucket size if from and to parameters are omitted.
from – Start of the range. Can be a number, a date or a date expressed as a string.
to (Any) – End of the range. Can be a number, a date or a date expressed as a string.
from_ (Any)

Return type:

InstrumentedExpression

elasticsearch.esql.functions.byte_length(string)

Returns the byte length of a string.

Parameters:: string (Any) – String expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.case(*conditions)

Accepts pairs of conditions and values. The function returns the value that belongs to the first condition that evaluates to true. If the number of arguments is odd, the last argument is the default value which is returned when no condition matches. If the number of arguments is even, and no condition matches, the function returns null.

Parameters:: conditions (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.categorize(field)

Groups text messages into categories of similarly formatted text values.

Parameters:: field (Any) – Expression to categorize
Return type:: InstrumentedExpression

elasticsearch.esql.functions.cbrt(number)

Returns the cube root of a number. The input can be any numeric value, the return value is always a double. Cube roots of infinities are null.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.ceil(number)

Round a number up to the nearest integer.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.cidr_match(ip, block_x)

Returns true if the provided IP is contained in one of the provided CIDR blocks.

Parameters:

ip (Any) – IP address of type ip (both IPv4 and IPv6 are supported).
block_x (Any) – CIDR block to test the IP against.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.coalesce(first, rest)

Returns the first of its arguments that is not null. If all arguments are null, it returns null.

Parameters:

first (Any) – Expression to evaluate.
rest (Any) – Other expression to evaluate.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.concat(*strings)

Concatenates two or more strings.

Parameters:: strings (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.cos(angle)

Returns the cosine of an angle.

Parameters:: angle (Any) – An angle, in radians. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.cosh(number)

Returns the hyperbolic cosine of a number.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.count(field)

Returns the total number (count) of input values.

Parameters:: field (Any) – Expression that outputs values to be counted. If omitted, equivalent to COUNT(*) (the number of rows).
Return type:: InstrumentedExpression

elasticsearch.esql.functions.count_distinct(field, precision)

Returns the approximate number of distinct values.

Parameters:

field (Any) – Column or literal for which to count the number of distinct values.
precision (Any) – Precision threshold. The maximum supported value is 40000. Thresholds above this number will have the same effect as a threshold of 40000. The default value is 3000.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.count_distinct_over_time(field, precision)

The count of distinct values over time for a field.

Parameters:

field (Any)
precision (Any) – Precision threshold. The maximum supported value is 40000. Thresholds above this number will have the same effect as a threshold of 40000. The default value is 3000.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.count_over_time(field)

The count over time value of a field.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.date_diff(unit, start_timestamp, end_timestamp)

Subtracts the startTimestamp from the endTimestamp and returns the difference in multiples of unit. If startTimestamp is later than the endTimestamp, negative values are returned.

Parameters:

unit (Any) – Time difference unit
start_timestamp (Any) – A string representing a start timestamp
end_timestamp (Any) – A string representing an end timestamp

Return type:

InstrumentedExpression

elasticsearch.esql.functions.date_extract(date_part, date)

Extracts parts of a date, like year, month, day, hour.

Parameters:

date_part (Any) – Part of the date to extract. Can be: aligned_day_of_week_in_month, aligned_day_of_week_in_year, aligned_week_of_month, aligned_week_of_year, ampm_of_day, clock_hour_of_ampm, clock_hour_of_day, day_of_month, day_of_week, day_of_year, epoch_day, era, hour_of_ampm, hour_of_day, instant_seconds, micro_of_day, micro_of_second, milli_of_day, milli_of_second, minute_of_day, minute_of_hour, month_of_year, nano_of_day, nano_of_second, offset_seconds, proleptic_month, second_of_day, second_of_minute, year, or year_of_era. If null, the function returns null.
date (Any) – Date expression. If null, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.date_format(date, date_format=None)

Returns a string representation of a date, in the provided format.

Parameters:

date_format (Any) – Date format (optional). If no format is specified, the yyyy-MM-dd’T’HH:mm:ss.SSSZ format is used. If null, the function returns null.
date (Any) – Date expression. If null, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.date_parse(date_pattern, date_string)

Returns a date by parsing the second argument using the format specified in the first argument.

Parameters:

date_pattern (Any) – The date format. If null, the function returns null.
date_string (Any) – Date expression as a string. If null or an empty string, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.date_trunc(interval, date)

Rounds down a date to the closest interval since epoch, which starts at 0001-01-01T00:00:00Z.

Parameters:

interval (Any) – Interval; expressed using the timespan literal syntax.
date (Any) – Date expression

Return type:

InstrumentedExpression

elasticsearch.esql.functions.e()

Returns Euler’s number).

Return type:: InstrumentedExpression

elasticsearch.esql.functions.ends_with(str, suffix)

Returns a boolean that indicates whether a keyword string ends with another string.

Parameters:

str (Any) – String expression. If null, the function returns null.
suffix (Any) – String expression. If null, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.exp(number)

Returns the value of e raised to the power of the given number.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.first(value, sort)

Calculates the earliest value of a field.

Parameters:

value (Any)
sort (Any)

Return type:

InstrumentedExpression

elasticsearch.esql.functions.first_over_time(field)

The earliest value of a field, where recency determined by the @timestamp field.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.floor(number)

Round a number down to the nearest integer.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.from_base64(string)

Decode a base64 string.

Parameters:: string (Any) – A base64 string.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.greatest(first, rest)

Returns the maximum value from multiple columns. This is similar to MV_MAX except it is intended to run on multiple columns at once.

Parameters:

first (Any) – First of the columns to evaluate.
rest (Any) – The rest of the columns to evaluate.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.hash(algorithm, input)

Computes the hash of the input using various algorithms such as MD5, SHA, SHA-224, SHA-256, SHA-384, SHA-512.

Parameters:

algorithm (Any) – Hash algorithm to use.
input (Any) – Input to hash.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.hypot(number1, number2)

Returns the hypotenuse of two numbers. The input can be any numeric values, the return value is always a double. Hypotenuses of infinities are null.

Parameters:

number1 (Any) – Numeric expression. If null, the function returns null.
number2 (Any) – Numeric expression. If null, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.ip_prefix(ip, prefix_length_v4, prefix_length_v6)

Truncates an IP to a given prefix length.

Parameters:

ip (Any) – IP address of type ip (both IPv4 and IPv6 are supported).
prefix_length_v4 (Any) – Prefix length for IPv4 addresses.
prefix_length_v6 (Any) – Prefix length for IPv6 addresses.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.knn(field, query, options=None)

Finds the k nearest vectors to a query vector, as measured by a similarity metric. knn function finds nearest vectors through approximate search on indexed dense_vectors.

Parameters:

field (Any) – Field that the query will target.
query (Any) – Vector value to find top nearest neighbours for.
options (Any) – (Optional) kNN additional options as function named parameters.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.kql(query)

Performs a KQL query. Returns true if the provided KQL query string matches the row.

Parameters:: query (Any) – Query string in KQL query string format.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.last(value, sort)

Calculates the latest value of a field.

Parameters:

value (Any)
sort (Any)

Return type:

InstrumentedExpression

elasticsearch.esql.functions.last_over_time(field)

The latest value of a field, where recency determined by the @timestamp field.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.least(first, rest)

Returns the minimum value from multiple columns. This is similar to MV_MIN except it is intended to run on multiple columns at once.

Parameters:

first (Any) – First of the columns to evaluate.
rest (Any) – The rest of the columns to evaluate.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.left(string, length)

Returns the substring that extracts length chars from string starting from the left.

Parameters:

string (Any) – The string from which to return a substring.
length (Any) – The number of characters to return.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.length(string)

Returns the character length of a string.

Parameters:: string (Any) – String expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.locate(string, substring, start)

Returns an integer that indicates the position of a keyword substring within another string. Returns 0 if the substring cannot be found. Note that string positions start from 1.

Parameters:

string (Any) – An input string
substring (Any) – A substring to locate in the input string
start (Any) – The start index

Return type:

InstrumentedExpression

elasticsearch.esql.functions.log(base, number)

Returns the logarithm of a value to a base. The input can be any numeric value, the return value is always a double. Logs of zero, negative numbers, and base of one return null as well as a warning.

Parameters:

base (Any) – Base of logarithm. If null, the function returns null. If not provided, this function returns the natural logarithm (base e) of a value.
number (Any) – Numeric expression. If null, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.log10(number)

Returns the logarithm of a value to base 10. The input can be any numeric value, the return value is always a double. Logs of 0 and negative numbers return null as well as a warning.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.ltrim(string)

Removes leading whitespaces from a string.

Parameters:: string (Any) – String expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.match(field, query, options=None)

Use MATCH to perform a match query on the specified field. Using MATCH is equivalent to using the match query in the Elasticsearch Query DSL.

Parameters:

field (Any) – Field that the query will target.
query (Any) – Value to find in the provided field.
options (Any) – (Optional) Match additional options as function named parameters.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.match_phrase(field, query, options=None)

Use MATCH_PHRASE to perform a match_phrase on the specified field. Using MATCH_PHRASE is equivalent to using the match_phrase query in the Elasticsearch Query DSL.

Parameters:

field (Any) – Field that the query will target.
query (Any) – Value to find in the provided field.
options (Any) – (Optional) MatchPhrase additional options as function named parameters.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.max(field)

The maximum value of a field.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.max_over_time(field)

The maximum over time value of a field.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.md5(input)

Computes the MD5 hash of the input.

Parameters:: input (Any) – Input to hash.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.median(number)

The value that is greater than half of all values and less than half of all values, also known as the 50% PERCENTILE.

Parameters:: number (Any) – Expression that outputs values to calculate the median of.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.median_absolute_deviation(number)

Returns the median absolute deviation, a measure of variability. It is a robust statistic, meaning that it is useful for describing data that may have outliers, or may not be normally distributed. For such data it can be more descriptive than standard deviation. It is calculated as the median of each data point’s deviation from the median of the entire sample. That is, for a random variable X, the median absolute deviation is median(|median(X) - X|).

Parameters:: number (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.min(field)

The minimum value of a field.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.min_over_time(field)

The minimum over time value of a field.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.multi_match(query, *fields, options=None)

Use MULTI_MATCH to perform a multi-match query on the specified field. The multi_match query builds on the match query to allow multi-field queries.

Parameters:

query (Any) – Value to find in the provided fields.
fields (Any) – Fields to use for matching
options (Any) – (Optional) Additional options for MultiMatch, passed as function named parameters

Return type:

InstrumentedExpression

elasticsearch.esql.functions.mv_append(field1, field2)

Concatenates values of two multi-value fields.

Parameters:

field1 (Any)
field2 (Any)

Return type:

InstrumentedExpression

elasticsearch.esql.functions.mv_avg(number)

Converts a multivalued field into a single valued field containing the average of all of the values.

Parameters:: number (Any) – Multivalue expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.mv_concat(string, delim)

Converts a multivalued string expression into a single valued column containing the concatenation of all values separated by a delimiter.

Parameters:

string (Any) – Multivalue expression.
delim (Any) – Delimiter.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.mv_contains(superset, subset)

Checks if all values yielded by the second multivalue expression are present in the values yielded by the first multivalue expression. Returns a boolean. Null values are treated as an empty set.

Parameters:

superset (Any)
subset (Any)

Return type:

InstrumentedExpression

elasticsearch.esql.functions.mv_count(field)

Converts a multivalued expression into a single valued column containing a count of the number of values.

Parameters:: field (Any) – Multivalue expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.mv_dedupe(field)

Remove duplicate values from a multivalued field.

Parameters:: field (Any) – Multivalue expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.mv_first(field)

Converts a multivalued expression into a single valued column containing the first value. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT.

Parameters:: field (Any) – Multivalue expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.mv_last(field)

Converts a multivalue expression into a single valued column containing the last value. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT.

Parameters:: field (Any) – Multivalue expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.mv_max(field)

Converts a multivalued expression into a single valued column containing the maximum value.

Parameters:: field (Any) – Multivalue expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.mv_median(number)

Converts a multivalued field into a single valued field containing the median value.

Parameters:: number (Any) – Multivalue expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.mv_median_absolute_deviation(number)

Converts a multivalued field into a single valued field containing the median absolute deviation. It is calculated as the median of each data point’s deviation from the median of the entire sample. That is, for a random variable X, the median absolute deviation is median(|median(X) - X|).

Parameters:: number (Any) – Multivalue expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.mv_min(field)

Converts a multivalued expression into a single valued column containing the minimum value.

Parameters:: field (Any) – Multivalue expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.mv_percentile(number, percentile)

Converts a multivalued field into a single valued field containing the value at which a certain percentage of observed values occur.

Parameters:

number (Any) – Multivalue expression.
percentile (Any) – The percentile to calculate. Must be a number between 0 and 100. Numbers out of range will return a null instead.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.mv_pseries_weighted_sum(number, p)

Converts a multivalued expression into a single-valued column by multiplying every element on the input list by its corresponding term in P-Series and computing the sum.

Parameters:

number (Any) – Multivalue expression.
p (Any) – It is a constant number that represents the p parameter in the P-Series. It impacts every element’s contribution to the weighted sum.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.mv_slice(field, start, end=None)

Returns a subset of the multivalued field using the start and end index values. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT or MV_SORT.

Parameters:

field (Any) – Multivalue expression. If null, the function returns null.
start (Any) – Start position. If null, the function returns null. The start argument can be negative. An index of -1 is used to specify the last value in the list.
end (Any) – End position(included). Optional; if omitted, the position at start is returned. The end argument can be negative. An index of -1 is used to specify the last value in the list.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.mv_sort(field, order)

Sorts a multivalued field in lexicographical order.

Parameters:

field (Any) – Multivalue expression. If null, the function returns null.
order (Any) – Sort order. The valid options are ASC and DESC, the default is ASC.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.mv_sum(number)

Converts a multivalued field into a single valued field containing the sum of all of the values.

Parameters:: number (Any) – Multivalue expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.mv_zip(string1, string2, delim=None)

Combines the values from two multivalued fields with a delimiter that joins them together.

Parameters:

string1 (Any) – Multivalue expression.
string2 (Any) – Multivalue expression.
delim (Any) – Delimiter. Optional; if omitted, , is used as a default delimiter.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.now()

Returns current date and time.

Return type:: InstrumentedExpression

elasticsearch.esql.functions.percentile(number, percentile)

Returns the value at which a certain percentage of observed values occur. For example, the 95th percentile is the value which is greater than 95% of the observed values and the 50th percentile is the MEDIAN.

Parameters:

number (Any)
percentile (Any)

Return type:

InstrumentedExpression

elasticsearch.esql.functions.pi()

Returns Pi, the ratio of a circle’s circumference to its diameter.

Return type:: InstrumentedExpression

elasticsearch.esql.functions.pow(base, exponent)

Returns the value of base raised to the power of exponent.

Parameters:

base (Any) – Numeric expression for the base. If null, the function returns null.
exponent (Any) – Numeric expression for the exponent. If null, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.present(field)

Returns true if the input expression yields any non-null values within the current aggregation context. Otherwise it returns false.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.present_over_time(field)

Calculates the presence of a field in the output result over time range.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.qstr(query, options=None)

Performs a query string query. Returns true if the provided query string matches the row.

Parameters:

query (Any) – Query string in Lucene query string format.
options (Any) – (Optional) Additional options for Query String as function named parameters.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.rate(field)

The rate of a counter field.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.repeat(string, number)

Returns a string constructed by concatenating string with itself the specified number of times.

Parameters:

string (Any) – String expression.
number (Any) – Number times to repeat.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.replace(string, regex, new_string)

The function substitutes in the string str any match of the regular expression regex with the replacement string newStr.

Parameters:

string (Any) – String expression.
regex (Any) – Regular expression.
new_string (Any) – Replacement string.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.reverse(str)

Returns a new string representing the input string in reverse order.

Parameters:: str (Any) – String expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.right(string, length)

Return the substring that extracts length chars from str starting from the right.

Parameters:

string (Any) – The string from which to returns a substring.
length (Any) – The number of characters to return.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.round(number, decimals=None)

Rounds a number to the specified number of decimal places. Defaults to 0, which returns the nearest integer. If the precision is a negative number, rounds to the number of digits left of the decimal point.

Parameters:

number (Any) – The numeric value to round. If null, the function returns null.
decimals (Any) – The number of decimal places to round to. Defaults to 0. If null, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.round_to(field, points)

Rounds down to one of a list of fixed points.

Parameters:

field (Any) – The numeric value to round. If null, the function returns null.
points (Any) – Remaining rounding points. Must be constants.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.rtrim(string)

Removes trailing whitespaces from a string.

Parameters:: string (Any) – String expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.sample(field, limit)

Collects sample values for a field.

Parameters:

field (Any) – The field to collect sample values for.
limit (Any) – The maximum number of values to collect.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.scalb(d, scale_factor)

Returns the result of d * 2 ^ scaleFactor, Similar to Java’s scalb function. Result is rounded as if performed by a single correctly rounded floating-point multiply to a member of the double value set.

Parameters:

d (Any) – Numeric expression for the multiplier. If null, the function returns null.
scale_factor (Any) – Numeric expression for the scale factor. If null, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.sha1(input)

Computes the SHA1 hash of the input.

Parameters:: input (Any) – Input to hash.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.sha256(input)

Computes the SHA256 hash of the input.

Parameters:: input (Any) – Input to hash.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.signum(number)

Returns the sign of the given number. It returns -1 for negative numbers, 0 for 0 and 1 for positive numbers.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.sin(angle)

Returns the sine of an angle.

Parameters:: angle (Any) – An angle, in radians. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.sinh(number)

Returns the hyperbolic sine of a number.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.space(number)

Returns a string made of number spaces.

Parameters:: number (Any) – Number of spaces in result.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.split(string, delim)

Split a single valued string into multiple strings.

Parameters:

string (Any) – String expression. If null, the function returns null.
delim (Any) – Delimiter. Only single byte delimiters are currently supported.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.sqrt(number)

Returns the square root of a number. The input can be any numeric value, the return value is always a double. Square roots of negative numbers and infinities are null.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_centroid_agg(field)

Calculate the spatial centroid over a field with spatial point geometry type.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_contains(geom_a, geom_b)

Returns whether the first geometry contains the second geometry. This is the inverse of the ST_WITHIN function.

Parameters:

geom_a (Any) – Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null.
geom_b (Any) – Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null. The second parameter must also have the same coordinate system as the first. This means it is not possible to combine geo_* and cartesian_* parameters.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.st_disjoint(geom_a, geom_b)

Returns whether the two geometries or geometry columns are disjoint. This is the inverse of the ST_INTERSECTS function. In mathematical terms: ST_Disjoint(A, B) ⇔ A ⋂ B = ∅

Parameters:

geom_a (Any) – Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null.
geom_b (Any) – Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null. The second parameter must also have the same coordinate system as the first. This means it is not possible to combine geo_* and cartesian_* parameters.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.st_distance(geom_a, geom_b)

Computes the distance between two points. For cartesian geometries, this is the pythagorean distance in the same units as the original coordinates. For geographic geometries, this is the circular distance along the great circle in meters.

Parameters:

geom_a (Any) – Expression of type geo_point or cartesian_point. If null, the function returns null.
geom_b (Any) – Expression of type geo_point or cartesian_point. If null, the function returns null. The second parameter must also have the same coordinate system as the first. This means it is not possible to combine geo_point and cartesian_point parameters.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.st_envelope(geometry)

Determines the minimum bounding box of the supplied geometry.

Parameters:: geometry (Any) – Expression of type geo_point, geo_shape, cartesian_point or cartesian_shape. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_extent_agg(field)

Calculate the spatial extent over a field with geometry type. Returns a bounding box for all values of the field.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_geohash(geometry, precision, bounds=None)

Calculates the geohash of the supplied geo_point at the specified precision. The result is long encoded. Use ST_GEOHASH_TO_STRING to convert the result to a string. These functions are related to the geo_grid query and the geohash_grid aggregation.

Parameters:

geometry (Any) – Expression of type geo_point. If null, the function returns null.
precision (Any) – Expression of type integer. If null, the function returns null. Valid values are between 1 and 12.
bounds (Any) – Optional bounds to filter the grid tiles, a geo_shape of type BBOX. Use ST_ENVELOPE if the geo_shape is of any other type.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.st_geohash_to_long(grid_id)

Converts an input value representing a geohash grid-ID in string format into a long.

Parameters:: grid_id (Any) – Input geohash grid-id. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_geohash_to_string(grid_id)

Converts an input value representing a geohash grid-ID in long format into a string.

Parameters:: grid_id (Any) – Input geohash grid-id. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_geohex(geometry, precision, bounds=None)

Calculates the geohex, the H3 cell-id, of the supplied geo_point at the specified precision. The result is long encoded. Use ST_GEOHEX_TO_STRING to convert the result to a string. These functions are related to the geo_grid query and the geohex_grid aggregation.

Parameters:

geometry (Any) – Expression of type geo_point. If null, the function returns null.
precision (Any) – Expression of type integer. If null, the function returns null. Valid values are between 0 and 15.
bounds (Any) – Optional bounds to filter the grid tiles, a geo_shape of type BBOX. Use ST_ENVELOPE if the geo_shape is of any other type.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.st_geohex_to_long(grid_id)

Converts an input value representing a geohex grid-ID in string format into a long.

Parameters:: grid_id (Any) – Input geohex grid-id. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_geohex_to_string(grid_id)

Converts an input value representing a Geohex grid-ID in long format into a string.

Parameters:: grid_id (Any) – Input Geohex grid-id. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_geotile(geometry, precision, bounds=None)

Calculates the geotile of the supplied geo_point at the specified precision. The result is long encoded. Use ST_GEOTILE_TO_STRING to convert the result to a string. These functions are related to the geo_grid query and the geotile_grid aggregation.

Parameters:

geometry (Any) – Expression of type geo_point. If null, the function returns null.
precision (Any) – Expression of type integer. If null, the function returns null. Valid values are between 0 and 29.
bounds (Any) – Optional bounds to filter the grid tiles, a geo_shape of type BBOX. Use ST_ENVELOPE if the geo_shape is of any other type.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.st_geotile_to_long(grid_id)

Converts an input value representing a geotile grid-ID in string format into a long.

Parameters:: grid_id (Any) – Input geotile grid-id. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_geotile_to_string(grid_id)

Converts an input value representing a geotile grid-ID in long format into a string.

Parameters:: grid_id (Any) – Input geotile grid-id. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_intersects(geom_a, geom_b)

Returns true if two geometries intersect. They intersect if they have any point in common, including their interior points (points along lines or within polygons). This is the inverse of the ST_DISJOINT function. In mathematical terms: ST_Intersects(A, B) ⇔ A ⋂ B ≠ ∅

Parameters:

geom_a (Any) – Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null.
geom_b (Any) – Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null. The second parameter must also have the same coordinate system as the first. This means it is not possible to combine geo_* and cartesian_* parameters.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.st_within(geom_a, geom_b)

Returns whether the first geometry is within the second geometry. This is the inverse of the ST_CONTAINS function.

Parameters:

geom_a (Any) – Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null.
geom_b (Any) – Expression of type geo_point, cartesian_point, geo_shape or cartesian_shape. If null, the function returns null. The second parameter must also have the same coordinate system as the first. This means it is not possible to combine geo_* and cartesian_* parameters.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.st_x(point)

Extracts the x coordinate from the supplied point. If the points is of type geo_point this is equivalent to extracting the longitude value.

Parameters:: point (Any) – Expression of type geo_point or cartesian_point. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_xmax(point)

Extracts the maximum value of the x coordinates from the supplied geometry. If the geometry is of type geo_point or geo_shape this is equivalent to extracting the maximum longitude value.

Parameters:: point (Any) – Expression of type geo_point, geo_shape, cartesian_point or cartesian_shape. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_xmin(point)

Extracts the minimum value of the x coordinates from the supplied geometry. If the geometry is of type geo_point or geo_shape this is equivalent to extracting the minimum longitude value.

Parameters:: point (Any) – Expression of type geo_point, geo_shape, cartesian_point or cartesian_shape. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_y(point)

Extracts the y coordinate from the supplied point. If the points is of type geo_point this is equivalent to extracting the latitude value.

Parameters:: point (Any) – Expression of type geo_point or cartesian_point. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_ymax(point)

Extracts the maximum value of the y coordinates from the supplied geometry. If the geometry is of type geo_point or geo_shape this is equivalent to extracting the maximum latitude value.

Parameters:: point (Any) – Expression of type geo_point, geo_shape, cartesian_point or cartesian_shape. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.st_ymin(point)

Extracts the minimum value of the y coordinates from the supplied geometry. If the geometry is of type geo_point or geo_shape this is equivalent to extracting the minimum latitude value.

Parameters:: point (Any) – Expression of type geo_point, geo_shape, cartesian_point or cartesian_shape. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.starts_with(str, prefix)

Returns a boolean that indicates whether a keyword string starts with another string.

Parameters:

str (Any) – String expression. If null, the function returns null.
prefix (Any) – String expression. If null, the function returns null.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.std_dev(number)

The population standard deviation of a numeric field.

Parameters:: number (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.substring(string, start, length=None)

Returns a substring of a string, specified by a start position and an optional length.

Parameters:

string (Any) – String expression. If null, the function returns null.
start (Any) – Start position.
length (Any) – Length of the substring from the start position. Optional; if omitted, all positions after start are returned.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.sum(number)

The sum of a numeric expression.

Parameters:: number (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.sum_over_time(field)

Calculates the sum over time value of a field.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.tan(angle)

Returns the tangent of an angle.

Parameters:: angle (Any) – An angle, in radians. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.tanh(number)

Returns the hyperbolic tangent of a number.

Parameters:: number (Any) – Numeric expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.tau()

Returns the ratio of a circle’s circumference to its radius.

Return type:: InstrumentedExpression

elasticsearch.esql.functions.term(field, query)

Performs a Term query on the specified field. Returns true if the provided term matches the row.

Parameters:

field (Any) – Field that the query will target.
query (Any) – Term you wish to find in the provided field.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.text_embedding(text, inference_id)

Generates dense vector embeddings from text input using a specified inference endpoint. Use this function to generate query vectors for KNN searches against your vectorized data or others dense vector based operations.

Parameters:

text (Any)
inference_id (Any)

Return type:

InstrumentedExpression

elasticsearch.esql.functions.to_aggregate_metric_double(number)

Encode a numeric to an aggregate_metric_double.

Parameters:: number (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_base64(string)

Encode a string to a base64 string.

Parameters:: string (Any) – A string.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_boolean(field)

Converts an input value to a boolean value. A string value of true will be case-insensitive converted to the Boolean true. For anything else, including the empty string, the function will return false. The numerical value of 0 will be converted to false, anything else will be converted to true.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_cartesianpoint(field)

Converts an input value to a cartesian_point value. A string will only be successfully converted if it respects the WKT Point format.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_cartesianshape(field)

Converts an input value to a cartesian_shape value. A string will only be successfully converted if it respects the WKT format.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_date_nanos(field)

Converts an input to a nanosecond-resolution date value (aka date_nanos).

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_dateperiod(field)

Converts an input value into a date_period value.

Parameters:: field (Any) – Input value. The input is a valid constant date period expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_datetime(field)

Converts an input value to a date value. A string will only be successfully converted if it’s respecting the format yyyy-MM-dd’T’HH:mm:ss.SSS’Z’. To convert dates in other formats, use DATE_PARSE.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_degrees(number)

Converts a number in radians to degrees).

Parameters:: number (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_double(field)

Converts an input value to a double value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to double. Boolean true will be converted to double 1.0, false to 0.0.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_geohash(field)

Converts an input value to a geohash value. A string will only be successfully converted if it respects the geohash format, as described for the geohash grid aggregation.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_geohex(field)

Converts an input value to a geohex value. A string will only be successfully converted if it respects the geohex format, as described for the geohex grid aggregation.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_geopoint(field)

Converts an input value to a geo_point value. A string will only be successfully converted if it respects the WKT Point format.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_geoshape(field)

Converts an input value to a geo_shape value. A string will only be successfully converted if it respects the WKT format.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_geotile(field)

Converts an input value to a geotile value. A string will only be successfully converted if it respects the geotile format, as described for the geotile grid aggregation.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_integer(field)

Converts an input value to an integer value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to integer. Boolean true will be converted to integer 1, false to 0.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_ip(field, options=None)

Converts an input string to an IP value.

Parameters:

field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
options (Any) – (Optional) Additional options.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.to_long(field)

Converts an input value to a long value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to long. Boolean true will be converted to long 1, false to 0.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_lower(str)

Returns a new string representing the input string converted to lower case.

Parameters:: str (Any) – String expression. If null, the function returns null. The input can be a single-valued column or expression, or a multi-valued column or expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_radians(number)

Converts a number in degrees) to radians.

Parameters:: number (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_string(field)

Converts an input value into a string.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_timeduration(field)

Converts an input value into a time_duration value.

Parameters:: field (Any) – Input value. The input is a valid constant time duration expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_unsigned_long(field)

Converts an input value to an unsigned long value. If the input parameter is of a date type, its value will be interpreted as milliseconds since the Unix epoch, converted to unsigned long. Boolean true will be converted to unsigned long 1, false to 0.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_upper(str)

Returns a new string representing the input string converted to upper case.

Parameters:: str (Any) – String expression. If null, the function returns null. The input can be a single-valued column or expression, or a multi-valued column or expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.to_version(field)

Converts an input string to a version value.

Parameters:: field (Any) – Input value. The input can be a single- or multi-valued column or an expression.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.top(field, limit, order)

Collects the top values for a field. Includes repeated values.

Parameters:

field (Any) – The field to collect the top values for.
limit (Any) – The maximum number of values to collect.
order (Any) – The order to calculate the top values. Either asc or desc.

Return type:

InstrumentedExpression

elasticsearch.esql.functions.trim(string)

Removes leading and trailing whitespaces from a string.

Parameters:: string (Any) – String expression. If null, the function returns null.
Return type:: InstrumentedExpression

elasticsearch.esql.functions.values(field)

Returns unique values as a multivalued field. The order of the returned values isn’t guaranteed. If you need the values returned in order use MV_SORT.

Parameters:: field (Any)
Return type:: InstrumentedExpression

elasticsearch.esql.functions.weighted_avg(number, weight)

The weighted average of a numeric expression.

Parameters:

number (Any) – A numeric value.
weight (Any) – A numeric weight.

Return type:

InstrumentedExpression