Filter in aggregation

user_4486335 0
Viewed 67

say I have mapping:

{
  // ...other fields,
  "locations": {
    "type": "nested",
    "properties": {
      "countrySlug": { "type": "keyword" },
      "citySlug": { "type": "keyword" }
    }
  }
}

So this way, each document can have multiple locations:

{
  "locations": [
    {
      "countrySlug": "germany",
      "citySlug": "berlin"
    },
    {
      "countrySlug": "germany",
      "citySlug": "hamburg"
    },
    {
      "countrySlug": "poland",
      "citySlug": "krakow"
    },
    {
      "countrySlug": "italy",
      "citySlug": "milan"
    }
  ]
}

Now I want to get aggregation of city slugs where location contains countrySlug = "germany".

My query looks like this:

{
  "_source": false,
  "aggs": {
    "cities": {
      "filter": {
        "bool": {
          "must": [
            {
              "bool": {
                "should": [
                  {
                    "nested": {
                      "path": "locations",
                      "query": {
                        "bool": {
                          "must": {
                            "term": {
                              "locations.countrySlug": "germany"
                            }
                          }
                        }
                      }
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      "aggs": {
        "agg": {
          "nested": {
            "path": "locations"
          },
          "aggs": {
            "slugs": {
              "terms": {
                "field": "locations.citySlug",
                "size": 5
              },
              "aggs": {
                "top_reverse_nested": {
                  "reverse_nested": {}
                }
              }
            }
          }
        }
      }
    }
  },
  "size": 0
}

But it returns all city slugs that were found, eg:

berlin: 2

krakow: 1

milan: 3

My goal is to get just:

berlin: 2

(or other city slugs that are related to a location with countrySlug = "germany")

Am I missing anything? How to make something like "post filter" for aggregations?

Thanks, PS

elasticsearch
1 Answers

After filtering out all the documents where countrySlug is germany, you can put a nested aggregation on the returned records.

GET /cities/_search
{
  "size": 0,
  "aggs": {
    "cities": {
      "nested": {
        "path": "locations"
      },
      "aggs": {
        "filter_cities": {
          "filter": {
            "bool": {
              "filter": [
                {
                  "term": {
                    "locations.countrySlug": "germany"
                  }
                }
              ]
            }
          },
          "aggs": {
            "cities": {
              "terms": {
                "field": "locations.citySlug"
              }
            }
          }
        }
      }
    }
  }
}

The result for the above query:

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 5,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "cities" : {
      "doc_count" : 17,
      "filter_cities" : {
        "doc_count" : 9,
        "cities" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [
            {
              "key" : "hamburg",
              "doc_count" : 5
            },
            {
              "key" : "berlin",
              "doc_count" : 4
            }
          ]
        }
      }
    }
  }
}
user_63527720
Related