CODEWITHSUNDEEP
elasticsearchaggregateaggregation
Sebastian
Sebastian

Reputation: 2550

Terms aggregation based on unique key

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?

Upvotes: 1

Views: 765

Answers (2)

Sebastian
Sebastian

Reputation: 2550

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
        }
      } ]
    }
  }
}

Upvotes: 1

Olly Cruickshank
Olly Cruickshank

Reputation: 6180

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"}
                   }
                }
            }                 
          }
       }
    }
}'

Upvotes: 2

Related Questions