Is it possible to return a list of records after applying several filters?

[nisargtest1 (Nisarg Patel)]

I need to apply several filters like Group BY same id number, apply count on each group, and if count is more than one then I need to calculate the mean price of each group and return those values.
How will I be able to achieve this in Kibana?

Thanks in advance.

[myasonik (Michail Yasonik)]

Hi @nisargtest1! What have you tried so far?

It looks like you're trying to do this inside Canvas. Have you seen Canvas' function reference? There are descriptions and examples of the many functions Canvas supports.

[Catherine_Liu]

It's not possible to apply those as filters, but per Canvas element, you can write an expression to group by id number and carry out those calculations.

Here's an example expression using a demo dataset:

filters
| demodata
| ply by="project" 
    expression={
      if condition={rowCount | gt 1} 
        then={math "mean(price)"} 
        else={getCell "price"} 
      | as "average_price"
    } 
| table
| render

What this expression does is use demodata as the datasource, then with the ply function, it groups the dataset by unique values in the project field, and applies the provided sub-expression to each of those groups. This sub-expression checks if the rowCount of each of those groups is greater than 1. If it is, then it'll calculate the average of the values in the price field. Otherwise, it will just return the value of the price field is there is only a single row. The as "average_price" line just tells the function want to name this new calculated field.

For your use case, you would just need to swap out demodata for your datasource, the project field for your ID field, and the price field with the name of your price field.