How to achieve grouping the results using field present inside a json array?

[ghostrider]

I have below format log messages. At the end I want to group the messages by BID. I tried using the below query but I am not getting any events even though there are events that qualify my query. 

{
"details" : [
{
"BID" : "123"
},
{
"BID" :  "456"
}
]
}

Expected Output : 

BID  Count
123  4
456  3

Query I am using :

{my_search}
| rex field=MESSAGE "(?<JSON>\{.*\})"
| spath input=JSON path=details.BID
| stats values(details.BID) as "BID" by CORRID
| stats count as Count by "BID"

[ghostrider]

Thanks you so much!! I just had one doubt. So these BIDs correspond to some static fields which describe what BID is. Like BID :  123 corresponds to Apple. This detail/description is however not present in the events or search results. Is there any way to configure these as static fields which will get displayed when the billing code is displayed Like below

Output

Description BID  Count
Apple       123  4
Orange      456  3

[kamlesh_vaghela]

@ghostrider 

You can create a lookup with BID and description mapping. And use the lookup in your search.

BID,Description
123,Apple

https://docs.splunk.com/Documentation/SplunkCloud/latest/Knowledge/ConfigureCSVlookups

https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Lookup

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.

[kamlesh_vaghela]

@ghostrider 

Can you please try this?

YOUR_SEARCH
| rename "details{}.BID" as BID
| stats count as Count by BID

OR

YOUR_SEARCH | spath
| rename "details{}.BID" as BID
| stats count as Count by BID

My Sample Search :

| makeresults | eval _raw="{
\"details\" : [
{
\"BID\" : \"123\"
},
{
\"BID\" :  \"456\"
}
]
}" | spath
| rename "details{}.BID" as BID
| stats count as Count by BID

I hope this will help you.

Thanks
KV
If any of my replies help you to solve the problem Or gain knowledge, an upvote would be appreciated.