ES|QL multivalue functions

Serverless Stack

ES|QL supports these multivalue functions:

• MV_APPEND
• MV_AVG
• MV_CONCAT
• MV_CONTAINS Stack Preview Serverless Preview
• MV_COUNT
• MV_DEDUPE
• MV_FIRST
• MV_LAST
• MV_MAX
• MV_MEDIAN
• MV_MEDIAN_ABSOLUTE_DEVIATION
• MV_MIN
• MV_PERCENTILE
• MV_PSERIES_WEIGHTED_SUM
• MV_SORT
• MV_SLICE
• MV_SUM
• MV_ZIP

Syntax

×

Parameters

field1

field2

Description

Concatenates values of two multi-value fields.

Supported types

Example

FROM employees
| WHERE emp_no == 10039 OR emp_no == 10040
| SORT emp_no
| EVAL dates = MV_APPEND(birth_date, hire_date)
| KEEP emp_no, birth_date, hire_date, dates
		

Syntax

×

Parameters

number

Multivalue expression.

Description

Converts a multivalued field into a single valued field containing the average of all of the values.

Supported types

Example

ROW a=[3, 5, 1, 6]
| EVAL avg_a = MV_AVG(a)
		

Syntax

×

Parameters

string

Multivalue expression.

delim

Delimiter.

Description

Converts a multivalued string expression into a single valued column containing the concatenation of all values separated by a delimiter.

Supported types

Examples

ROW a=["foo", "zoo", "bar"]
| EVAL j = MV_CONCAT(a, ", ")
		

To concat non-string columns, call TO_STRING first:

ROW a=[10, 9, 8]
| EVAL j = MV_CONCAT(TO_STRING(a), ", ")
		

Stack Preview 9.2.0

Syntax

×

Parameters

superset

Multivalue expression.

subset

Multivalue expression.

Description

Checks if all values yielded by the second multivalue expression are present in the values yielded by the first multivalue expression. Returns a boolean. Null values are treated as an empty set.

Supported types

Examples

ROW set = ["a", "b", "c"], element = "a"
| EVAL set_contains_element = mv_contains(set, element)
		

ROW setA = ["a","c"], setB = ["a", "b", "c"]
| EVAL a_subset_of_b = mv_contains(setB, setA)
| EVAL b_subset_of_a = mv_contains(setA, setB)
		

FROM airports
| WHERE mv_contains(type, ["major","military"]) AND scalerank == 9
| KEEP scalerank, name, country
		

Syntax

×

Parameters

field

Multivalue expression.

Description

Converts a multivalued expression into a single valued column containing a count of the number of values.

Supported types

Example

ROW a=["foo", "zoo", "bar"]
| EVAL count_a = MV_COUNT(a)
		

Syntax

×

Parameters

field

Multivalue expression.

Description

Remove duplicate values from a multivalued field.

Supported types

Example

ROW a=["foo", "foo", "bar", "foo"]
| EVAL dedupe_a = MV_DEDUPE(a)
		

Syntax

×

Parameters

field

Multivalue expression.

Description

Converts a multivalued expression into a single valued column containing the first value. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT.

The order that multivalued fields are read from underlying storage is not guaranteed. It is frequently ascending, but don’t rely on that. If you need the minimum value use MV_MIN instead of MV_FIRST. MV_MIN has optimizations for sorted values so there isn’t a performance benefit to MV_FIRST.

Supported types

Example

ROW a="foo;bar;baz"
| EVAL first_a = MV_FIRST(SPLIT(a, ";"))
		

Syntax

×

Parameters

field

Multivalue expression.

Description

Converts a multivalue expression into a single valued column containing the last value. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT.

The order that multivalued fields are read from underlying storage is not guaranteed. It is frequently ascending, but don’t rely on that. If you need the maximum value use MV_MAX instead of MV_LAST. MV_MAX has optimizations for sorted values so there isn’t a performance benefit to MV_LAST.

Supported types

Example

ROW a="foo;bar;baz"
| EVAL last_a = MV_LAST(SPLIT(a, ";"))
		

Syntax

×

Parameters

field

Multivalue expression.

Description

Converts a multivalued expression into a single valued column containing the maximum value.

Supported types

Examples

ROW a=[3, 5, 1]
| EVAL max_a = MV_MAX(a)
		

It can be used by any column type, including keyword columns. In that case it picks the last string, comparing their utf-8 representation byte by byte:

