Aggregate contents of array column in Azure Data Explorer

Question

I have some data stored in the customEvents property bag that I want to perform aggregation and analytics on. I'm new to the analytics queries that you can run against AppInsights using the Azure Data Explorer query language and am stuck.

I have a handle on converting the contents of one of the property bag's key-value-pairs into an array of numbers (in the example below, that output is represented by items.

let items = parse_json('{"operation_Id": "12345Z12", "days":[43, 21, 65]}');
print items.operation_Id, items.days;

However, when I need to calculate the average value of the items in the array for each operation_Id, I run into a documentation wall. I've looked at mvexpand, let (using a lambda expression), datatable, using dynamic data types, etc. The blocking issue I've had with using mvexpand is that I want to associate every row in the output correlated with its operation operation_Id, and mvexpand only seems to persist that relationship in the first row. With a datatable, the type doesn't support a pipeline input.

Another common error I've (including from the code sample below is Operator Source Expression should be table or column).

let items = parse_json('{"days":[43, 21, 65]}');

let arraySum = (T:(x: long))
{
   T 
   | summarize sum(x) 
};
items
| project days | invoke arraySum()

If necessary, I can perform the aggregation code in JavaScript and only pass the calculated average in the property bag, but it feels like a waste to throw away the raw data values. Is there some obvious calculation or aggregation function that solves this problem?

Yoni L. Yoni L. · Accepted Answer · 2018-11-30T05:47:17

Both the following options should allow you to calculate the average as you're interested in:

(caveat: this is based on the example you've shown, which may potentially be "dumbed down" to not reflect your real-life scenario, so please clarify in case this isn't helpful):

let items = dynamic({"operation_Id": "12345Z12", "days":[43, 21, 65]});
print operationId = tostring(items.operation_Id), days = items.days
| mvexpand days to typeof(int)
| summarize avg(days) by operationId

// or

let items = dynamic({"operation_Id": "12345Z12", "days":[43, 21, 65]});
print operationId = tostring(items.operation_Id), days = items.days
| project operationId, series_stats_dynamic(days)['avg']

Your second example is indeed invalid (scalars and tabular arguments are not born equal), but could be rewritten as follows:

(same caveat as above)

let items = dynamic({"days":[43, 21, 65]});
let arraySum = (T:(x: long))
{
   T 
   | summarize sum(x) 
};
print items 
| mvexpand x = items.days to typeof(long)
| invoke arraySum()

// or

let items = dynamic({"days":[43, 21, 65]});
print items
| project sum = series_stats_dynamic(items.days)["avg"] * array_length(items.days)

Updated examples following up the comments provided later on:

datatable (Operation_id:string, customDimensions:dynamic) 
[ 
    "MTFfq", dynamic({"siteId": "1", "fileCount": "3", "pendingDays":[15,10,11]}), 
    "LXVjk", dynamic({"siteId": "2", "fileCount": "1", "pendingDays":[3]}),
    "jnySt", dynamic({"siteId": "3", "fileCount": "2", "pendingDays":[7,11]}), 
    "NoxoX", dynamic({"siteId": "4", "fileCount": "4", "pendingDays":[1,4,3,11]})
]
| mvexpand days = customDimensions.pendingDays to typeof(int)
| summarize avg(days) by Operation_id

// or

datatable (Operation_id:string, customDimensions:dynamic) 
[ 
    "MTFfq", dynamic({"siteId": "1", "fileCount": "3", "pendingDays":[15,10,11]}), 
    "LXVjk", dynamic({"siteId": "2", "fileCount": "1", "pendingDays":[3]}),
    "jnySt", dynamic({"siteId": "3", "fileCount": "2", "pendingDays":[7,11]}), 
    "NoxoX", dynamic({"siteId": "4", "fileCount": "4", "pendingDays":[1,4,3,11]})
]
| project Operation_id, series_stats_dynamic(customDimensions.pendingDays)['avg']

and:

let arraySum = (T:(x: long))
{
   T 
   | summarize sum(x) 
};
datatable (Operation_id:string, customDimensions:dynamic) 
[ 
    "MTFfq", dynamic({"siteId": "1", "fileCount": "3", "pendingDays":[15,10,11]}), 
    "LXVjk", dynamic({"siteId": "2", "fileCount": "1", "pendingDays":[3]}),
    "jnySt", dynamic({"siteId": "3", "fileCount": "2", "pendingDays":[7,11]}), 
    "NoxoX", dynamic({"siteId": "4", "fileCount": "4", "pendingDays":[1,4,3,11]})
]
| mvexpand x = customDimensions.pendingDays to typeof(long)
| invoke arraySum()

// or

datatable (Operation_id:string, customDimensions:dynamic) 
[ 
    "MTFfq", dynamic({"siteId": "1", "fileCount": "3", "pendingDays":[15,10,11]}), 
    "LXVjk", dynamic({"siteId": "2", "fileCount": "1", "pendingDays":[3]}),
    "jnySt", dynamic({"siteId": "3", "fileCount": "2", "pendingDays":[7,11]}), 
    "NoxoX", dynamic({"siteId": "4", "fileCount": "4", "pendingDays":[1,4,3,11]})
]
| project Operation_id, sum = series_stats_dynamic(customDimensions.pendingDays)["avg"] * array_length(customDimensions.pendingDays)

Some references for operators/functions used above:

Aggregate contents of array column in Azure Data Explorer

1 Answers