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

    Appends two multi-value fields together.

  • MV_DIFFERENCE

    Returns values from first field not in second field.

  • MV_DEDUPE

    Removes duplicate values from a multi-value field.

  • MV_SORT

    Sorts the values in a multi-value field.

  • MV_INTERSECTION

    Returns values present in both multi-value fields.

  • MV_UNION

    Returns all unique values from the combined multi-value fields.

  • MV_SLICE

    Returns a subset of a multi-value field using start and end indexes.

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

  • MV_ZIP

    Combines values from two multi-value fields with a delimiter.

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

  • MV_CONTAINS

    Checks if one multi-value field contains all values from another.

  • MV_GREATER

    Checks whether a multivalue field has any value greater than a bound.

  • MV_INTERSECTS

    Checks if any value from one multi-value exists in another.

  • MV_LESS

    Checks whether a multivalue field has any value less than a bound.

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)

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

  • MV_COUNT

    Counts the values in a multi-value field.

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

  • MV_FIRST

    Returns the first value from a multi-value field.

  • MV_LAST

    Returns the last value from a multi-value field.

  • MV_MIN

    Returns the smallest value from a multi-value field.

  • MV_MAX

    Returns the largest value from a multi-value field.

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

    Averages the values in a multi-value field.

  • MV_SUM

    Calculates the sum of all values in a multi-value field.

  • MV_MEDIAN

    Calculates the median of all values in a multi-value field.

  • MV_MEDIAN_ABSOLUTE_DEVIATION

    Calculates the median absolute deviation of a multi-value field.

  • MV_PERCENTILE

    Calculates the percentile of all values in a multi-value field.

  • MV_PSERIES_WEIGHTED_SUM

    Calculates the P-series weighted sum of a multi-value field.

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

  • MV_CONCAT

    Concatenates multi-value strings with a delimiter.