elasticsearch terms and sum aggregation

Question

I have documents in elasticsearch (1.5) that looks like:

{
    "gender": [
        {
            "name": "unknown",
            "value": 12
        },
        {
            "name": "male",
            "value": 89
        },
        {
            "name": "female",
            "value": 84
        } 
    ]
}

• not all of the documents contains the three options (male/female/unknown)

i would like to get the sum of all values per each gender name. like that:

{
    "buckets": [
        {
            "key": "unknown",
            "doc_count": 112,
            "gender_a": {
                "value": 462
            }
        },
        {
            "key": "male",
            "doc_count": 107,
            "gender_a": {
                "value": 438
            }
        },
        {
            "key": "female",
            "doc_count": 36,
            "gender_a": {
                "value": 186
            }
        }
    ]
}

i tried this query:

{
    "aggs": {
        "gender_name": {
            "terms": {
                "field": "gender.name"
            },
            "aggs": {
                "gender_sum": {
                    "sum": {
                        "field": "gender.value"
                    }
                }
            }
        }
    }
}

but something weird is going on, and i don't get the right values.

any idea what i am missing ?

Solution

You will probably need to make sure that your "gender" property has type "nested". With that, I was able to make the following do what I think you're asking.

First I set up a simple index:

PUT /test_index
{
   "mappings": {
      "doc": {
         "properties": {
            "gender": {
               "type": "nested",
               "properties": {
                  "name": {
                     "type": "string"
                  },
                  "value": {
                     "type": "long"
                  }
               }
            }
         }
      }
   }
}

Then added a couple of docs:

PUT /test_index/doc/1
{
    "gender": [
        {
            "name": "unknown",
            "value": 12
        },
        {
            "name": "male",
            "value": 89
        },
        {
            "name": "female",
            "value": 84
        } 
    ]
}

PUT /test_index/doc/2
{
    "gender": [
        {
            "name": "male",
            "value": 8
        },
        {
            "name": "female",
            "value": 4
        } 
    ]
}

Then I was able to get total counts by gender name as follows:

POST /test_index/_search?search_type=count
{
   "aggs": {
      "genders": {
         "nested": {
            "path": "gender"
         },
         "aggs": {
            "gender_terms": {
               "terms": {
                  "field": "gender.name"
               },
               "aggs": {
                  "gender_name_value_sums": {
                     "sum": {
                        "field": "gender.value"
                     }
                  }
               }
            }
         }
      }
   }
}
...
{
   "took": 1,
   "timed_out": false,
   "_shards": {
      "total": 1,
      "successful": 1,
      "failed": 0
   },
   "hits": {
      "total": 2,
      "max_score": 0,
      "hits": []
   },
   "aggregations": {
      "genders": {
         "doc_count": 5,
         "gender_terms": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
               {
                  "key": "female",
                  "doc_count": 2,
                  "gender_name_value_sums": {
                     "value": 88,
                     "value_as_string": "88.0"
                  }
               },
               {
                  "key": "male",
                  "doc_count": 2,
                  "gender_name_value_sums": {
                     "value": 97,
                     "value_as_string": "97.0"
                  }
               },
               {
                  "key": "unknown",
                  "doc_count": 1,
                  "gender_name_value_sums": {
                     "value": 12,
                     "value_as_string": "12.0"
                  }
               }
            ]
         }
      }
   }
}

Here is the code I used to test it:

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

Comments

[Udy]

Can you please explain why I need it to be nested? Is it the only option? Or maybe where can I find documentation about this? Thanks!

[Sloan Ahrens]

Elasticsearch has to have a way to group the documents. Here is a decent explanation of when to use the nested type. Your other option is the parent/child relationship, though this probably wouldn't be the best use-case.