Loading

ES|QL multivalue functions

ES|QL fields can contain more than one value. Multivalue functions let you manipulate, filter, and reduce those values within a query without needing to normalize the data first.

ES|QL supports these multivalue functions, grouped by category:

Functions to add, remove, combine, or reorder multi-value inputs. All these functions return multi-values.

  • MV_APPEND: Adds a value or multi-value to the end of a multi-value.
  • MV_DIFFERENCE : Removes a value or multi-value from a multi-value.
  • MV_DEDUPE: Removes duplicate values from a multi-value.
  • MV_SORT: Sorts the values in a multi-value.
  • MV_INTERSECTION : Keeps the values present in both multi-values.
  • MV_UNION : Keeps all the unique values present in either multi-value.
  • MV_SLICE: Keeps the values in a multi-value between the given start and end indexes.

Functions that map a multi-value to a new multi-value.

  • MV_ZIP: Combines values from two multi-values at each position using a delimiter.

Functions that return a boolean value based on the properties of a multi-value. These provide optimized shorthand expressions for common operations.

Note

null is interpreted as an empty set. To reject "unknown" or absent values, check for null before calling the function.

WHERE field2 IS NOT null AND MV_CONTAINS(field1, field2)
  • MV_CONTAINS : Tests if a multi-value contains all of the provided values.
  • MV_INTERSECTS : Tests if a multi-value contains any of the provided values.

Functions that reduce a multi-value to a single value.

  • MV_COUNT: Counts the number of values in a multi-value.

Functions that reduce a multi-value to a single value by keeping one of the existing values.

  • MV_FIRST: returns the first value in a multi-value.
  • MV_LAST: returns the last value in a multi-value.
  • MV_MIN: returns the smallest value in a multi-value.
  • MV_MAX: returns the largest value in a multi-value.

Functions that reduce a multi-value to a single value by aggregating the values.

Functions that calculate a single value from a numeric multi-value. (double, integer, long, etc.)

  • MV_AVG: calculates the average of all values in a multi-value.
  • MV_SUM: calculates the sum of all values in a multi-value.
  • MV_MEDIAN: calculates the median of all values in a multi-value.
  • MV_MEDIAN_ABSOLUTE_DEVIATION: calculates the median absolute deviation of all values in a multi-value.
  • MV_PERCENTILE: calculates the percentile of all values in a multi-value.
  • MV_PSERIES_WEIGHTED_SUM: calculates the weighted sum of all values in a multi-value.

Functions that calculate a single value from a string multi-value. (text, keyword)

  • MV_CONCAT: concatenates all values into a single string.