CODEWITHSUNDEEP
elasticsearch
epipko
epipko

Reputation: 397

elasticsearch how to find number of occurrences

I wonder if it's possible to convert this sql query into ES query? select top 10 app, cat, count(*) from err group by app, cat

Or in English it would be answering: "Show top app, cat and their counts", so this will be grouping by multiple fields and returning name and count.

Upvotes: 3

Views: 8124

Answers (2)

bittusarkar
bittusarkar

Reputation: 6357

For aggregating on a combination of multiple fields, you have to use scripting in Terms Aggregation like below:

POST <index name>/<type name>/_search?search_type=count
{
  "aggs": {
    "app_cat": {
      "terms": {
        "script" : "doc['app'].value + '#' + doc['cat'].value",
        "size": 10
      }
    }
  }
}

I am using # as a delimiter assuming that it is not present in any value of app and/or cat fields. You can use any other delimiter of your choice. You'll get a response something like below:

{
   "took": 3,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 10,
      "max_score": 0,
      "hits": []
   },
   "aggregations": {
      "app_cat": {
         "buckets": [
            {
               "key": "app2#cat2",
               "doc_count": 4
            },
            {
               "key": "app1#cat1",
               "doc_count": 3
            },
            {
               "key": "app2#cat1",
               "doc_count": 2
            },
            {
               "key": "app1#cat2",
               "doc_count": 1
            }
         ]
      }
   }
}

On the client side, you can get the individual values of app and cat fields from the aggregation response by string manipulations.

In newer versions of Elasticsearch, scripting is disabled by default due to security reasons. If you want to enable scripting, read this.

Upvotes: 3

Vineeth Mohan
Vineeth Mohan

Reputation: 19283

Terms aggregation is what you are looking for.

Upvotes: 1

Related Questions