Nested array of objects aggregation in Elasticsearch

Question

Documents in the Elasticsearch are indexed as such

Document 1

{
  "task_completed": 10
  "tagged_object": [
    {
      "category": "cat",
      "count": 10
    },
    {
      "category": "cars",
      "count": 20
    }
  ]
} 

Document 2

{
  "task_completed": 50
  "tagged_object": [
    {
      "category": "cars",
      "count": 100
    },
    {
      "category": "dog",
      "count": 5
    }
  ]
} 

As you can see that the value of the category key is dynamic in nature. I want to perform a similar aggregation like in SQL with the group by category and return the sum of the count of each category.

In the above example, the aggregation should return cat: 10, cars: 120 and dog: 5

Wanted to know how to write this aggregation query in Elasticsearch if it is possible. Thanks in advance.

Answer 1

You can achieve your required result, using nested, terms, and sum aggregation.

Adding a working example with index mapping, search query and search result

Index Mapping:

{
  "mappings": {
    "properties": {
      "tagged_object": {
        "type": "nested"
      }
    }
  }
}

Search Query:

{
  "size": 0,
  "aggs": {
    "resellers": {
      "nested": {
        "path": "tagged_object"
      },
      "aggs": {
        "books": {
          "terms": {
            "field": "tagged_object.category.keyword"
          },
          "aggs":{
            "sum_of_count":{
              "sum":{
                "field":"tagged_object.count"
              }
            }
          }
        }
      }
    }
  }
}

Search Result:

"aggregations": {
    "resellers": {
      "doc_count": 4,
      "books": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "cars",
            "doc_count": 2,
            "sum_of_count": {
              "value": 120.0
            }
          },
          {
            "key": "cat",
            "doc_count": 1,
            "sum_of_count": {
              "value": 10.0
            }
          },
          {
            "key": "dog",
            "doc_count": 1,
            "sum_of_count": {
              "value": 5.0
            }
          }
        ]
      }
    }
  }