ElasticSearch: How to aggregate values from nested field with date range?

Question

I have an index in ES that holds many kinds of statistics of the ads split by dates. each doc contains the statistics of the days that the ad was active at.

I want to make an aggs that sums all statistics (clicks on ad for example) in range of dates group by country name.

doc example:

    "_index" : "ads_statistics",
    "_type" : "_doc",
    "_id" : "GKfumIMBQ_B0VJNY8PsU",
    "_score" : 9.962151,
    "_source" : {
      "statistics_by_date" : [
         {
          "date" : "2022-08-09 00:00:00",
          "countries" : [
            {
              "name" : "USA",
              "clicks" : 901
            },
            {
              "name" : "FR",
              "clicks" : 250
            },
            {...}
          ]
        }, {
          "date" : "2022-08-10 00:00:00",
          "countries" : [
            {
              "name" : "USA",
              "clicks" : 825
            },
            {
              "name" : "FR",
              "clicks" : 411
            },
            {...}
          ]
        }, {
          "date" : "2022-08-11 00:00:00",
          "countries" : [
            {
              "name" : "USA",
              "clicks" : 523
            },
            {
              "name" : "CZ",
              "clicks" : 23
            },
            {...}
          ]
        } }

I've write this code, but it returns 0s in aggs results:

query:

  "aggs": {
"statistics_by_country": {
  "nested": {
    "path": "statistics_by_date.countries"
  },
  "aggs": {
    "country_terms": {
      "terms": {
        "field": "statistics_by_date.countries.name.keyword"
      },
      "aggs": {
        "filter": {
          "filter": {
            "range": {
              "statistics_by_date.date": {
                "gte": "2022-08-10",
                "lte": "2022-08-11"
              }
            }
          },
          "aggs": {
            "total_clicks": {
              "sum": {
                "field": "statistics_by_date.countries.clicks"
              }
            }
          }
        }
      }
    }
  }
}

}

response:

      "aggregations" : {
      "statistics_by_country" : {
      "doc_count" : 189,
      "country_terms" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 129,
        "buckets" : [
          {
            "key" : "USA",
            "doc_count" : 3,
            "filter" : {
              "doc_count" : 0,
              "total_clicks" : {
                "value" : 0.0
              }
            }
          },
          {
            "key" : "FR",
            "doc_count" : 2,
            "filter" : {
              "doc_count" : 0,
              "total_clicks" : {
                "value" : 0.0
              }
            }
          },
          {
            "key" : "CZ",
            "doc_count" : 1,
            "filter" : {
              "doc_count" : 0,
              "total_clicks" : {
                "value" : 0.0
              }
            }
          }
        ]
      }
    }
  }

notes:

• statistics_by_date.countries and statistics_by_date from nested type in doc's mapping.
• statistics_by_date.date from type date

My ideal result with the range of 08-10 : 08-11 is:

• USA : 825 + 523 = 1348
• FR : 411
• CZ : 23

What do I do wrong ?

Answer 1

Try this query. Note that I edited the filter clause for the nested query.

{
  "size": 0,
  "aggs": {
    "statistics_by_country": {
      "nested": {
        "path": "statistics_by_date.countries"
      },
      "aggs": {
        "country_terms": {
          "terms": {
            "field": "statistics_by_date.countries.name.keyword"
          },
          "aggs": {
            "filter": {
              "filter": {
                "nested": {
                  "path": "statistics_by_date",
                  "query": {
                    "range": {
                      "statistics_by_date.date": {
                        "gte": "2022-08-10 00:00:00",
                        "lte": "2022-08-11 00:00:00"
                      }
                    }
                  }
                }
              },
              "aggs": {
                "total_clicks": {
                  "sum": {
                    "field": "statistics_by_date.countries.clicks"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Mapping simulate

PUT idx_test
{
  "mappings": {
    "properties": {
      "statistics_by_date":{
        "type": "nested",
        "properties": {
          "date": {
            "type": "date",
            "format":"yyyy-MM-dd HH:mm:ss"
          },
          "countries": {
            "type": "nested"
          }
        }
      }
    }
  }
}

POST idx_test/_doc
{
  "statistics_by_date": [
    {
      "date": "2022-08-09 00:00:00",
      "countries": [
        {
          "name": "USA",
          "clicks": 901
        },
        {
          "name": "FR",
          "clicks": 250
        }
      ]
    },
    {
      "date": "2022-08-10 00:00:00",
      "countries": [
        {
          "name": "USA",
          "clicks": 825
        },
        {
          "name": "FR",
          "clicks": 411
        }
      ]
    },
    {
      "date": "2022-08-11 00:00:00",
      "countries": [
        {
          "name": "USA",
          "clicks": 523
        },
        {
          "name": "CZ",
          "clicks": 23
        }
      ]
    }
  ]
}