mv-expand 运算符mv-expand operator

展开多值数组或属性包。Expands multi-value array or property bag.

mv-expand 应用于 dynamic 类型的数组或属性包,以便集合中的每个值都获得一个单独的行。mv-expand is applied on a dynamic-typed array or property bag column so that each value in the collection gets a separate row. 将复制扩展行中的所有其他列。All the other columns in an expanded row are duplicated.


T | mv-expand [bagexpansion=(bag | array)] [with_itemindex=IndexColumnName] ColumnName [, ColumnName ...] [limit Rowlimit]T | mv-expand [bagexpansion=(bag | array)] [with_itemindex=IndexColumnName] ColumnName [, 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]


  • ColumnName:在结果中,已命名列中的数组将扩展为多行。ColumnName: In the result, arrays in the named column are expanded to multiple rows.

  • ArrayExpression:生成数组的表达式。ArrayExpression: An expression yielding an array. 如果使用此形式,则会添加新列并保留现有列。If this form is used, a new column is added and the existing one is preserved.

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

  • Typename:指示数组元素的基础类型,该类型将成为 mv-apply 运算符生成的列的类型。Typename: Indicates the underlying type of the array's elements, which becomes the type of the column produced by the mv-apply operator. 应用类型的操作仅限强制转换,不包括分析或类型转换。The operation of applying type is cast-only and doesn't include parsing or type-conversion. 不符合声明类型的数组元素将为 null 值。Array elements that do not 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.


    运算符 mvexpand 的旧格式和过时形式的默认行限制为 128。The legacy and obsolete form of the operator mvexpand has a default row limit of 128.

  • IndexColumnName:如果指定了 with_itemindex,则输出将包含一个名为 IndexColumnName 的附加列,该列包含原始展开集合中的项的索引(从 0 开始)。IndexColumnName: If with_itemindex is specified, the output will include an additional column (named IndexColumnName), which contains the index (starting at 0) of the item in the original expanded collection.


用于已命名列或数组表达式中任何数组的每个值的多个行。Multiple rows for each of the values in any array that are in the named column or in the array expression. 如果指定了多个列或表达式,则它们会并行展开。If several columns or expressions are specified, they're expanded in parallel. 对于每个输入行,将存在与最长展开表达式中的元素数相同的输出行数(较短的列表用 null 填充)。For each input row, there will be as many output rows as there are elements in the longest expanded expression (shorter lists are padded with nulls). 如果行中的值为空数组,则该行在展开后无内容(不会显示在结果集中)。If the value in a row is an empty array, the row expands to nothing (won't show in the result set). 但是,如果行中的值不是数组,则该行将按原样保留在结果集中。However, if the value in a row isn't an array, the row is kept as is in the result set.

扩展列始终具有动态类型。The expanded column always has dynamic type. 如需计算或聚合值,请使用转换,如 todatetime()tolong()Use a cast such as todatetime() or tolong() if you want to compute or aggregate values.

支持两种模式的属性包扩展:Two modes of property-bag expansions are supported:

  • bagexpansion=bag:将属性包扩展为单个条目属性包。bagexpansion=bag: Property bags are expanded into single-entry property bags. 此模式是默认扩展。This mode is the default expansion.
  • bagexpansion=array:将属性包扩展为双元素 [key,value] 数组结构,可允许统一访问键和值(以及对属性名称运行非重复计数聚合)。bagexpansion=array: Property bags are expanded into two-element [key,value] array structures, allowing uniform access to keys and values (also, 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 的输出强制转换为某个类型(默认为 dynamic),请使用 to typeofIf you want 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 
ColumnNameColumnName ColumnOrdinalColumnOrdinal DateTypeDateType ColumnTypeColumnType
aa 00 System.StringSystem.String stringstring
bb 11 System.ObjectSystem.Object 动态dynamic
cc 22 System.Int32System.Int32 intint

请注意,列 bdynamic 类型的,而 cint 类型的。Notice column b is coming out as dynamic while c is coming out 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