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_INTERSECTS

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

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.