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.

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