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 always keeps exactly one row per unique combination of all columns in scope. Use LIMIT ... BY directly for the following two cases:

• Keeping more than one copy of each duplicate. To retain up to N rows per unique combination instead of just one, use LIMIT N BY <all columns>. Note that LIMIT ... BY does not support wildcards, so you must list every column explicitly:

  FROM employees
  | WHERE emp_no IN (10001, 10003, 10007, 10008, 10010)
  | KEEP languages
  | LIMIT 2 BY languages
  | SORT languages
  

  languages:integer
  2
  2
  4
  4

• Deduplicating on a subset of the columns. To treat rows as duplicates based on only some of the columns, while still returning the remaining columns, list just those columns in the BY clause. Precede it with a SORT to control which row is kept for each group:

  FROM employees
  | WHERE emp_no IN (10001, 10002, 10003, 10005, 10006)
  | SORT last_name
  | LIMIT 1 BY gender
  | KEEP first_name, last_name, gender
  

  first_name:keyword	last_name:keyword	gender:keyword
  Parto	Bamford	M
  Anneke	Preusig	F

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.

Columns of an unsupported type are ignored when comparing rows, and DEDUP does not fail because of them. This includes fields mapped to incompatible types across the indices of a FROM (a union type): such a field is returned as null until it is converted, so it cannot tell rows apart. For example, if emp_no is integer in one index and long in another, FROM employees, employees_incompatible | KEEP emp_no | DEDUP returns a single row. A later conversion such as EVAL e = emp_no::long does not bring the removed rows back. To deduplicate on the value of such a field, convert it first and then DEDUP: EVAL e = emp_no::long | DROP emp_no | DEDUP.

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

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