How to Expand Arrays with mv-expand in KQL: Example

Telemetry tables very often contain dynamic columns that store lists inside a single cell: event tags, product IDs in an order, error codes. Expanding arrays with mv-expand in KQL turns every value in that list into its own row, which is what lets you then filter, group and count it like any normal table. Every example below can be copied straight into a KQL Queryset, because the data is created in memory.

Prerequisites

• Access to a KQL database (Eventhouse in Microsoft Fabric) or an Azure Data Explorer cluster.
• A KQL Queryset or the Kusto Web UI to write queries.
• Basic knowledge of the where, project and summarize operators.
• No data loading required: we use the datatable operator to create sample tables.

Step 1: Create sample data with datatable

Before expanding arrays in KQL, we need a table with a dynamic column. The datatable operator creates a temporary table without touching the database — perfect for learning and testing.

let Eventos = datatable(id:int, utilizador:string, etiquetas:dynamic)
[
    1, "ana",   dynamic(["bi", "fabric"]),
    2, "rui",   dynamic(["kql"]),
    3, "maria", dynamic(["bi", "kql", "azure"])
];
Eventos

The result has 3 rows. Notice that the etiquetas column shows the whole list in a single cell — and that is exactly why we cannot yet count how often each tag appears.

Step 2: Expand the array with mv-expand

The mv-expand (multi-value expand) operator takes a dynamic column and produces one row per array element. The other columns of the original row are repeated in each new row.

Eventos
| mv-expand etiquetas

We now have 6 rows: 2 for "ana", 1 for "rui" and 3 for "maria". The etiquetas column no longer holds a list, but a single value per row.

Step 3: Give the expanded value a name and a type

By default the expanded value is still of type dynamic, which gets in the way of string comparisons and sorting. Good practice is to give the column a new name and cast the type with to typeof().

Eventos
| mv-expand etiqueta = etiquetas to typeof(string)
| project id, utilizador, etiqueta

The etiqueta column is now a real string. That lets you write, for example, | where etiqueta == "kql" with no surprises.

Step 4: Get each value's position with with_itemindex

Sometimes the order inside the array carries meaning (the first error, the primary category). The with_itemindex option returns the index of each element, starting at 0.

Eventos
| mv-expand with_itemindex = posicao etiqueta = etiquetas to typeof(string)
| project id, utilizador, posicao, etiqueta
| where posicao == 0

This example returns only the first tag of each event — a simple way to extract the main value from a list.

Step 5: Expand a property bag

A dynamic column can also hold an object with key/value pairs. With kind=array, each pair is expanded into a small two-element array: position 0 holds the key and position 1 holds the value.

let Propriedades = datatable(id:int, props:dynamic)
[
    1, dynamic({"pais": "PT", "plano": "pro"}),
    2, dynamic({"pais": "ES", "plano": "free"})
];
Propriedades
| mv-expand kind = array props
| extend chave = tostring(props[0]), valor = tostring(props[1])
| project id, chave, valor

The result is a long, narrow table with one row per property — the ideal shape for pivoting or filtering by key later on.

Step 6: Aggregate after expanding

With the array expanded, summarize becomes trivial again. This is the real goal of most mv-expand queries in KQL: counting how often each value appears.

Eventos
| mv-expand etiqueta = etiquetas to typeof(string)
| summarize eventos = count() by etiqueta
| order by eventos desc

You should get bi and kql with 2 events each, and fabric and azure with 1.

Common error: if the column is text (for example, JSON stored as a string), mv-expand cannot expand it. Convert it first with extend lista = todynamic(coluna_texto) and only then expand.

Verify the result

Check three things. First, the row count: Eventos | mv-expand etiquetas | count should return 6, that is, the sum of the sizes of all arrays. Second, the column type: add | extend t = gettype(etiqueta) and confirm that it shows string (and not array). Third, if you work with very large arrays, keep in mind that mv-expand expands up to 2048 values per row by default; you can adjust that maximum with the limit option.

Conclusion

With mv-expand, to typeof() and with_itemindex you can already turn any dynamic column into a flat table ready to aggregate. The natural next step is mv-apply, which expands the array, applies a subquery to each element and aggregates it back — very handy, for instance, to keep only the highest value of each list. A final tip: expand as late as possible in the query, after your where clauses, because expansion multiplies rows and that is where performance is lost. Which dynamic columns in your data are waiting to be expanded?