Elastic Search (COUNT*) with group by and where condition

Question

Dear Elastic Serach users,

I am newbie in ElasticSearch.

I am confused for how to convert the following sql command into elasticSearch DSL query ? Can anyone help to assist me.

SELECT ip, count(*) as c  FROM elastic WHERE  date 
BETWEEN '2016-08-20  00:00:00' and '2016-08-22 13:41:09' 
AND service='http' AND destination='10.17.102.1' GROUP BY ip ORDER BY c DESC;

THank YOu

Answer 1

The following query will achieve exactly what you want, i.e. it will select the documents within the desired date range and with the required service and destination and then run a terms aggregation (=group by) on their ip field and order the latter in decreasing count order.

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "date": {
              "gt": "2016-08-22T00:00:00.000Z",
              "lt": "2016-08-22T13:41:09.000Z"
            }
          }
        },
        {
          "term": {
            "service": "http"
          }
        },
        {
          "term": {
            "destination": "10.17.102.1"
          }
        }
      ]
    }
  },
  "aggs": {
    "group_by_ip": {
      "terms": {
        "field": "ip"
      }
    }
  }
}