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

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

++++
<titleabbrev>Processing commands</titleabbrev>
++++
:keywords: {es}, ESQL, {es} query language, processing commands
:description: ESQL processing commands change an input table by adding, removing, or changing rows and columns.

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-mv_expand>>
* <<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-mv_expand]]
=== `MV_EXPAND`

The `MV_EXPAND` processing command expands multivalued fields into one row per value, duplicating other fields:

[source,esql]
----
include::{esql-specs}/mv_expand.csv-spec[tag=simple]
----

[%header,format=dsv,separator=|]
|===
include::{esql-specs}/mv_expand.csv-spec[tag=simple-result]
|===

[[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`
* `COUNT_DISTINCT`
* `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, if `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

Refer to <<esql-operators>> for an overview of the supported operators.

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