[[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]] === `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 <> 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 <> 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 <> 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 <> for more information. [source,esql] ---- FROM employees | PROJECT first_name, last_name, height | WHERE length(first_name) < 4 ----