mv-expand 运算符mv-expand operator

将多值动态数组或属性包扩展为多个记录。Expands multi-value dynamic arrays or property bags into multiple records.

mv-expand 可以说与聚合运算符相反,聚合运算符是将多个值打包成单个动态类型化数组或属性包,如 summarize ... make-list()make-seriesmv-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.


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

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


  • ColumnNameArrayExpression:一个列引用或一个标量表达式,含 dynamic 类型的值,带有数组或属性包。ColumnName, ArrayExpression: 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.
    如果使用“ArrayExpression”且“Name”不等于任何输入列名称时,扩展的值将扩展为输出中的新列。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. 否则,将替换现有的 ColumnName。Otherwise, the existing ColumnName is replaced.

  • Name:新列的名称。Name: A name for the new column.

  • Typename:指示数组元素的基础类型,该类型将成为 mv-expand 运算符生成的列的类型。Typename: 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. 不符合声明类型的数组元素将成为 null 值。Array elements that don't conform with the declared type will become null values.

  • RowLimit:从每个原始行生成的最大行数。RowLimit: The maximum number of rows generated from each original row. 默认值为 2147483647。The default is 2147483647.


    mvexpandmv-expand 运算符的已过时的旧形式。mvexpand is a legacy and obsolete form of the operator mv-expand. 旧版本的默认行限制为 128 行。The legacy version has a default row limit of 128.

  • IndexColumnName: 如果指定了 with_itemindex,输出将包括另一列(名为 IndexColumnName),其中包含最初扩展的集合中的项的索引(从 0 开始)。IndexColumnName: If with_itemindex is specified, the output will include another column (named IndexColumnName), which contains the index (starting at 0) of the item in the original expanded collection.


对于输入中的每个记录,运算符会在输出中返回零个、一个或多个记录,具体通过以下方式确定: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. 对于扩展的每个 ColumnName 或 ArrayExpression,则根据以下所述方式确定每个值的输出记录数量。For each ColumnName or ArrayExpression that is expanded, the number of output records is determined for each value as explained below. 对于每个输入记录,将计算输出记录的最大数量。For each input record, the maximum number of output records is calculated. 将“并行”扩展所有数组或属性包,以便将缺少的值(如果有)替换为 null 值。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. 如果动态值为 null,则为该值 (null) 生成单个记录。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.

扩展的列的类型为 dynamic,除非使用 to typeof() 子句显式指定其类型。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:

  • bagexpansion=bagkind=bag:将属性包扩展为单个条目属性包。bagexpansion=bag or kind=bag: Property bags are expanded into single-entry property bags. 此模式是默认模式。This mode is the default mode.
  • bagexpansion=arraykind=array:将属性包扩展为双元素 [key,value] 数组结构,可允许统一访问键和值。bagexpansion=array or kind=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.


单个列Single Column

单个列的简单展开:A simple expansion of a single column:

datatable (a:int, b:dynamic)[1,dynamic({"prop1":"a", "prop2":"b"})]
| mv-expand b 
aa bb
11 {"prop1":"a"}{"prop1":"a"}
11 {"prop2":"b"}{"prop2":"b"}

压缩的两个列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
aa bb cc
11 {"prop1":"a"}{"prop1":"a"} 55
11 {"prop2":"b"}{"prop2":"b"} 44
11 33

两个列的笛卡尔乘积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)
  dynamic({"prop1":"a", "prop2":"b"}),
  dynamic([5, 6])
| mv-expand b
| mv-expand c
aa bb cc
11 { "prop1": "a"}{ "prop1": "a"} 55
11 { "prop1": "a"}{ "prop1": "a"} 66
11 { "prop2": "b"}{ "prop2": "b"} 55
11 { "prop2": "b"}{ "prop2": "b"} 66

转换输出Convert output

若要将 mv-expand 的输出强制为某个特定类型(默认为动态),请使用 to typeofTo 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 
ColumnNameColumnName ColumnOrdinalColumnOrdinal DateTypeDateType ColumnTypeColumnType
aa 00 System.StringSystem.String stringstring
bb 11 System.ObjectSystem.Object 动态dynamic
cc 22 System.Int32System.Int32 intint

请注意 b 列以 dynamic 的形式返回,而 cint 的形式返回。Notice column b is returned as dynamic while c is returned as int.

使用 with_itemindexUsing with_itemindex

通过 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
xx 索引Index
11 00
22 11
33 22
44 33

另请参阅See also