Data reshaping plugins

• 1: bag_unpack plugin
• 2: narrow plugin
• 3: pivot plugin

1 - bag_unpack plugin

The bag_unpack plugin unpacks a single column of type dynamic, by treating each property bag top-level slot as a column. The plugin is invoked with the evaluate operator.

Syntax

T | evaluate bag_unpack( Column [, OutputColumnPrefix ] [, columnsConflict ] [, ignoredProperties ] ) [: OutputSchema]

Parameters

Output schema syntax

( ColumnName : ColumnType [, …] )

To add all columns of the input table to the plugin output, use a wildcard * as the first parameter, as follows:

( * , ColumnName : ColumnType [, …] )

Returns

The bag_unpack plugin returns a table with as many records as its tabular input (T). The schema of the table is the same as the schema of its tabular input with the following modifications:

• The specified input column (Column) is removed.
• The schema is extended with as many columns as there are distinct slots in the top-level property bag values of T. The name of each column corresponds to the name of each slot, optionally prefixed by OutputColumnPrefix. Its type is either the type of the slot, if all values of the same slot have the same type, or dynamic, if the values differ in type.

Examples

Expand a bag

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

Output

Expand a bag with OutputColumnPrefix

Expand a bag and use the OutputColumnPrefix option to produce column names that begin with the prefix ‘Property_’.

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

Output

Expand a bag with columnsConflict

Expand a bag and use the columnsConflict option to resolve conflicts between existing columns and columns produced by the bag_unpack() operator.

datatable(Name:string, d:dynamic)
[
    'Old_name', dynamic({"Name": "John", "Age":20}),
    'Old_name', dynamic({"Name": "Dave", "Age":40}),
    'Old_name', dynamic({"Name": "Jasmine", "Age":30}),
]
| evaluate bag_unpack(d, columnsConflict='replace_source') // Use new name

Output

datatable(Name:string, d:dynamic)
[
    'Old_name', dynamic({"Name": "John", "Age":20}),
    'Old_name', dynamic({"Name": "Dave", "Age":40}),
    'Old_name', dynamic({"Name": "Jasmine", "Age":30}),
]
| evaluate bag_unpack(d, columnsConflict='keep_source') // Keep old name

Output

Expand a bag with ignoredProperties

Expand a bag and use the ignoredProperties option to ignore certain properties in the property bag.

datatable(d:dynamic)
[
    dynamic({"Name": "John", "Age":20, "Address": "Address-1" }),
    dynamic({"Name": "Dave", "Age":40, "Address": "Address-2"}),
    dynamic({"Name": "Jasmine", "Age":30, "Address": "Address-3"}),
]
// Ignore 'Age' and 'Address' properties
| evaluate bag_unpack(d, ignoredProperties=dynamic(['Address', 'Age']))

Output

Expand a bag with a query-defined OutputSchema

Expand a bag and use the OutputSchema option to allow various optimizations to be evaluated before running the actual query.

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

Output

Expand a bag and use the OutputSchema option to allow various optimizations to be evaluated before running the actual query. Use a wildcard * to return all columns of the input table.

datatable(d:dynamic, Description: string)
[
    dynamic({"Name": "John", "Age":20}), "Student",
    dynamic({"Name": "Dave", "Age":40}), "Teacher",
    dynamic({"Name": "Jasmine", "Age":30}), "Student",
]
| evaluate bag_unpack(d) : (*, Name:string, Age:long)

Output

2 - narrow plugin

The narrow plugin “unpivots” a wide table into a table with three columns:

• Row number
• Column type
• Column value (as string)

The narrow plugin is designed mainly for display purposes, as it allows wide tables to be displayed comfortably without the need of horizontal scrolling.

The plugin is invoked with the evaluate operator.

Syntax

T | evaluate narrow()

Examples

The following example shows an easy way to read the output of the Kusto .show diagnostics management command.

.show diagnostics
 | evaluate narrow()

The results of .show diagnostics itself is a table with a single row and 33 columns. By using the narrow plugin we “rotate” the output to something like this:

3 - pivot plugin

Rotates a table by turning the unique values from one column in the input table into multiple columns in the output table and performs aggregations as required on any remaining column values that will appear in the final output.

Syntax

T | evaluate pivot(pivotColumn[, aggregationFunction] [,column1 [,column2 … ]]) [: OutputSchema]

Parameters

Returns

Pivot returns the rotated table with specified columns (column1, column2, …) plus all unique values of the pivot columns. Each cell for the pivoted columns will contain the aggregate function computation.

Examples

Pivot by a column

For each EventType and State starting with ‘AL’, count the number of events of this type in this state.

StormEvents
| project State, EventType
| where State startswith "AL"
| where EventType has "Wind"
| evaluate pivot(State)

Output

Pivot by a column with aggregation function

For each EventType and State starting with ‘AR’, display the total number of direct deaths.

StormEvents
| where State startswith "AR"
| project State, EventType, DeathsDirect
| where DeathsDirect > 0
| evaluate pivot(State, sum(DeathsDirect))

Output

Pivot by a column with aggregation function and a single additional column

Result is identical to previous example.

StormEvents
| where State startswith "AR"
| project State, EventType, DeathsDirect
| where DeathsDirect > 0
| evaluate pivot(State, sum(DeathsDirect), EventType)

Output

Specify the pivoted column, aggregation function, and multiple additional columns

For each event type, source, and state, sum the number of direct deaths.

StormEvents
| where State startswith "AR"
| where DeathsDirect > 0
| evaluate pivot(State, sum(DeathsDirect), EventType, Source)

Output

Pivot with a query-defined output schema

The following example selects specific columns in the StormEvents table. It uses an explicit schema definition that allows various optimizations to be evaluated before running the actual query.

StormEvents
| project State, EventType
| where EventType has "Wind"
| evaluate pivot(State): (EventType:string, ALABAMA:long, ALASKA:long)

Output