KQL: mv-expand OR bag_unpack equivalent command to convert a list to multiple columns

1
votes

According to mv-expand documentation:

Expands multi-value array or property bag.

mv-expand is applied on a dynamic-typed column so that each value in the collection gets a separate row. All the other columns in an expanded row are duplicated.

Just like the mv-expand operator will create a row each for the elements in the list -- Is there an equivalent operator/way to make each element in a list an additional column?

I checked the documentation and found Bag_Unpack:

The bag_unpack plugin unpacks a single column of type dynamic by treating each property bag top-level slot as a column.

However, it doesn't seem to work on the list, and rather works on top-level JSON property.

Using bag_unpack (like the below query):

datatable(d:dynamic)
[
    dynamic({"Name": "John", "Age":20}),
    dynamic({"Name": "Dave", "Age":40}),
    dynamic({"Name": "Smitha", "Age":30}),
]
| evaluate bag_unpack(d)

It will do the following:

Name    Age
John    20
Dave    40
Smitha  30

Is there a command/way (see some_command_which_helps) I can achieve the following (convert a list to columns):

datatable(d:dynamic)
[
    dynamic(["John", "Dave"])
]
| evaluate some_command_which_helps(d)

That translates to something like:

Col1    Col2
John    Dave

Is there an equivalent where I can convert a list/array to multiple columns?

For reference: We can run the above queries online on Log Analytics in the demo section if needed (however, it may require login).

2
azure-log-analyticsazure-data-explorerkql

2 Answers

2
votes

you could try something along the following lines

(that said, from an efficiency standpoint, you may want to check your options of restructuring the data set to begin with, using a schema that matches how you plan to actually consume/query it)

datatable(d:dynamic)
[
    dynamic(["John", "Dave"]),
    dynamic(["Janice", "Helen", "Amber"]),
    dynamic(["Jane"]),
    dynamic(["Jake", "Abraham", "Gunther", "Gabriel"]),
]
| extend r = rand()
| mv-expand with_itemindex = i d
| summarize b = make_bag(pack(strcat("Col", i + 1), d)) by r
| project-away r
| evaluate bag_unpack(b)

which will output:

|Col1  |Col2   |Col3   |Col4   |
|------|-------|-------|-------|
|John  |Dave   |       |       |
|Janice|Helen  |Amber  |       |
|Jane  |       |       |       |
|Jake  |Abraham|Gunther|Gabriel|
2
votes

To extract key value pairs from text and convert them to columns without hardcoding the key names in query:

print message="2020-10-15T15:47:09 Metrics: duration=2280, function=WorkerFunction, count=0, operation=copy_into, invocationId=e562f012-a994-4fc9-b585-436f5b2489de, tid=lct_b62e6k59_prd_02, table=SALES_ORDER_SCHEDULE, status=success"
| extend Properties = extract_all(@"(?P<key>\w+)=(?P<value>[^, ]*),?", dynamic(["key","value"]), message)
| mv-apply Properties on (summarize make_bag(pack(tostring(Properties[0]), Properties[1])))
| evaluate bag_unpack(bag_)
| project-away message