Nested aggregation and filter in ElasticSearch

Question

I have an issue with nested aggregation and filter, basically without filter it returns sum for global scope but with nested doc_count is OK but sum is always 0, here is query I am trying to run:

{
    "query": {
        "nested": { 
          "path": "skills.tree",
          "query": {
            "bool" : {
              "must" : [
                {"match": {"leaf0": "Management"}},
                {"match": {"leaf1": "Financial"}}
              ]
            }
          }
        }
      },
    "aggs": {
        "by_org": {
              "terms": { 
                "field":    "org"
              },
              "aggs": {
                "sum_weight0-filtered": {
                  "filter": {
                    "nested": {
                      "path": "skills.tree",
                      "query": {
                        "bool" : {
                          "must" : [
                            {"match": {"leaf0": "Management"}},
                            {"match": {"leaf1": "Financial"}}
                          ]
                        }
                      }
                    }
                  },
                  "aggs":{
                    "sum0":{
                      "sum": { 
                        "field": "skills.tree.weight0"
                      }
                    },
                    "sum1":{
                      "sum": { 
                        "field": "skills.tree.weight1"
                      }
                    }
                  }
                }
              }
        }
    }
}

and below is a sample output:

{
   "took": 978,
   "timed_out": false,
   "_shards": {
      "total": 50,
      "successful": 50,
      "failed": 0
   },
   "hits": {
      "total": 11337,
      "max_score": 0,
      "hits": []
   },
   "aggregations": {
      "by_org": {
         "buckets": [
            {
               "key": "Aetna",
               "doc_count": 1888,
               "sum_weight0-filtered": {
                  "doc_count": 1888,
                  "sum0": {
                     "value": 0
                  },
                  "sum1": {
                     "value": 0
                  }
               }
            },
            {
               "key": "AECOM",
               "doc_count": 1085,
               "sum_weight0-filtered": {
                  "doc_count": 1085,
                  "sum0": {
                     "value": 0
                  },
                  "sum1": {
                     "value": 0
                  }
               }
            }
....

and here is part schema:

'skills'        => array(
                                'properties'    => array(
                                    'tree'  => array(
                                        'type'  => 'nested',
                                        'properties'    => array(
                                            'leaf0' => array(
                                                "type"      => "multi_field",
                                                "fields"    => array(
                                                    "leaf0"=> array(
                                                        "type"  => "string",
                                                        "index" => "not_analyzed"
                                                    ),
                                                    "search"        => array(
                                                        "type"  => "string",
                                                        "index" => "analyzed"
                                                    )
                                                )
                                            ),
                                            'leaf1' => array(
                                                "type"      => "multi_field",
                                                "fields"    => array(
                                                    "leaf1"=> array(
                                                        "type"  => "string",
                                                        "index" => "not_analyzed"
                                                    ),
                                                    "search"        => array(
                                                        "type"  => "string",
                                                        "index" => "analyzed"
                                                    )
                                                )
                                            ),
                                            'leaf2' => array(
                                                "type"      => "multi_field",
                                                "fields"    => array(
                                                    "leaf2"=> array(
                                                        "type"  => "string",
                                                        "index" => "not_analyzed"
                                                    ),
                                                    "search"        => array(
                                                        "type"  => "string",
                                                        "index" => "analyzed"
                                                    )
                                                )
                                            ),
                                            'leaf3' => array(
                                                "type"      => "multi_field",
                                                "fields"    => array(
                                                    "leaf3"=> array(
                                                        "type"  => "string",
                                                        "index" => "not_analyzed"
                                                    ),
                                                    "search"        => array(
                                                        "type"  => "string",
                                                        "index" => "analyzed"
                                                    )
                                                )
                                            ),
                                            'leaf4' => array(
                                                "type"      => "multi_field",
                                                "fields"    => array(
                                                    "leaf4"=> array(
                                                        "type"  => "string",
                                                        "index" => "not_analyzed"
                                                    ),
                                                    "search"        => array(
                                                        "type"  => "string",
                                                        "index" => "analyzed"
                                                    )
                                                )
                                            ),
                                            'leaf5' => array(
                                                "type"      => "multi_field",
                                                "fields"    => array(
                                                    "leaf5"=> array(
                                                        "type"  => "string",
                                                        "index" => "not_analyzed"
                                                    ),
                                                    "search"        => array(
                                                        "type"  => "string",
                                                        "index" => "analyzed"
                                                    )
                                                )
                                            ),
                                            'weight1' => array(
                                                'type'      => 'integer',
                                            ),
                                            'weight2' => array(
                                                'type'      => 'integer',
                                            ),
                                            'weight3' => array(
                                                'type'      => 'integer',
                                            ),
                                            'weight4' => array(
                                                'type'      => 'integer',
                                            ),
                                            'weight5' => array(
                                                'type'      => 'integer',
                                            )
                                        )
                                    )

The problem is in regards to sum0 and sum1 they all return 0 despite values being in there (it works on higher scope (no filter)). What am I doing wrong here?

Answer 1

The issue may simply be how you are accessing the nested fields, specifically that you have to direct those match statements against the search subfields of leaf0 and leaf1 - based upon your mapping definition, the subfields are ones that are actually analyzed. With that in mind, try the following:

{
    "query": {
        "nested": {
          "path": "skills.tree",
          "query": {
            "bool" : {
              "must" : [
                {"match": {"tree.leaf0.search": "Management"}},
                {"match": {"tree.leaf1.search": "Financial"}}
              ]
            } 
          }
        }
      },  
    "aggs": {
        "by_org": {
              "terms": {
                "field":    "org"
              },
              "aggs": {
                "sum_weight0-filtered": {
                  "filter": {
                    "nested": {
                      "path": "skills.tree",
                      "query": {
                        "bool" : {
                          "must" : [
                            {"match": {"tree.leaf0.search": "Management"}},
                            {"match": {"tree.leaf1.search": "Financial"}}
                          ]
                        } 
                      }
                    }
                  },  
                  "aggs":{
                    "tree" : {
                      "nested" : {"path" : "skills.tree"},
                      "aggs" : {
                        "sum0" : {
                          "sum": {
                            "field": "tree.weight0"
                          }
                        },
                        "sum1": {
                          "sum": {
                            "field": "tree.weight1"
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
    }
}

I got this to work with a tiny contrived test set of data - it may be worth noting that I directed the query against the index at large and not against a particular document type (since in your originally posted query, the nested paths appear to be "fully" qualified).

Answer 2

The nested filter that you have applied , applies only to the condition and not where the aggregation will look for the values in subsequent aggregations. This means that the sum value is present in the nested object and not on the parent document and hence you got 0. Now if you use nested aggregation to ask ES to do aggregation on nested objects , it should work -

{
  "query": {
    "nested": {
      "path": "skills.tree",
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "leaf0": "Management"
              }
            },
            {
              "match": {
                "leaf1": "Financial"
              }
            }
          ]
        }
      }
    }
  },
  "aggs": {
    "by_org": {
      "terms": {
        "field": "org"
      },
      "aggs": {
        "sum_weight0-filtered": {
          "filter": {
            "nested": {
              "path": "skills.tree",
              "query": {
                "bool": {
                  "must": [
                    {
                      "match": {
                        "leaf0": "Management"
                      }
                    },
                    {
                      "match": {
                        "leaf1": "Financial"
                      }
                    }
                  ]
                }
              }
            }
          },
          "aggs": {
            "nestedAgg": {
              "nested": {
                "path": "skills.tree"
              },
              "aggs": {
                "sum0": {
                  "sum": {
                    "field": "skills.tree.weight0"
                  }
                },
                "sum1": {
                  "sum": {
                    "field": "skills.tree.weight1"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}