Group by date and category elasticsearch query

Question

I have a index in elasticsearch and have imported below json file:

{"index" : {"_id": "1"}}
{"Name": "apple","Type": "fruit","Rate": "64","Date": "2016-01-24"}
{"index" : {"_id": "2"}}`enter code here`
{"Name": "grape","Type": "fruit","Rate": "100","Date": "2016-01-21"}
{"index" : {"_id": "3"}}
{"Name": "banana","Type": "fruit","Rate": "72","Date": "2016-01-14"}
{"index" : {"_id": "4"}}
{"Name": "orange","Type": "fruit","Rate": "82","Date": "2016-01-13"}
{"index" : {"_id": "5"}}
{"Name": "mango","Type": "fruit","Rate": "53","Date": "2016-02-16"}
{"index" : {"_id": "6"}}
{"Name": "grapes","Type": "fruit","Rate": "56","Date": "2016-02-18"}
{"index" : {"_id": "7"}}
{"Name": "blueberry","Type": "fruit","Rate": "96","Date": "2016-02-25"}
{"index" : {"_id": "8"}}
{"Name": "watermelon","Type": "fruit","Rate": "124","Date": "2016-02-12"}
{"index" : {"_id": "9"}}
{"Name": "papaya","Type": "fruit","Rate": "75","Date": "2016-03-09"}
{"index" : {"_id": "10"}}
{"Name": "carrot","Type": "vegetable","Rate": "25","Date": "2016-01-21"}
{"index" : {"_id": "11"}}
{"Name": "ladyfinger","Type": "vegetable","Rate": "89","Date": "2016-01-26"}
{"index" : {"_id": "12"}}
{"Name": "potato","Type": "vegetable","Rate": "36","Date": "2016-02-14"}
{"index" : {"_id": "13"}}
{"Name": "tomato","Type": "vegetable","Rate": "45","Date": "2016-02-07"}
{"index" : {"_id": "14"}}
{"Name": "spinach","Type": "vegetable","Rate": "25","Date": "2016-02-24"}
{"index" : {"_id": "15"}}
{"Name": "raddish","Type": "vegetable","Rate": "21","Date": "2016-02-13"}
{"index" : {"_id": "16"}}
{"Name": "pumpkin","Type": "vegetable","Rate": "78","Date": "2016-03-10"}
{"index" : {"_id": "17"}}
{"Name": "lemon","Type": "vegetable","Rate": "98","Date": "2016-03-11"}

now i want to get count of each Type for each month, till now i am able to find total count group by category by below elasticsearch query:

{
  "size": 0,
  "aggs": {
    "group_by_Type": {
      "terms": {
        "field": "Type"
      }
    }
  }
}

I want this count to be devided by month. Please help

Solution

Good start! Now you simply need to use a date_histogram aggregation with interval set to monthand then move your current terms aggregation as a sub-aggregation of the date_histogram aggregation, basically like this:

{
  "size": 0,
  "aggs": {
    "group_by_month": {
      "date_histogram": {
        "field": "Date",
        "interval": "month"
      },
      "aggs": {
        "group_by_Type": {
          "terms": {
            "field": "Type"
          }
        }
      }
    }
  }
}

UPDATE

If you only want to focus on a time period, you can add a query to restrain the document set to aggregate:

{
  "size": 0,
  "query": {
      "range": {
          "Date": {
              "gte": "2016-01-01T00:00:00.000Z",
              "lt": "2016-02-01T00:00:00.000Z"
          }
      }
  },
  "aggs": {
    "group_by_month": {
      "date_histogram": {
        "field": "Date",
        "interval": "month"
      },
      "aggs": {
        "group_by_Type": {
          "terms": {
            "field": "Type"
          }
        }
      }
    }
  }
}

Comments

[Vaibhav]

Thank you so much Val for quick reply, your query works. :)

[Vaibhav]

and what if I want to get details of a particular month like of January 2016 only.

[Vaibhav]

actually my Index is too large and my query is taking very long time.

[Val]

You can add a query to only focus on the desired time interval. I've updated my answer

[Vaibhav]

Tried updated query before but it is showing error: 'Unexpected string in JSON at position 187'

[Vaibhav]

I am using head plugin for querying.

[Val]

My bad sorry, I forgot one comma. Please try again