Elasticsearch - Aggregate on nested field, then on field outside the nesting

Question

I have this mapping :

{
    "event": {
        "properties": {
            "visitor": {
                "type": "keyword"
            },
            "location": {
                "type": "nested",
                "properties": {
                    "country": {
                        "type": "keyword"
                    },
                    "region": {
                        "type": "keyword"
                    },
                    "city": {
                        "type": "keyword"
                    }
                }
            }
        }
    }
}

These two aggregations work as intended :

{
   "size": 0,
   "aggs": {
      "location": {
         "nested": {
            "path": "location"
         },
         "aggs": {
            "by_country": {
               "terms": {
                  "field": "location.country"
               }
            }
         }
      }
   }
}

{
   "size": 0,
   "aggs": {
      "visitor_count": {
         "cardinality": {
            "field": "visitor"
         }
      }
   }
}

But when I try to combine them like this, the country aggregation works fine, but the visitor counts are all equal to 0, which is wrong.

{
   "size": 0,
   "aggs": {
      "location": {
         "nested": {
            "path": "location"
         },
         "aggs": {
            "by_country": {
               "terms": {
                  "field": "location.country"
               },
               "aggs": {
                  "visitor_count": {
                     "cardinality": {
                        "field": "visitor"
                     }
                  }
               }
            }
         }
      }
   }
}

Can someone tell me how to achieve what I'm trying to do ? I guess the issue is that the visitor field is not part of the nested location field, but I can't find a workaround. (Of course, the actual mapping is more complicated and I really would like to avoid changing it)

score 0 · Accepted Answer · answered Oct 05 '17 at 15:08

Ok, turns out the magical keyword was "reverse_nested". This code did the trick for me :

{
   "size": 0,
   "aggs": {
      "location": {
         "nested": {
            "path": "location"
         },
         "aggs": {
            "by_country": {
               "terms": {
                  "field": "location.country"
               },
               "aggs": {
                  "reverse": {
                     "reverse_nested": {},
                     "aggs": {
                        "visitor_count": {
                           "cardinality": {
                              "field": "visitor"
                           }
                        }
                     }
                  }
               }
            }
         }
      }
   }
}

Elasticsearch - Aggregate on nested field, then on field outside the nesting

1 Answers1