How can I write this complex query in elasticsearch

Question

How can I get the following complex query written in elasticsearch

select username, count(id), 
  concat(YEAR(postedtime),'-',MONTH(postedtime),'-',DAY(postedtime),' ',HOUR(postedtime)) 
from table 
where username in ("user1", "user2", "user3") 
group by username, 
  concat(YEAR(postedtime),'-',MONTH(postedtime),'-',DAY(postedtime),' ',HOUR(postedtime));

Olly Cruickshank · Accepted Answer · 2015-04-13 13:15:36Z

Below is a query which should get you started - some notes:

Counts are returned automatically and don't need to be explicitly requested.
The "size":0 prevents the documents being returned - you'll only see the aggregation.
The dates returned will be in epoch format.
You could replace the query_string with a terms query or even use a filter instead - it will depend on your requirements.
there are 2 levels of aggregation, the first by username the 2nd by date buckets.

the code:

curl -XGET 'http://localhost:9200/myindex/table/_search?pretty' -d '{
 "size": 0,
 "query":{
    "query_string": { "query":"user1 OR user2 OR user3", "fields": ["username"]}
   },
 "aggs" : {
    "username_agg" : {
      "terms": {"field" : "username"},
      "aggs" : {
          "date_agg": { 
            "date_histogram" : { "field" : "postedtime", "interval" : "hour" } 
          }
       }
    }
  }
}'

Collectives™ on Stack Overflow

How can I write this complex query in elasticsearch

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related