How to aggregate results by elements in the array, and sort them by ratio

[karol]

I got my data stream in a following format:

[
    {
        "name": "event 1"
        "attributes": [false, true, true],
    },
    {
        "name": "event 2"
        "attributes": [false, false, true],
    }
]

I want to output them sorting by a ratio of `true` to `false` values in the array:

name| true count | false count | percentage

event 1 | 2 | 1 | 66.67%
event 2 | 1 | 2 | 33.33%

[PrewinThomas · Accepted solution]

@karol 

You can use spath and mvexpand on your field.

| spath input=attributes
| mvexpand attributes
| eval true_count=if(attributes="true", 1, 0), false_count=if(attributes="false", 1, 0)
| stats sum(true_count) as true_count sum(false_count) as false_count by name
| eval percentage=round((true_count / (true_count + false_count)) * 100, 2)
| sort - percentage

Regards,
Prewin
If this answer helped you, please consider marking it as the solution or giving a Karma. Thanks!