Unable to create nested date aggregation query

Question

I am trying to create an ElasticSearch aggregation query which can generate sum or average of value in all my ingested documents.

The documents are of the format -

{
"weather":"cold",
"date_1":"2017/07/05",
"feedback":[
    {
    "date_2":"2017/08/07",
    "value":28,
    "comment":"not cold"
    },{
    "date_2":"2017/08/09",
    "value":48,
    "comment":"a bit chilly"
    },{
    "date_2":"2017/09/07",
    "value":18,
    "comment":"very cold"
    }, ...
    ]
}

I am able to create a sum aggregation of all "feedback.value" using "date_1" by using the following request -

GET _search
{
"query": {
        "query_string": {
                "query": "cold"
                 }
        },
"size": 0,
"aggs": {
        "temperature": {
                    "date_histogram":{
                                      "field" : "date_1",
                                      "interval" : "month"
                                      },
                      "aggs":{
                              "temperature_agg":{
                                                "terms": {
                                                        "field": "feedback.value"
                                                          }
                                                }
                              }

                    }
        }
}

However, I need to generate the same query across all documents aggregate based on "feedback.date_2". I am not sure if ElasticSearch can resolve such aggregation or how to approach it. Any guidance would be helpful

[EDIT] Mapping file( I only define the nested items, ES identifes other fields on its own)

{
 "mappings": {
  "catalog_item": {
   "properties": {

      "feedback":{
          "type":"nested",
          "properties":{
              "date_2":{
                  "type":   "date",
                  "format":"YYYY-MM-DD"
              }, 

                "value": {
                "type": "float"
              },

                "comment": {
                "type": "text"
              }


          }
        }

   }
  }
 }
}

Answer 1

You would need to make use of nested documents and sum aggregation.

Here's a working example:

Sample Mapping:

PUT test
{
  "mappings": {
    "doc": {
      "properties": {
        "feedback": {
          "type": "nested"
        }
      }
    }
  }
}

Add Sample document:

PUT test/doc/1
{
   "date_1": "2017/08/07",
  "feedback": [
    {
      "date_2": "2017/08/07",
      "value": 28,
      "comment": "not cold"
    },
    {
      "date_2": "2017/08/09",
      "value": 48,
      "comment": "a bit chilly"
    },
    {
      "date_2": "2017/09/07",
      "value": 18,
      "comment": "very cold"
    }
  ]
}

Calculate both the sum and average based on date_2.

GET test/_search
{
  "size": 0,
  "aggs": {
    "temperature_aggregation": {
      "nested": {
        "path": "feedback"
      },
      "aggs": {
        "temperature": {
          "date_histogram": {
            "field": "feedback.date_2",
            "interval": "month"
          },
          "aggs": {
            "sum": {
              "sum": {
                "field": "feedback.value"
              }
            },
             "avg": {
              "avg": {
                "field": "feedback.value"
              }
            }
          }
        }
      }
    }
  }
}