mv-apply operator

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 return 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

   It is recommended to use the default column names.

3. The data types of the elements of those dynamic arrays, after expansion. These 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

and SubQuery has the same syntax of any query statement.

Learn more about syntax conventions.

Parameters

Name	Type	Required	Description
ItemIndex	string		Indicates the name of a column of type long that's appended to the input as part of the array-expansion phase and indicates the 0-based array index of the expanded value.
Name	string		The name to assign the array-expanded values of each array-expanded expression. If not specified, the name of the column is used if available. A random name is generated if ArrayExpression isn't a simple column name.
ArrayExpression	dynamic	✔️	The array whose values are array-expanded. If the expression is the name of a column in the input, the input column is removed from the input and a new column of the same name, or ColumnName if specified, appears in the output.
Typename	string		The name of the type that the individual elements of the dynamic array ArrayExpression take. Elements that don't conform to this type are replaced by a null value. If unspecified, dynamic is used by default.
RowLimit	int		A limit on the number of records to generate from each record of the input. If unspecified, 2147483647 is used.
SubQuery	string		A tabular query expression with an implicit tabular source that gets applied to each array-expanded subtable.

Note

Unlike the mv-expand operator, the mv-apply operator doesn't support bagexpand=array expansion. If the expression to be expanded is a property bag and not an array, you can use an inner mv-expand operator (see example below).

Examples

Getting the largest element from the array

Run the query

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

xMod2	l	element
1	[1, 3, 5, 7]	7
0	[2, 4, 6, 8]	8

Calculating the sum of the largest two elements in an array

Run the query

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

xMod2	l	SumOfTop2
1	[1,3,5,7]	12
0	[2,4,6,8]	14

Select elements in arrays

Run the query

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

Val1	Arr1	Arr2	NewArr1	NewArr2
1	["A1","A2","A3"]	[10,30,7]	["A2',"A1"]	[30,10]
7	["B1","B2","B5"]	[15,11,50]	["B5","B1"]	[50,15]
3	["C1","C2","C3","C4"]	[6,40,20,8]	["C2","C3"]	[40,20]

Using with_itemindex for working with a subset of the array

Run the query

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 
    (
    // here you have 'index' column
    where index >= 3
    )
| project index, element

Output

index	element
3	7
4	9
3	8
4	10

Using mutiple columns to join element of 2 arrays

Run the query

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

Output

Val	Arr1	Arr2	Out
1	["A1","A2","A3"]	["B1","B2","B3"]	["A1_B1","A2_B2","A3_B3"]
5	["C1","C2"]	["D1","D2"]	["C1_D1","C2_D2"]

Applying mv-apply to a property bag

In the following example, mv-apply is used in combination with an inner mv-expand to remove values that don't start with "555" from a property bag:

Run the query

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

Output

SourceNumber	TargetNumber	CharsCount	values
555-555-1234	555-555-1212	46	{ "SourceNumber": "555-555-1234", "TargetNumber": "555-555-1212" }
555-555-1212			{ "SourceNumber": "555-555-1212" }

Related content

• mv-expand operator