CODEWITHSUNDEEP
jqueryjsonelasticsearchkibana
kadrian
kadrian

Reputation: 5009

Elasticsearch Query - How to aggregate timestamps and filter by term?

I have a working filter query:

{
  "filter": {
    "and": [{
      "range": {
        "timestamp": {
          "gte": "2015-05-07T12:04:30Z"
          "lte": "2015-08-07T12:04:30Z"
        }
      }
    }, {
      "term": {
        "in_context.course_id": "1234567"
      }
    }]
  },
  "fields": ["timestamp"]
}

which returns the timestamp of every event in the last three months. But there are ~ 1 million timestamps returned which makes the response too large, so I would like to aggregate the results and get the sum of documents per day.

I found this snippet that uses a date histogram:

{
    "aggs" : {
        "histo1" : {
            "date_histogram" : {
                "field" : "timestamp",
                "interval" : "day"
            },
            "aggs" : {
                "price_stats" : {
                    "stats" : {
                        "field" : "price"
                    }
                }
            }
        }
    }
}

which is exactly what I need, but I can't figure out how to incorporate it into my original query with the term filter! Would be really glad for help!

Upvotes: 3

Views: 1825

Answers (1)

Val
Val

Reputation: 217474

You can simply do it like this by adding the aggs part at the top level in your query. Note that I wrapped your filter into a filtered query.

{
  "size": 0,
  "query": {
    "filtered": {
      "filter": {
        "and": [
          {
            "range": {
              "timestamp": {
                "gte": "2015-05-07T12:04:30Z",
                "lte": "2015-08-07T12:04:30Z"
              }
            }
          },
          {
            "term": {
              "in_context.course_id": "1234567"
            }
          }
        ]
      }
    }
  },
  "aggs": {
    "histo1": {
      "date_histogram": {
        "field": "timestamp",
        "interval": "day"
      }
    }
  }
}

Upvotes: 1

Related Questions