ContextQMD
Back to Elasticsearch

{{esql}} multivalue functions [esql-mv-functions]

docs/reference/query-languages/esql/functions-operators/mv-functions.md

9.5.05.0 KB
Original Source

{{esql}} multivalue functions [esql-mv-functions]

{{esql}} 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.

{{esql}} supports these multivalue functions, grouped by category:

Manipulation functions

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

  • MV_APPEND

    :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_append.md :::

  • MV_DIFFERENCE {applies_to}stack: preview 9.4 {applies_to}serverless: preview :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_difference.md :::

  • MV_DEDUPE :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_dedupe.md :::

  • MV_SORT :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_sort.md :::

  • MV_INTERSECTION {applies_to}stack: preview 9.3 {applies_to}serverless: preview :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_intersection.md :::

  • MV_UNION {applies_to}stack: preview 9.4 {applies_to}serverless: preview :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_union.md :::

  • MV_SLICE :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_slice.md :::

Transformation functions

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

  • MV_ZIP :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_zip.md :::

Filter and predicate functions

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

  • MV_CONTAINS {applies_to}stack: preview 9.2 {applies_to}serverless: preview :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_contains.md :::
  • MV_INTERSECTS {applies_to}stack: preview 9.3 {applies_to}serverless: preview :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_intersects.md :::

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

esql
WHERE field2 IS NOT null AND MV_CONTAINS(field1, field2)

:::

Reduction functions

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

General functions

  • MV_COUNT :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_count.md :::

Selection functions

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

  • MV_FIRST :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_first.md :::
  • MV_LAST :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_last.md :::
  • MV_MIN :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_min.md :::
  • MV_MAX :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_max.md :::

Aggregation functions

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

Numeric aggregation functions

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

  • MV_AVG :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_avg.md :::
  • MV_SUM :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_sum.md :::
  • MV_MEDIAN :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_median.md :::
  • MV_MEDIAN_ABSOLUTE_DEVIATION :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_median_absolute_deviation.md :::
  • MV_PERCENTILE :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_percentile.md :::
  • MV_PSERIES_WEIGHTED_SUM :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_pseries_weighted_sum.md :::

String aggregation functions

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

  • MV_CONCAT :::{include} ../_snippets/generated/x-pack-esql/functions/briefSummary/mv_concat.md :::