ES group by term, sort by timestamp, limit 1 to Table

Grafana Elasticsearch
1

Hi,

I have two related query questions:

One, I want to query ElasticSearch datasource to group by a term, sort by a timestamp, and to select the TOP result for each group. I don’t know how to build this in the Query Builder, but I do know how to build the raw JSON query, which uses the top_hit aggregation (which isnt in grafana). I’d like to output this data into a Table with the other key/value pairs in the document found.

The documents in the index I’m searching on are like so:

{ "fruit":"apple", "taste":5, "timestamp":100}
{ "fruit":"pear", "taste":5, "timestamp":110}
{ "fruit":"apple", "taste":4, "timestamp":200}
{ "fruit":"pear", "taste":8, "timestamp":90}
{ "fruit":"banana", "taste":5, "timestamp":100}

I want the resulting table to be:

Fruit    Taste    Time
Apple    4        200
Pear     5        110
Bannana  5        100

The JSON Query is:

{
    "aggs": {
        "group": {
            "terms": {
                "field": "fruit.keyword"
            },
            "aggs": {
                "1": {
                    "top_hits": {
                        "size": 1,
                        "sort": [
                            {
                                "timestamp": {
                                    "order": "desc"
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
}

I’m not sure how to get all the key/values pairs for each document. I played around with it for a while and found i could group by a term and order by timestamp (date histogram? i dont remember), but couldn’t get the pairs.

Also, following the example above, I want to be able to return the number of different types of fruits in a single stat. I tried doing Metric=count, Group By=terms=fruit.keyword but i get this.datapoints[a][1] is undefined.

Thanks

2

Yeah, this is also what I want. I think grafana should also provide ‘group by’ and ‘timestamp’ options for ‘raw documents’ for elasticsearch datasource.

3

{
“aggs”: {
“group”: {
“terms”: {
“field”: “fruit.keyword”
},
“aggs”: {
“top”: {
“terms”: {
“field”: “timestamp”,
“order”: {
“_key”: “desc”
}
}
}
}
}
}
}