mv-expand

This page explains how to use the mv-expand operator in APL.

The mv-expand operator expands dynamic arrays and property bags into multiple rows. Each element of the array or each property of the bag becomes its own row, while other columns are duplicated.

You use mv-expand when you want to analyze or filter individual values inside arrays or objects. This is especially useful when working with logs that include lists of values, OpenTelemetry traces that contain arrays of spans, or security events that group multiple attributes into one field.

For users of other query languages

If you come from other query languages, this section explains how to adjust your existing queries to achieve the same results in APL.

Splunk SPL users

In Splunk SPL, the mvexpand command expands multi-value fields into separate events. The APL mv-expand operator works in a very similar way, splitting array values into individual rows. The main difference is that APL explicitly works with dynamic arrays or property bags, while Splunk handles multi-value fields implicitly.

Splunk example

... | mvexpand request_uri

ANSI SQL users

In ANSI SQL, you use CROSS JOIN UNNEST or CROSS APPLY to flatten arrays into rows. In APL, mv-expand provides a simpler and more direct way to achieve the same result.

SQL example

SELECT id, value
FROM logs
CROSS JOIN UNNEST(request_uris) AS t(value)

Usage

Syntax

mv-expand [kind=(bag|array)] [with_itemindex=IndexFieldName] FieldName [to typeof(Typename)] [limit Rowlimit]

Parameters

Parameter	Description
kind	Optional. Specifies whether the column is a bag (object) or an array. Defaults to bag.
with_itemindex=IndexFieldName	Optional. Outputs an additional column with the zero-based index of the expanded item.
FieldName	Required. The name of the column that contains an array or object to expand.
to typeof(Typename)	Optional. Converts each expanded element to the specified type.
limit Rowlimit	Optional. Limits the number of expanded rows per record.

Returns

The operator returns a table where each element of the expanded array or each property of the expanded object is placed in its own row. Other columns are duplicated for each expanded row.

Use case example

When analyzing logs, some values can be stored as arrays. You can use mv-expand to expand them into individual rows for easier filtering.

Query

['sample-http-logs']
| limit 100
| mv-expand territories
| summarize count = count() by territory_name = tostring(territories)

Run in Playground

Output

territory_name	count
United States	67
India	22
Japan	12

This query expands the territories array into rows and counts the most frequent territories.

Work with dynamic map keys

When you have map fields with dynamic keys (keys that vary across events), you can use mv-expand with bag_keys() to process all keys without knowing them in advance. This is useful for nested objects where the field names change, such as user-defined tags, custom metadata, or dynamic configurations.

Query

['otel-demo-traces']
| limit 10
| extend map_keys = bag_keys(['attributes.custom'])
| mv-expand map_keys
| summarize count() by tostring(map_keys)

Run in Playground

Output

map_keys	count
http.status_code	6
http.method	4

In this example:

1. bag_keys extracts all the keys from the map field into an array
2. mv-expand splits those keys into separate rows
3. summarize counts how often each key appears across all events

Work with dynamic map keys and values

To work with both keys and values from a dynamic map, use parse_json to convert the map field to a JSON object, and then use mv-expand with kind=array to expand the object into separate rows.

Query

['otel-demo-traces']
| limit 10
| extend map_items = parse_json(tostring(['attributes.custom']))
| mv-expand kind=array map_items
| extend key = tostring(map_items[0]), value = tostring(map_items[1])
| summarize avg(todouble(value)) by key

Run in Playground

Output

key	avg_value
net.peer.port	39,546
net.host.port	8,080

List of related operators

• project: Selects or computes columns. Use it when you want to reshape data, not expand arrays.
• summarize: Aggregates data across rows. Use it after expanding arrays to compute statistics.
• top: Returns the top N rows by expression. Use it after expansion to find the most frequent values.