Elasticsearch Date range query with aggregation

Question

I am trying to execute following query. I have 3 attributes in document STATUS - Which can be "FAIL", "PASS" , "INVALID" DATE - contains date and time.

I want daily number of count for each status

eg : Date : 11-09-2016, STATUS : FAIL, count: 120
Date : 11-09-2016, STATUS : PASS, count: 150

I want data for last one month, two month and so on

 SearchRequest requestQuery =
Requests.searchRequest(ConstantsValue.indexName)
    .types(ConstantsValue._Type)
    .source("{size:999999,"
    + "\"_source\" : "
    + "[\"DTCREATED\", \"STATUS\"]"             
    + ",\"aggs\": "     
    + "{\"group_by_STATUS\": {\"terms\": {\"field\": \"STATUS\"},"
    + "\"aggs\" : "
    + "{\"group_by_DATE\" : {\"date_histogram\" : "
    + "{\"field\" : \"DTCREATED\", \"interval\" : \"day\","
    + "\"format\" : \"yyyy-MM-dd\" },"
    + "\"aggs\" : "
    + "{\"grades_count\" : { \"value_count\" : { \"field\" : \"STATUS\" } }}}}}}}");

This code gives me daily count of each status but for all records. and want to add range filter something like below.

+"\"query\": {"
+" \"filtered\": {"
+" \"filter\": {"
+ "\"range\": { \"DTCREATED\": { \"gte\": \"now-90d/d\" }}"
+"}}}}}");

But I am not able to merge content of these two queries. I have tried my best. Any help is greatly appreciated.

Answer 1

well you need add query.. you can combine query and aggregations like this :

"query": {
    "range": {
       "@timestamp": {
          "from": "now-90d"
       }
    }
},
"aggs" : {"..."}

the 'd' is represnt days. in this query you ask all the document from "now - 90 days" till today, and you add the aggragation from the last asnwer
you can look here for the query elasticsearch range query

Answer 2

If I understand you correctly, you need to use sub-aggregations.

The query looks something like this:

"aggs" : {
  "days" :{
    "date_histogram" : {
        "field" : "DTCREATED",
        "interval" : "day"
    }
  },
  "aggs" : {
    "statuse_in_day" : {
        "terms" : {
            "field" : "STATUS"
        }
    }
}

First you will get the buckets by day and inside each of this buckets you will get bucket by status value.