Aggregation on nested document with reverse_nested to count unique parent


(Andris Priedīte) #1

I have document type "ads" (title, url and similar fields) which have nested documents "views" (view time, user, date of birth, country and similar fields).

I'm trying to count ads per country, first by filtering on parent ("title") and nested docs ("date of birth").

I'm using reverse_nested to get this doc count. The problem is that "country_count" aggregation on the nested documents "views.country" returns me TOTAL number of the parent documents who has a nested "views" with this country. It ignores date of birth in the nested documents.

For example, if "ads" document has two "views":

  1. country: UK, dob: 1985-01-01
  2. country: DE, dob: 1970-01-01

Then following aggregation will return that both UK an DE have one document matching:

"countries": {
    "filter": {                        
        "and": [
            {"term": {"title": "free beer"}},
            {
                "nested": {
                    "path": "views",
                    "filter": {
                        "and": [
                            {"range": {"views.dob": {"gte": "1980-09-15", "lt": "1990-09-15"}}}
                        ]
                    }
                }
            }
        ]
    },       
    "aggs": {
        "in_to_views": {
            "nested": {
                "path" : "views"
            },
            "aggs": {
                "country_count": {
                    "terms": {"field": "views.country"},
                    "aggs": {
                        "back_to_ads": {"reverse_nested": {}}
                    }
                }
            }
        }
    }
}

(Martijn Van Groningen) #2

The nested aggregator delegates to all nested docs that parent doc matches with.
The easiest to get what you want is to repeat the range filter in the aggregations like this:

{
  "aggs": {
    "in_to_views": {
      "nested": {
        "path": "views"
      },
      "aggs": {
        "my-filter": {
          "filter": {
            "range": {
              "views.dob": {
                "gte": "1980-09-15",
                "lt": "1990-09-15"
              }
            }
          },
          "aggs": {
            "country_count": {
              "terms": {
                "field": "views.country"
              },
              "aggs": {
                "back_to_ads": {
                  "reverse_nested": {}
                }
              }
            }
          }
        }
      }
    }
  }
}

(system) #3