How to write a search to get the counts of values in a JSON array and show the average ResponseTime by value?

[jmenziessmith]

I am trying to write a search that will give me counts of each value occurrence within an array in JSON.

My truncated JSON that is logged looks like this:

{
    "TimestampUtc": "2015-12-14T17:48:22.9603567Z",
    "Request": {
        "Body": {
            "SelectedColumns": [{
                "ColumnId": 1004
            }, {
                "ColumnId": 1000
            }, {
                "ColumnId": 1003
            }, {
                "ColumnId": 1107
            }, {
                "ColumnId": 1101
            }]
        }
    },
    "Metrics": {
        "ResponseTime": 199
    }
}

I need to show a table of ColumnIdValue Count where ColumnIdValue is the value of ColumnId, and Count is the number of times that value appears in the JSON events.

I'm not sure how to go about this. Does mvexpand help here?

I'd also like to then write a second search which will show the average ResponseTime by ColumnIdValue.

Thanks

[jmenziessmith · Accepted solution]

Ive worked out how to do the first part

source="*reportingservice.log.json"   User.OrganizationId=* 
| mvexpand Request.Body.SelectedColumns{}.ColumnId 
| rename Request.Body.SelectedColumns{}.ColumnId as ColumnId  
| stats count(ColumnId) as count by ColumnId 
| table  ColumnId, count 
| sort -count

And the second part

source="*reportingservice.log.json"   User.OrganizationId=* 
| mvexpand Request.Body.SelectedColumns{}.ColumnId 
| rename Request.Body.SelectedColumns{}.ColumnId as ColumnId  
| stats avg(Metrics.ResponseTime) as responsetime by ColumnId 
| table  ColumnId, responsetime
| sort -responsetime