mv-expand operator - Azure Data Explorer & Real-Time Intelligence (2024)

Expands multi-value dynamic arrays or property bags into multiple records.

mv-expand can be described as the opposite of the aggregation operatorsthat pack multiple values into a single dynamic-typedarray or property bag, such as summarize ... make-list() and make-series.Each element in the (scalar) array or property bag generates a new record in theoutput of the operator. All columns of the input that aren't expanded are duplicated to all the records in the output.

Syntax

T |mv-expand [kind=(bag | array)] [with_itemindex= IndexColumnName] ColumnName [to typeof( Typename)] [, ColumnName ...] [limit Rowlimit]

T |mv-expand [kind=(bag | array)] [Name =] ArrayExpression [to typeof(Typename)] [, [Name =] ArrayExpression [to typeof(Typename)] ...] [limit Rowlimit]

Learn more about syntax conventions.

Parameters

Returns

For each record in the input, the operator returns zero, one, or many records in the output,as determined in the following way:

1. Input columns that aren't expanded appear in the output with their original value.If a single input record is expanded into multiple output records, the value is duplicatedto all records.

2. For each ColumnName or ArrayExpression that is expanded, the number of output recordsis determined for each value as explained in modes of expansion. For each input record, the maximum number of output records is calculated. All arrays or property bags are expanded "in parallel"so that missing values (if any) are replaced by null values. Elements are expanded into rows in the order that they appear in the original array/bag.

   See Also

   Approximate, partial and combined lookups in Azure Sentinelmv-apply operator - Azure Data Explorer & Real-Time IntelligenceKQL’s mv-apply command – Yet Another Security BlogCalculating downtime using Window functions and Time Series functions in Azure Data Explorer

3. If the dynamic value is null, then a single record is produced for that value (null).If the dynamic value is an empty array or property bag, no record is produced for that value.Otherwise, as many records are produced as there are elements in the dynamic value.

The expanded columns are of type dynamic, unless they're explicitly typedby using the to typeof() clause.

Modes of expansion

Two modes of property bag expansions are supported:

• kind=bag or bagexpansion=bag: Property bags are expanded into single-entry property bags. This mode is the default mode.
• kind=array or bagexpansion=array: Property bags are expanded into two-element [key,value] array structures, allowing uniform access to keys and values. This mode also allows, for example, running a distinct-count aggregation over property names.

Examples

Single column - array expansion

datatable (a: int, b: dynamic)[ 1, dynamic([10, 20]), 2, dynamic(['a', 'b'])]| mv-expand b

Output

Single column - bag expansion

A simple expansion of a single column:

datatable (a: int, b: dynamic)[ 1, dynamic({"prop1": "a1", "prop2": "b1"}), 2, dynamic({"prop1": "a2", "prop2": "b2"})]| mv-expand b

Output

Single column - bag expansion to key-value pairs

A simple bag expansion to key-value pairs:

datatable (a: int, b: dynamic)[ 1, dynamic({"prop1": "a1", "prop2": "b1"}), 2, dynamic({"prop1": "a2", "prop2": "b2"})]| mv-expand kind=array b | extend key = b[0], val=b[1]

Output

Zipped two columns

Expanding two columns will first 'zip' the applicable columns and then expand them:

datatable (a: int, b: dynamic, c: dynamic)[ 1, dynamic({"prop1": "a", "prop2": "b"}), dynamic([5, 4, 3])]| mv-expand b, c

Output

Cartesian product of two columns

If you want to get a Cartesian product of expanding two columns, expand one after the other:

datatable (a: int, b: dynamic, c: dynamic)[ 1, dynamic({"prop1": "a", "prop2": "b"}), dynamic([5, 6])]| mv-expand b| mv-expand c

Output

Convert output

To force the output of an mv-expand to a certain type (default is dynamic), use to typeof:

datatable (a: string, b: dynamic, c: dynamic)[ "Constant", dynamic([1, 2, 3, 4]), dynamic([6, 7, 8, 9])]| mv-expand b, c to typeof(int)| getschema 

Output

Notice column b is returned as dynamic while c is returned as int.

Using with_itemindex

Expansion of an array with with_itemindex:

range x from 1 to 4 step 1| summarize x = make_list(x)| mv-expand with_itemindex=Index x

Output

Related content

• For more examples, see Chart count of live activities over time.
• mv-apply operator.
• For the opposite of the mv-expand operator, see summarize make_list().
• For expanding dynamic JSON objects into columns using property bag keys, see bag_unpack() plugin.