ROW a=["foo", "zoo", "bar"]
| EVAL max_a = MV_MAX(a)
		

Syntax

×

Parameters

number

Multivalue expression.

Description

Converts a multivalued field into a single valued field containing the median value.

Supported types

Examples

ROW a=[3, 5, 1]
| EVAL median_a = MV_MEDIAN(a)
		

If the row has an even number of values for a column, the result will be the average of the middle two entries. If the column is not floating point, the average rounds down:

ROW a=[3, 7, 1, 6]
| EVAL median_a = MV_MEDIAN(a)
		

Syntax

×

Parameters

number

Multivalue expression.

Description

Converts a multivalued field into a single valued field containing the median absolute deviation. It is calculated as the median of each data point’s deviation from the median of the entire sample. That is, for a random variable X, the median absolute deviation is median(|median(X) - X|).

Supported types

Example

ROW values = [0, 2, 5, 6]
| EVAL median_absolute_deviation = MV_MEDIAN_ABSOLUTE_DEVIATION(values), median = MV_MEDIAN(values)
		

Syntax

×

Parameters

field

Multivalue expression.

Description

Converts a multivalued expression into a single valued column containing the minimum value.

Supported types

Examples

ROW a=[2, 1]
| EVAL min_a = MV_MIN(a)
		

It can be used by any column type, including keyword columns. In that case, it picks the first string, comparing their utf-8 representation byte by byte:

ROW a=["foo", "bar"]
| EVAL min_a = MV_MIN(a)
		

Syntax

×

Parameters

number

Multivalue expression.

percentile

The percentile to calculate. Must be a number between 0 and 100. Numbers out of range will return a null instead.

Description

Converts a multivalued field into a single valued field containing the value at which a certain percentage of observed values occur.

Supported types

Example

ROW values = [5, 5, 10, 12, 5000]
| EVAL p50 = MV_PERCENTILE(values, 50), median = MV_MEDIAN(values)
		

Syntax

×

Parameters

number

Multivalue expression.

p

It is a constant number that represents the p parameter in the P-Series. It impacts every element’s contribution to the weighted sum.

Description

Converts a multivalued expression into a single-valued column by multiplying every element on the input list by its corresponding term in P-Series and computing the sum.

Supported types

Example

ROW a = [70.0, 45.0, 21.0, 21.0, 21.0]
| EVAL sum = MV_PSERIES_WEIGHTED_SUM(a, 1.5)
| KEEP sum
		

Syntax

×

Parameters

field

Multivalue expression. If null, the function returns null.

start

Start position. If null, the function returns null. The start argument can be negative. An index of -1 is used to specify the last value in the list.

end

End position(included). Optional; if omitted, the position at start is returned. The end argument can be negative. An index of -1 is used to specify the last value in the list.

Description

Returns a subset of the multivalued field using the start and end index values. This is most useful when reading from a function that emits multivalued columns in a known order like SPLIT or MV_SORT.

The order that multivalued fields are read from underlying storage is not guaranteed. It is frequently ascending, but don’t rely on that.

Supported types

Examples

row a = [1, 2, 2, 3]
| eval a1 = mv_slice(a, 1), a2 = mv_slice(a, 2, 3)
		

row a = [1, 2, 2, 3]
| eval a1 = mv_slice(a, -2), a2 = mv_slice(a, -3, -1)
		

Syntax

×

Parameters

field

Multivalue expression. If null, the function returns null.

order

Sort order. The valid options are ASC and DESC, the default is ASC.

Description

Sorts a multivalued field in lexicographical order.

Supported types

Example

ROW a = [4, 2, -3, 2]
| EVAL sa = mv_sort(a), sd = mv_sort(a, "DESC")
		

Syntax

×

Parameters

number

Multivalue expression.

Description

Converts a multivalued field into a single valued field containing the sum of all of the values.

Supported types

Example

ROW a=[3, 5, 6]
| EVAL sum_a = MV_SUM(a)
		

Syntax

×

Parameters

string1

Multivalue expression.

string2

Multivalue expression.

delim

Delimiter. Optional; if omitted, , is used as a default delimiter.

Description

Combines the values from two multivalued fields with a delimiter that joins them together.

Supported types

Example

ROW a = ["x", "y", "z"], b = ["1", "2"]
| EVAL c = mv_zip(a, b, "-")
| KEEP a, b, c