ES|QL DEDUP command
DEDUP removes duplicate rows from a result set, keeping only one row per unique combination of values across all columns.
DEDUP
DEDUP takes no arguments. It compares all columns currently in scope and discards any row that is an exact duplicate of a previously seen row. Null values are treated as equal for the purpose of deduplication.
DEDUP is equivalent to LIMIT 1 BY <all columns>.
DEDUP cannot be used when any column in scope has one of the following types: aggregate_metric_double, counter types (counter_long, counter_integer, counter_double), or date_range. Attempting to do so results in a validation error.
Full-text search functions (such as MATCH or KQL) cannot appear after DEDUP in the same pipeline.
Remove duplicate values from a single column:
FROM employees
| WHERE emp_no IN (10001, 10002, 10003, 10005, 10006)
| KEEP gender
| DEDUP
| SORT gender
| gender:keyword |
|---|
| F |
| M |
Remove rows that are duplicates across multiple columns:
FROM employees
| WHERE emp_no IN (10001, 10002, 10010, 10011)
| KEEP gender, languages
| DEDUP
| SORT gender NULLS LAST, languages
| gender:keyword | languages:integer |
|---|---|
| F | 5 |
| M | 2 |
| null | 4 |
| null | 5 |