Find the count of a specific field value in json array of objects.

[nikitha15]

Hi i have a json data which i am working on and i used fieldsummary to get data similar to below image.

sample example:

suppose i have my result like this

I want to get count of value "Denver" in the field values from the above image. I tried spath but it's not working.

output  should be  like:

value        Count

Denver         1

Any help is appreciated. thanks.

[nikitha15]

Thanks a ton. This worked.

[venkatasri · Accepted solution]

Hi @nikitha15 

Can you try this approach, you have to tune regex matching to your data.

| makeresults 
| eval value="[{\"name1\":\"val1\",\"count\":3},{\"name2\":\"val2\",\"count\":1}, {\"name3\":\"val3\",\"count\":2}]" 
| eval json_arrays=split(value,"},") 
| mvexpand json_arrays 
| rex field=json_arrays "\"(?<name>[^\"]+)\":\"(?<value>[^\"]+)\"\,\"count\":(?<count_val>\d+)" 
| table json_arrays name value count_val

Tried output,

---

An upvote would be appreciated and Accept solution if this reply helps!