mvapply 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  mvapply Metric to typeof(real) on
(
top 2 by Metric desc
)
The mvapply
operator has the following
processing steps:
 Uses the
mvexpand
operator to expand each record in the input into subtables (order is preserved).  Applies the subquery for each of the subtables.
 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.
 Returns the union of the results.
The mvapply
operator gets the following inputs:
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.
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.
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.Optionally, the name of a column to add to the subtables that specifies the 0based index of the element in the array that resulted in the subtable record.
Optionally, the maximum number of array elements to expand.
The mvapply
operator can be thought of as a generalization of the
mvexpand
operator (in fact, the latter can be implemented
by the former, if the subquery includes only projections.)
Syntax
T 
mvapply
[ItemIndex] ColumnsToExpand [RowLimit] on
(
SubQuery )
Where ItemIndex has the syntax:
with_itemindex
=
IndexColumnName
ColumnsToExpand is a commaseparated 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 arrayexpansion phase and indicates the 0based array index of the expanded value. 

Name  string  The name to assign the arrayexpanded values of each arrayexpanded 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 arrayexpanded. 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 arrayexpanded subtable. 
Note
Unlike the mvexpand
operator, the mvapply
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 mvexpand
operator (see example below).
Examples
Getting the largest element from the array
let _data =
range x from 1 to 8 step 1
 summarize l=make_list(x) by xMod2 = x % 2;
_data
 mvapply 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
let _data =
range x from 1 to 8 step 1
 summarize l=make_list(x) by xMod2 = x % 2;
_data
 mvapply 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
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])
]
 mvapply 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
let _data =
range x from 1 to 10 step 1
 summarize l=make_list(x) by xMod2 = x % 2;
_data
 mvapply 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
datatable (Val: int, Arr1: dynamic, Arr2: dynamic)
[
1, dynamic(['A1', 'A2', 'A3']), dynamic(['B1', 'B2', 'B3']),
5, dynamic(['C1', 'C2']), dynamic(['D1', 'D2'])
]
 mvapply 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 mvapply to a property bag
In the following example, mvapply
is used in combination with an
inner mvexpand
to remove values that don't start with "555" from a property bag:
datatable(SourceNumber: string, TargetNumber: string, CharsCount: long)
[
'5555551234', '5555551212', 46,
'5555551212', '', int(null)
]
 extend values = pack_all()
 mvapply removeProperties = values on
(
mvexpand kind = array values
 where values[1] !startswith "555"
 summarize propsToRemove = make_set(values[0])
)
 extend values = bag_remove_keys(values, propsToRemove)
 projectaway propsToRemove
Output
SourceNumber  TargetNumber  CharsCount  values 

5555551234  5555551212  46  { "SourceNumber": "5555551234", "TargetNumber": "5555551212" } 
5555551212  { "SourceNumber": "5555551212" } 
See also
 mvexpand operator
Feedback
Submit and view feedback for