mv-expand operator

  • Article

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

mv-expand can be described as the opposite of the aggregation operators that pack multiple values into a single dynamic-typed array 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 the output 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

Name Type Required Description
ColumnName, ArrayExpression string ✔️ A column reference, or a scalar expression with a value of type dynamic that holds an array or a property bag. The individual top-level elements of the array or property bag get expanded into multiple records.
When ArrayExpression is used and Name doesn't equal any input column name, the expanded value is extended into a new column in the output. Otherwise, the existing ColumnName is replaced.
Name string A name for the new column.
Typename string ✔️ Indicates the underlying type of the array's elements, which becomes the type of the column produced by the mv-expand operator. The operation of applying type is cast-only and doesn't include parsing or type-conversion. Array elements that don't conform with the declared type become null values.
RowLimit int The maximum number of rows generated from each original row. The default is 2147483647. mvexpand is a legacy and obsolete form of the operator mv-expand. The legacy version has a default row limit of 128.
IndexColumnName string If with_itemindex is specified, the output includes another column named IndexColumnName that contains the index starting at 0 of the item in the original expanded collection.

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 duplicated to all records.

  2. For each ColumnName or ArrayExpression that is expanded, the number of output records is 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.

  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 typed by 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

Run the query

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

Output

a b
1 10
1 20
2 a
2 b

Single column - bag expansion

A simple expansion of a single column:

Run the query

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

Output

a b
1 {"prop1": "a1"}
1 {"prop2": "b1"}
2 {"prop1": "a2"}
2 {"prop2": "b2"}

Single column - bag expansion to key-value pairs

A simple bag expansion to key-value pairs:

Run the query

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

a b key val
1 ["prop1","a1"] prop1 a1
1 ["prop2","b1"] prop2 b1
2 ["prop1","a2"] prop1 a2
2 ["prop2","b2"] prop2 b2

Zipped two columns

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

Run the query

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

Output

a b c
1 {"prop1":"a"} 5
1 {"prop2":"b"} 4
1 3

Cartesian product of two columns

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

Run the query

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

Output

a b c
1 { "prop1": "a"} 5
1 { "prop1": "a"} 6
1 { "prop2": "b"} 5
1 { "prop2": "b"} 6

Convert output

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

Run the query

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

ColumnName ColumnOrdinal DateType ColumnType
a 0 System.String string
b 1 System.Object dynamic
c 2 System.Int32 int

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

Using with_itemindex

Expansion of an array with with_itemindex:

Run the query

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

Output

x Index
1 0
2 1
3 2
4 3

Related content