mv-apply operator

Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

Applies a subquery to each record, and returns the union of the results of all subqueries.

For example, assume a table T has a column Metric of type dynamic whose values are arrays of real numbers. The following query locates the two biggest values in each Metric value, and returns the records corresponding to these values.

T | mv-apply Metric to typeof(real) on 
(
   top 2 by Metric desc
)

The mv-apply operator has the following processing steps:

1. Uses the mv-expand operator to expand each record in the input into subtables (order is preserved).
2. Applies the subquery for each of the subtables.
3. Adds zero or more columns to the resulting subtable. These columns contain the values of the source columns that aren't expanded, and are repeated where needed.
4. Returns the union of the results.

The mv-apply operator gets the following inputs:

1. One or more expressions that evaluate into dynamic arrays to expand. The number of records in each expanded subtable is the maximum length of each of those dynamic arrays. Null values are added where multiple expressions are specified and the corresponding arrays have different lengths.

2. Optionally, the names to assign the values of the expressions after expansion. These names become the columns names in the subtables. If not specified, the original name of the column is used when the expression is a column reference. A random name is used otherwise.

   Note

   We recommend that you use the default column names.

3. The data types of the elements of those dynamic arrays, after expansion. These data types become the column types of the columns in the subtables. If not specified, dynamic is used.

4. Optionally, the name of a column to add to the subtables that specifies the 0-based index of the element in the array that resulted in the subtable record.

5. Optionally, the maximum number of array elements to expand.

The mv-apply operator can be thought of as a generalization of the mv-expand operator (in fact, the latter can be implemented by the former, if the subquery includes only projections.)

Syntax

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

Where ItemIndex has the syntax:

with_itemindex = IndexColumnName

ColumnsToExpand is a comma-separated list of one or more elements of the form:

[Name =] ArrayExpression [to typeof (Typename)]

RowLimit is simply:

limit RowLimit

SubQuery has the same syntax of any query statement.

Learn more about syntax conventions.

Parameters

Examples

Review the examples and run them in your Data Explorer query page.

Internal mv-expand done by mv-apply

The query helps in understanding the mv-expand done internally by mv-apply

let data = datatable (index: int, Arr: dynamic)
[
    0, dynamic([7]),
    1, dynamic([6, 11, 7])
];
data
| mv-apply Arr2=Arr to typeof(long) on 
    (
       take 100
    )

Output

Getting the largest element from the array

The query creates a new column having the largest element of an array.

let data = datatable (index: int, Arr: dynamic)
[
    0, dynamic([1, 5, 3]),
    1, dynamic([10, 2, 5, 7])
];
data
| mv-apply topElem=Arr to typeof(long) on 
    (
    top 1 by topElem
    )

Output

Find top two elements in an array

The query saves the top two elements of an array in a new array column Top2.

let data = datatable (index: int, Arr: dynamic)
[
    0, dynamic([5, 1, 3]),
    1, dynamic([4, 10, 8, 7])
];
data
| mv-apply Arr2=Arr to typeof(long) on 
    (
    top 2 by Arr2
    | summarize Top2=make_list(Arr2)
    )

Output

Select elements in arrays

The query identifies the top two elements from each dynamic array based on the Arr2 values and summarizes them into new lists.

datatable (Val:int, Arr1:dynamic, Arr2:dynamic)
[ 1, dynamic(['A1', 'A2', 'A3']),       dynamic([10, 30, 7]), 
  7, dynamic(['B1', 'B2', 'B5']),       dynamic([15, 11, 50]),
  3, dynamic(['C1', 'C2', 'C3', 'C4']), dynamic([6, 40, 20, 8])
] 
| mv-apply NewArr1=Arr1, NewArr2=Arr2 to typeof(long) on (
 top 2 by NewArr2
 | summarize NewArr1=make_list(NewArr1), NewArr2=make_list(NewArr2)
)

Output

Using with_itemindex for working with a subset of the array

The query results in a table with rows where the index is 2 or greater, including the index and element values from the original lists.

let data = datatable (row: int, Arr: dynamic)
[
    0, dynamic([5, 1, 3]),
    1, dynamic([4, 10, 8, 7])
];
data
| mv-apply with_itemindex=index value=Arr to typeof(long) on 
  (
    // here you have 'index' column
    where index >= 2
  )

Output

Using multiple columns to join element of two arrays

The query combines elements from two dynamic arrays into a new concatenated format and then summarizes them into lists.

datatable (Val: int, Arr1: dynamic, Arr2: dynamic)
[
    1, dynamic(['A1', 'A2']), dynamic(['B1', 'B2', 'B3']), 
    5, dynamic(['C1', 'C2']), dynamic(['D1'])
] 
| mv-apply T1=Arr1, T2=Arr2 on (
    extend Out = strcat(T1, "_", T2)
    | summarize Out= make_list(Out)
  )

Output

Applying mv-apply to a property bag

This query dynamically removes properties from the packed values object based on the criteria that their values don't start with 555. The final result contains the original columns with unwanted properties removed.

datatable(Source: string, Target: string, Count: long)
[
    '555-1234', '555-1212', 46,
    '555-2424', '', int(null)
]
| extend Pack = pack_all()
| mv-apply removeProperties = Pack on 
    (
    mv-expand kind = array Pack
    | where Pack[1] !startswith "555"
    | summarize propsToRemove = make_set(Pack[0])
    )
| extend values = bag_remove_keys(Pack, propsToRemove)
| project-away propsToRemove

Output

Related content

• mv-expand operator
• array_iff function