Exploring the mv-apply Operator in Kusto Query Language

Unlocking the Power of Arrays

Kusto Query Language (KQL) is a powerful tool for querying large datasets, and one of its versatile operators is the mv-apply operator. This operator allows you to apply a subquery to each record and return the union of the results. Let's dive into its syntax, parameters, and some practical examples to understand its functionality better.

Syntax and Parameters

The mv-apply operator can be thought of as a generalization of the mv-expand operator. It expands each record in the input into subtables, applies the subquery for each subtable, and returns the union of the results. Here’s the basic syntax:

T | mv-apply [ItemIndex] ColumnsToExpand [RowLimit] on (SubQuery)

• ItemIndex: Indicates the name of a column that specifies the 0-based index of the element in the array.

• ColumnsToExpand: A comma-separated list of expressions that evaluate into dynamic arrays to expand.

• RowLimit: Limits the number of records to generate from each input record.

• SubQuery: A tabular query expression applied to each array-expanded subtable.

Examples

1. Getting the Largest Element from an Array

This example locates the largest element in each array:

let _data = range x from 1 to 8 step 1 | summarize l=make_list(x) by xMod2 = x % 2;
_data | mv-apply element=l to typeof(long) on (top 1 by element)

Output:

2. Calculating the Sum of the Largest Two Elements in an Array

This example calculates the sum of the top two elements in each array:

let _data = range x from 1 to 8 step 1 | summarize l=make_list(x) by xMod2 = x % 2;
_data | mv-apply l to typeof(long) on (top 2 by l | summarize SumOfTop2=sum(l))

Output:

3. Using with_itemindex for Working with a Subset of the Array

This example filters elements based on their index:

let _data = range x from 1 to 10 step 1 | summarize l=make_list(x) by xMod2 = x % 2;
_data | mv-apply with_itemindex=index element=l to typeof(long) on (index >= 3) | project index, element

Output:

TLDR

The mv-apply operator in KQL is a powerful tool for manipulating and querying dynamic arrays. By understanding its syntax and parameters, and through practical examples, you can leverage this operator to perform complex data transformations efficiently.