Date range filtering does not affect date histogram aggregation

I have some issues with filtering on a nested date field when doing aggregations. I want to separate the filtering and aggregation logic so that I can use the same filtering for different aggregations. This has been possible for all my queries except when filtering on a date range, as shown below:

{
    "query": {
        "nested": {
            "path": "transactions",
            "query": {
                "range": {
                    "transactions.date": {
                        "gte": "2013",
                        "lte": "2014",
                        "format": "yyyy"
                    }
                }
            }
        }
    },
    "aggs" : {
        "transactions": {
            "nested": {
                "path": "transactions"
            },
            "aggs": {
                "count_per_year": {
                    "date_histogram": {
                        "field": "transactions.date",
                        "interval": "year"
                    }
                }   
            }
        }
    }
}

Which I was assuming would limit the aggregation to only include transactions in 2013 and 2014, however, the query returns the following result:

"aggregations": {
    "transactions": {
        "doc_count": 23302,
        "count_per_year": {
            "buckets": [
                {
                    "key_as_string": "2013-01-01T00:00:00.000Z",
                    "key": 1356998400000,
                    "doc_count": 181
                },
                {
                    "key_as_string": "2014-01-01T00:00:00.000Z",
                    "key": 1388534400000,
                    "doc_count": 8363
                },
                {
                    "key_as_string": "2015-01-01T00:00:00.000Z",
                    "key": 1420070400000,
                    "doc_count": 9621
                },
                {
                    "key_as_string": "2016-01-01T00:00:00.000Z",
                    "key": 1451606400000,
                    "doc_count": 5137
                }
            ]
        }
    }
}

Another attempt has been the following:

{
	"aggs": {
		"filtered": {
			"filter": {
				"nested": {
					"path": "transactions",
					"query": {
						"range": {
							"transactions.date": {
								"gte": "2014-01-01",
		                        "lte": "2017-01-01",
		                        "format": "yyyy-MM-dd"
							}
						}	
					}
				}
			},
			"aggs": {
				"data": {
					"nested": {
						"path": "transactions"
					},
					"aggs": {
						"spend_per_year": {
							"date_histogram": {
								"field": "transactions.date",
								"interval": "month"
							}
						}
					}
				}
			}
		}
	}
}

I have been successful in doing a filter aggregation that limits the aggregation values in the following way:

{
		
	"aggs": {
		"transactions": {
			"nested": {
				"path": "transactions"
			},
			"aggs": {
				"filtered": {
					"filter": {
						"range": {
							"transactions.date": {
								"gte": "2013",
								"lte": "2014",
								"format": "yyyy"
							}
						}
					},
					"aggs": {
						"spend_per_year": {
							"date_histogram": {
								"field": "transactions.date",
								"interval": "month"
							}
						}
					}
				}
			}
		}
	}
}

But I am wondering what is wrong with the two first queries? Any idea why the filtering is ignored by ES?

1 Like

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.