Terms aggregation based on unique key

Question

I have an index full of documents. Each of them has a key "userid" with a distinct value per user, but each user may have multiple documents. Each user has additional properties (like "color", "animal").

I need to get the agg counts per property which would be:

aggs: {
   colors: { terms: { field: color } },
   animals: { terms: { field: animal } }
}

But I need these counts per unique userid, maybe:

aggs: {
   group-by: { field: userid },
   sub-aggs: {
      colors: { terms: { field: color } },
      animals: { terms: { field: animal } }
   }
}

I looked at the nested aggregations, but didn't get it if they'd be helpful.

Is this possible?

Answer 1

To nest the terms (similar to a GROUP BY in SQL) just create more aggregation levels.

It's not clear what totals you want out at the lowest level, but this query will return document counts for the three different levels:

curl -XGET 'http://localhost:9200/myindex/mypets/_search?pretty' -d '{
  "query": {
    "query_string": { "query":"some query", "fields": ["field1", "field2"]}
  },
  "aggs" : {
      "userid_agg" : {
        "terms": { "field" : "userid"},
        "aggs" : {
           "colors_agg" : {
               "terms": { "field" : "color"},
               "aggs" : {
                  "animals_agg" : {
                      "terms": { "field" : "animal"}
                   }
                }
            }                 
          }
       }
    }
}'

Answer 2

Here is what I finally found by hints from the other answer and the ES documentation:

curl -sSd '
{
   "aggs" : {
      "colors" : {
         "aggs" : {
            "users" : {
               "cardinality" : {
                  "field" : "userid"
               }
            }
         },
         "terms" : {
            "field" : "color"
         }
      }
   }
}' 'http://localhost:9200/index/type/_search?size=0&pretty'        

{
  "took" : 806,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 5288447,
    "max_score" : 0.0,
    "hits" : [ ]
  },
  "index" : {
    "colors" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [ {
        "key" : "red",
        "doc_count" : 1185936,
        "users" : {
          "value" : 776440
        }
      }, {
        "key" : "green",
        "doc_count" : 1104816,
        "users" : {
          "value" : 758189
        }
      } ]
    }
  }
}