Elastic Search how to group by date (when nested in arrays)

Question

I have the database structured as :

{
    name:"string",
    ....
    arrayData: [ 
        { price:100, date: "yyyy-mm-dd" },
        { price:120, date: "yyyy-mm-dd" } ,
        { price:150, date: "yyyy-mm-dd" } ,
        { price:250, date: "yyyy-mm-dd" } 
    ]
}

How do I get the average price for 2015 ?

My current approach was to aggregate the price using this filter:

{
  "range": {
    "arrayData.date": {
      "gte": "2015-01-01",
      "lt": "2016-01-01"
    }
  }
}

The test aggregation :

"aggs": {
    "2": {
      "date_histogram": {
        "field": "arrayData.date",
        "interval": "1y",
        "time_zone": "Europe/Helsinki",
        "min_doc_count": 1
      },
      "aggs": {
        "1": {
          "avg": {
            "field": "arrayData.price"
          }
        }
      }
    }
  }

But this will also get the average for the other years in the documents with a matching date in arrayData.

This is also supposed to work with Kibana, as in the end I'll have to add it to the dashboard.

Answer 1

Seems I need to map the objects in the array as nested.

According to Elastic Documentation:

Arrays of inner object fields do not work the way you may expect. Lucene has no concept of inner objects, so Elasticsearch flattens object hierarchies into a simple list of field names and values. For instance, the following document:

{
  "user" : [ 
    {
      "first" : "John",
      "last" :  "Smith"
    },
    {
      "first" : "Alice",
      "last" :  "White"
    }
  ]
}

The user field is dynamically added as a field of type object and would be transformed internally into a document that looks more like this:

{
  "user.first" : [ "alice", "john" ],
  "user.last" :  [ "smith", "white" ]
}