elasticsearch/docs/reference/esql/esql-processing-commands.asciidoc

[[esql-processing-commands]]
== ESQL processing commands

Processing commands change an input table by adding, removing, or changing rows
and columns.

image::images/esql/processing-command.svg[A processing command changes an input table,align="center"]

[discrete]
[[esql-limit]]
=== `LIMIT`

The `LIMIT` processing command enables you to limit the number of rows:

[source,esql]
----
FROM employees
| LIMIT 5
----

[discrete]
[[esql-project]]
=== `PROJECT`

The `PROJECT` command enables you to change:

* the columns that are returned,
* the order in which they are returned,
* and the name with which they are returned.

To limit the columns that are returned, use a comma-separated list of column
names. The columns are returned in the specified order:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
----

Rather than specify each column by name, you can use wildcards to return all
columns with a name that matches a pattern:

[source,esql]
----
FROM employees
| PROJECT h*
----

The asterisk wildcard (`*`) by itself translates to all columns that do not
match the other arguments. This query will first return all columns with a name
that starts with an h, followed by all other columns:

[source,esql]
----
FROM employees
| PROJECT h*, *
----

Use a dash to specify columns you do not want returned:

[source,esql]
----
FROM employees
| PROJECT -h*
----

Use `=` to rename columns:

[source,esql]
----
FROM employees
| PROJECT current_employee = still_hired, *
----

[discrete]
[[esql-eval]]
=== `EVAL`
`EVAL` enables you to add new columns to the end of the table:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| EVAL height_feet = height * 3.281, height_cm = height * 100
----

If the specified column already exists, the existing column will be dropped, and
the new column will be appended to the table:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| EVAL height = height * 3.281
----

[discrete]
==== Functions 
`EVAL` supports various functions for calculating values. Refer to
<<esql-functions,Functions>> for more information.

[discrete]
[[esql-sort]]
=== `SORT`
Use the `SORT` command to sort rows on one or more fields:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| SORT height
----

The default sort order is ascending. Set an explicit sort order using `ASC` or
`DESC`:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| SORT height desc
----

If two rows have the same sort key, the original order will be preserved. You
can provide additional sort expressions to act as tie breakers:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| SORT height desc, first_name ASC
----

[discrete]
==== `null` values 
By default, `null` values are treated as being larger than any other value. With
an ascending sort order, `null` values are sorted last, and with a descending
sort order, `null` values are sorted first. You can change that by providing
`NULLS FIRST` or `NULLS LAST`:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| SORT first_name ASC NULLS FIRST
----

[discrete]
[[esql-where]]
=== `WHERE`

Use `WHERE` to produce a table that contains all the rows from the input table
for which the provided condition evaluates to `true`:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, still_hired
| WHERE still_hired == true
----

Which, because `still_hired` is a boolean field, can be simplified to:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, still_hired
| WHERE still_hired
----

[discrete]
==== Operators
These comparison operators are supported:

* equality: `==`
* inequality: `!=`
* comparison: 
** less than: `<`
** less than or equal: `<=`
** larger than: `>`
** larger than or equal: `>=`

You can use the following boolean operators:

* `AND`
* `OR`
* `NOT`

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height, still_hired
| WHERE height > 2 AND NOT still_hired
----

[discrete]
==== Functions 
`WHERE` supports various functions for calculating values. Refer to
<<esql-functions,Functions>> for more information.

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, height
| WHERE length(first_name) < 4
----

[discrete]
[[esql-stats-by]]
=== `STATS ... BY`
Use `STATS ... BY` to group rows according to a common value and calculate one
or more aggregated values over the grouped rows. 

[source,esql]
----
FROM employees
| STATS count = COUNT(languages) BY languages
----

If `BY` is omitted, the output table contains exactly one row with the
aggregations applied over the entire dataset:

[source,esql]
----
FROM employees
| STATS avg_lang = AVG(languages)
----

It's possible to calculate multiple values:

[source,esql]
----
FROM employees
| STATS avg_lang = AVG(languages), max_lang = MAX(languages)
----

It's also possible to group by multiple values (only supported for long and
keyword family fields):

[source,esql]
----
FROM employees
| EVAL hired = DATE_FORMAT(hire_date, "YYYY")
| STATS avg_salary = AVG(salary) BY hired, languages.long
| EVAL avg_salary = ROUND(avg_salary)
| SORT hired, languages.long
----

The following aggregation functions are supported:

* `AVG`
* `COUNT`
* `MAX`
* `MEDIAN`
* `MEDIAN_ABSOLUTE_DEVIATION`
* `MIN`
* `SUM`