How to get distinct values and their counts from fields arrays

[karol]

I got a stream of events in a following format:

[
  {
    "name": "event 1"
    "attributes": ["a", "b"],
  },
  {
    "name": "event 2"
    "attributes": ["a", "c"],
  }
]

I am looking to aggregate them in a following way:

a | 2
b | 1
c | 1

 The list is sorted in a descending order with counts for each unique entry in the attributes array.

[PrewinThomas]

@karol 

With JSON array, you can use below.

| makeresults
| eval raw="[{\"name\":\"event 1\", \"attributes\":[\"a\",\"b\"]}, {\"name\":\"event 2\", \"attributes\":[\"a\",\"c\"]}]"
| spath input=raw path={} output=events
| mvexpand events
| spath input=events path=attributes{} output=attribute
| stats count by attribute
| sort - count

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

[ITWhisperer · Accepted solution]

Is this part of a json structure? Assuming it is, you could do something like this

| makeresults
| eval _raw="{
\"array\": [
  {
    \"name\": \"event 1\",
    \"attributes\": [\"a\", \"b\"]
  },
  {
    \"name\": \"event 2\",
    \"attributes\": [\"a\", \"c\"]
  }
]}"
``` The lines above simulate something like the data you shared ```
| spath array{} output=array
| mvexpand array
| spath input=array attributes{} output=attributes
| stats count by attributes
| sort 0 -count