Aggregate only matched nested object values in ElasticSearch

Question

I need to sum only the values on the nested objects that match the query. It looks like ElasticSearch determines the documents matching the query and then sums across all of the nested objects. From the below outline I want to search on nestedobjects.objtype="A" and get back the sum of objvalue only for matching nestedobjects, I want to get the value 4. is this possible? If so, how?

Here is the mapping

{
  "myindex": {
    "mappings": {
      "mytype": {
        "properties": {
           "nestedobjects": {
             "type": "nested",
             "include_in_parent": true,
             "properties": {
               "objtype": {
                 "type": "string"
               },
               "objvalue": {
                 "type": "integer"
               }
             }
           }
         }
       }
     }
   }
 }

Here are my documents

PUT /myindex/mytype/1
{
  "nestedobjects": [
    { "objtype": "A", "objvalue": 1 },
    { "objtype": "B", "objvalue": 2 }
  ]
}
PUT /myindex/mytype/2
{
  "nestedobjects": [
    { "objtype": "A", "objvalue": 3 },
    { "objtype": "B", "objvalue": 3 }
  ]
}

Here is my query code.

POST allscriptshl7/_search?search_type=count
{
  "query": {
    "filtered": {
      "query": {
        "query_string": {
          "query": "nestedobjects.objtype:A"
        }
      }
    }
  },
  "aggregations": {
    "my_agg": {
      "sum": {
        "field": "nestedobjects.objvalue"
      }
    }
  }
}

Sloan Ahrens · Accepted Answer · 2015-10-01 20:38:59Z

10

Since both (outer) documents match the condition that one of their inner documents match the query, both outer documents are returned, and the aggregation is calculated against all of the inner documents belonging to those outer documents. Whew.

Anyway, this seems to do what you're wanting, I think, using filter aggregation:

POST /myindex/_search?search_type=count
{
   "aggs": {
      "nested_nestedobjects": {
         "nested": {
            "path": "nestedobjects"
         },
         "aggs": {
            "filtered_nestedobjects": {
               "filter": {
                  "term": {
                     "nestedobjects.objtype": "a"
                  }
               },
               "aggs": {
                  "my_agg": {
                     "sum": {
                        "field": "nestedobjects.objvalue"
                     }
                  }
               }
            }
         }
      }
   }
}
...
{
   "took": 4,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 2,
      "max_score": 0,
      "hits": []
   },
   "aggregations": {
      "nested_nestedobjects": {
         "doc_count": 4,
         "filtered_nestedobjects": {
            "doc_count": 2,
            "my_agg": {
               "value": 4,
               "value_as_string": "4.0"
            }
         }
      }
   }
}

Here is some code I used to test it:

http://sense.qbox.io/gist/c1494619ff1bd0394d61f3d5a16cb9dfc229113a

Very well-structured question, by the way.

answered Oct 1, 2015 at 20:38

Sloan Ahrens

8,7382 gold badges32 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sebastian Over a year ago

No real solution: Depends on pre-known values for the filter, doesn't work with histogram, date_histogram or terms with unpredictable keys.

Collectives™ on Stack Overflow

Aggregate only matched nested object values in ElasticSearch

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related