Grafana Elasticsearch table aggregate by time and then terms for multiple metrics

  • What Grafana version and what operating system are you using?
    Grafana v10.2.2

  • What are you trying to achieve?
    In Table visualization, I want to aggregate by both time and terms, then show multiple metrics in the table.

In SQL terms, it would be like this:

  • How are you trying to achieve it?
    Firstly, I tried to group by Terms and then by Date Histogram, it shows multiple frames.

  • What happened?
    Then, I tried Reduce to rows, but SUM and COUNT go to rows as well.

Moreover, I tried Join by Field, and it spreads out in columns.

  • What did you expect to happen?
    What I expect is I can “group by” time and terms, and then show multiple metrics like SUM and COUNT in a table, something like this:

Welcome @blue86321

I highly recommend you look into using infinity grafana plugin and hit the 9200 endpoint and using UQL query language you have a whole log more flexibility.

Can you share a sample ES document you are working with minus sensitive data, just obfuscate it

Thank you for your advice, I’ll go check out that plugin later.

Here’s my ES doc, it’s demo data so I don’t mind sharing it.

  "_source": {
      "@timestamp": "2023-12-07T01:11:23.684Z",
      "log_name": "myapp-order",
      "order": {
        "order_id": "f5ca4d4f-11ec-4a1f-b057-87baefb02fec",
        "status": "PENDING",
        "amount": 5700,
        "order_time": "2023-12-07T01:10:15.685583",
        "pay_time": "2023-12-07T01:11:23.684537"
      "user": {
        "uid": "4",
        "anonymous": false
      "time": "2023-12-07T01:11:23.684537"
1 Like

to get you started take a look at this jsonata query using sample es doc, which is a feature of infinity which gives you a lot of flexibility

Here is jsonata docu

Hi, @yosiasz
I tried the Infinity plugin, but I don’t think it solves my problem.
In Elasticsearch, if we write a query via API, we need to use request body for a GET request (see the following code block), which Infinity doesn’t support.

GET /_search
  "query": {
    "query_string": {
      "query": "(new york city) OR (big apple)",
      "default_field": "content"

Therefore, I still suffer from my original problem :frowning: but thank you for your help!

Might want to read up on the documentation and poke around the plugin options


Yes, I did. That’s why I said it seems like it doesn’t support request body for a GET request.

As you can see from the screenshot, there are only Headers and Query Params options, but no body option.
body option will pop up only when we select a POST method.

I do not see a body in your GET request

If you see specs of GET

Not sure if es does so something different with use of body in GET

In Elasticsearch, we do need body to query data, as I mentioned earlier in GET /_search.

Here is an example of how we query data in index myapp-order-2023.12 and look up all docs with its order.status equal to PENDING

curl -L -X GET 'http://localhost:9200/myapp-order-2023.12/_search' \
-H 'Content-Type: application/json' \
-d '{
  "query": {
    "term": {
      "order.status.keyword": {
        "value": "PENDING"

I know it didn’t follow the spec, but in reality, there are some scenarios in which we need something that is not standard according to the spec.

So, Infinity plugin cannot solve my current problem anyway. It seems like for Elasticsearch in Grafana, there is no way we can get the result that I mentioned at the very beginning of my problem.

1 Like