Calculate Average of Field Values in an Array

Calculate Average of Field Values in a flat array using the array:reduceRow() function

Query

logscale
array:reduceRow("ages[]", var=x, function=avg(x))

Introduction

The array:reduceRow() function can be used together with the aggregate function avg() as the function argument to calculate the average of field values in a flat array.

In this example, the array:reduceRow() function is used to calculate the average age of the field ages and return the result in a field named _reduceRow._avg.

Example incoming data might look like this:

ages[0]ages[1]ages[2]
163264
153045
124
895767

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0[\Add Field/] result{{Result Set}} repo --> 0 0 --> result style 0 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    array:reduceRow("ages[]", var=x, function=avg(x))

    Produces two events, calculating the average value across the ages[] array for each event. The results are placed into the _avg field for each new event.

  3. Event Result set.

Summary and Results

The query is used to calculate averages for a given array for each event and is a shorthand version of using array:eval() specifically for processing each event.

Sample output from the incoming example data:

ages[0]ages[1]ages[2]_avg
16326437.333
15304530
1242.67
89576771

Note that the evaluation is per event, for example per row of the overall table of values across the array over all events. To calculate values across the column of values, use array:reduceColumn().