CODEWITHSUNDEEP
elasticsearchaggregation
sodik
sodik

Reputation: 4683

Elasticsearch and aggregation of subqueries

I know that elasticsearch allows sub-aggregations (ie. nested aggregation), however I would like to apply aggregation on the result of "first" aggregation (or in generic any query - aggregation or not).

Concrete example: I log events about user actions (for simplicity I have documents with user_id and action). I can make a query that counts number of actions executed by each user. However I would like to find out percentage (or count) of "active users" (e.g. users that have executed more than 10 actions). Ideal result would be a histogram over all users showing how active the users are.

Is there a way how to create such query? Or is there any other approach I can take other than store aggregated results of subquery and compute the histogram out of that?

Note: I have seen Elastic Search and "sub queries" question, but it was about something else and it is over one and half year old and elasticsearch is being actively developed.

Additionally it seems that in version 1.4 there will be available scripted metric aggregation, but anyway that would require to store counter for every user until reduce phase. And some "approximate solution" is good for me - similar to what ES uses internally for its aggregations.

Upvotes: 4

Views: 1710

Answers (1)

Matteo Melani
Matteo Melani

Reputation: 2726

Here is the query I have used, notice the "min_doc_count" in the aggregation.

{
  "query": {
    "filtered": {
      "filter": {
        "and": [
          { "term" : { "name": "did x" } },
          { "range": { "created_at": { "gte": "now-7d", "lte": "now" } } }
        ]
      }
    }
  },
  "aggregations": {
    "my_agg": {
      "terms": {
        "field": "user_id",
        "min_doc_count": 10,
        "size": 0
      }
    }
  }
}

This query returns the list of buckets (users) with more than 9 events in the specified time period. Just 'count' results to get the number of active users.

I have tested this approach with thousands of events and it works well. At a certain scale you will have to use Hadoop.

Upvotes: 2

Related Questions