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

[[esql-processing-commands]]
== Processing commands

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

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

ESQL supports these processing commands:

* <<esql-dissect>>
* <<esql-drop>>
* <<esql-eval>>
* <<esql-grok>>
* <<esql-limit>>
* <<esql-project>>
* <<esql-rename>>
* <<esql-sort>>
* <<esql-stats-by>>
* <<esql-where>>


[[esql-dissect]]
=== `DISSECT`

`DISSECT` enables you to extract structured data out of a string. `DISSECT`
matches the string against a delimiter-based pattern, and extracts the specified
keys as columns. 

Refer to the <<dissect-processor,dissect processor documentation>> for the
syntax of dissect patterns.

[source,esql]
----
ROW a = "1953-01-23T12:15:00Z - some text - 127.0.0.1" 
| DISSECT a "%{Y}-%{M}-%{D}T%{h}:%{m}:%{s}Z - %{msg} - %{ip}"
----

[[esql-drop]]
=== `DROP`

Use `DROP` to remove columns from a table:

[source,esql]
----
FROM employees
| DROP height
----

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

[source,esql]
----
FROM employees
| DROP height*
----

[[esql-eval]]
=== `EVAL`
`EVAL` enables you to add new columns to the end of a 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.

[[esql-grok]]
=== `GROK`

`GROK` enables you to extract structured data out of a string. `GROK` matches
the string against patterns, based on regular expressions, and extracts the
specified patterns as columns. 

Refer to the <<grok-processor,grok processor documentation>> for the syntax for
of grok patterns.

[source,esql]
----
ROW a = "12 15.5 15.6 true" 
| GROK a "%{NUMBER:b:int} %{NUMBER:c:float} %{NUMBER:d:double} %{WORD:e:boolean}"
----

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

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

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

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

The `PROJECT` command enables you to specify what columns are returned and the
order in 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*, *
----

[[esql-rename]]
=== `RENAME`

Use `RENAME` to rename a column. If a column with the new name already exists,
it will be replaced by the new column.

[source,esql]
----
FROM employees
| PROJECT first_name, last_name, still_hired
| RENAME employed = still_hired
----

Multiple columns can be renamed with a single `RENAME` command:

[source,esql]
----
FROM employees
| PROJECT first_name, last_name
| RENAME fn = first_name, ln = last_name
----

[[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
----

[[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`

[[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
----