0

Suppose I have a mapping that stores physical attributes of people, and a field in that mapping that is the user id. For instance:

    "attributes": {
      "hair_color": {
        "type": "string"
      },
      "eyes_color": {
        "type": "string"
      },
      "height": {
        "type": "float"
      },
      "user_id": {
        "type": "integer"
      }
    }

I'm trying to make a query that will return how many people have a given eye color. For example, it would return something like "green": 962.

I think that what I need to do is a terms bucket for the eye_color field and then a cardinality sub aggregation that takes into account user_id, but I haven't been successful so far. This is what I have:

{
"aggs" : {
    "eyes_color_bucket" : {
        "terms" : {
            "field" : "eyes_color"
        }
    },
    "aggs":{
        "count":{
            "cardinality":{
                "field": "eyes_color_bucket"
            }
        }
    }
}

Which of course fails. Any help appreciated.

Sumit
  • 2,190
  • 23
  • 31
AArias
  • 2,558
  • 3
  • 26
  • 36

1 Answers1

1

You're almost there, try it like this:

{
  "size": 0,
  "aggs": {
    "eyes_color_bucket": {
      "terms": {
        "field": "eyes_color"
      },
      "aggs": {
        "count": {
          "cardinality": {
            "field": "user_id"
          }
        }
      }
    }
  }
}

UPDATE

Following up on Richa's comment below, if you make the assumption that one user will only have one eye color (i.e. no lenses or whatever), you can simplify your aggregation query like this:

{
  "size": 0,
  "aggs": {
    "eyes_color_bucket": {
      "terms": {
        "field": "eyes_color"
      }
    }
  }
}

The doc_count you get in each bucket should be the number of users having that eye color. Kudos to @Richa for bringing this up.

Val
  • 207,596
  • 13
  • 358
  • 360
  • Won't doc_count for any key would give the count of people? Or I am missing something? – Richa Feb 12 '16 at 09:21
  • 1
    Yes, only if there's only one document for each `user_id` / `eyes_color` pairs. One could imagine to store multiple eyes colors for each individual if they consider using lenses. Unlikely, but possible. – Val Feb 12 '16 at 09:24
  • thnx for the explanation. – Richa Feb 12 '16 at 09:27
  • This is the right answer for what I asked, but I now realize that I asked the wrong thing. Gonna post a new question now explaining myself better. – AArias Feb 12 '16 at 09:42
  • [Heres the new question if you want to take a look](http://stackoverflow.com/questions/35360676/reverse-cardinality-in-elasticsearch) – AArias Feb 12 '16 at 10:50