Creating ES|QL Expressions and Conditions

IMPORTANT: This documentation is no longer updated. Refer to Elastic's version policy and the latest documentation.

Creating ES|QL Expressions and Conditions

The ES|QL query builder for Python provides two ways to create expressions and conditions in ES|QL queries.

The simplest option is to provide all ES|QL expressions and conditionals as strings. The following example uses this approach to add two calculated columns to the results using the EVAL command:

from elasticsearch.esql import ESQL

# FROM employees
# | SORT emp_no
# | KEEP first_name, last_name, height
# | EVAL height_feet = height * 3.281, height_cm = height * 100
query = (
    ESQL.from_("employees")
    .sort("emp_no")
    .keep("first_name", "last_name", "height")
    .eval(height_feet="height * 3.281", height_cm="height * 100")
)

A more advanced alternative is to replace the strings with Python expressions, which are automatically translated to ES|QL when the query object is rendered to a string. The following example is functionally equivalent to the one above:

from elasticsearch.esql import ESQL, E

# FROM employees
# | SORT emp_no
# | KEEP first_name, last_name, height
# | EVAL height_feet = height * 3.281, height_cm = height * 100
query = (
    ESQL.from_("employees")
    .sort("emp_no")
    .keep("first_name", "last_name", "height")
    .eval(height_feet=E("height") * 3.281, height_cm=E("height") * 100)
)

Here the E() helper function is used as a wrapper to the column name that initiates an ES|QL expression. The E() function transforms the given column into an ES|QL expression that can be modified with Python operators.

Here is a second example, which uses a conditional expression in the WHERE command:

from elasticsearch.esql import ESQL

# FROM employees
# | KEEP first_name, last_name, height
# | WHERE first_name == "Larry"
query = (
    ESQL.from_("employees")
    .keep("first_name", "last_name", "height")
    .where('first_name == "Larry"')
)

Using Python syntax, the condition can be rewritten as follows:

from elasticsearch.esql import ESQL, E

# FROM employees
# | KEEP first_name, last_name, height
# | WHERE first_name == "Larry"
query = (
    ESQL.from_("employees")
    .keep("first_name", "last_name", "height")
    .where(E("first_name") == "Larry")
)

Preventing injection attacks

edit

ES|QL, like most query languages, is vulnerable to code injection attacks if untrusted data provided by users is added to a query. To eliminate this risk, ES|QL allows untrusted data to be given separately from the query as parameters.

Continuing with the example above, let’s assume that the application needs a find_employee_by_name() function that searches for the name given as an argument. If this argument is received by the application from users, then it is considered untrusted and should not be added to the query directly. Here is how to code the function in a secure manner:

def find_employee_by_name(name):
    query = (
        ESQL.from_("employees")
        .keep("first_name", "last_name", "height")
        .where(E("first_name") == E("?"))
    )
    return client.esql.query(query=query, params=[name])

Here the part of the query in which the untrusted data needs to be inserted is replaced with a parameter, which in ES|QL is defined by the question mark. When using Python expressions, the parameter must be given as E("?") so that it is treated as an expression and not as a literal string.

The list of values given in the params argument to the query endpoint are assigned in order to the parameters defined in the query.

« Adding processing commands Using ES|QL functions »