Multiple group by in Elasticsearch -

how write efficient es query below sql query?

select sum(visits) visits index group ip, port order visits desc limit 10 

i using below es query, order visit inside port bucket. wan't top visits after grouping them destination ip , port.

"aggregations" : {     "ip":      {         "terms":          {             "field": "ip",             "size": 10         },         "aggregations":          {             "port":              {                 "terms":                  {                     "field": "port",                     "size": 0,                     "order":                      {                         "visits": "desc"                     }                 },                 "aggregations":                  {                     "visits":                      {                         "sum":                          {                             "field": "visits"                         }                     }                 }             }         }     } } 

can 1 me this?

thanks.

data in elasticsearch

 ip            port           visits      1.1.1.1        80             10     1.1.1.2        80             10      1.1.1.1        80             20     1.1.1.3        20             100      1.1.1.1        57             20     1.1.1.1        57             200 

es response

    1.1.1.1                      57             200              80             50     1.1.1.2                     80             10      1.1.1.3                      20             100   

expected

1.1.1.1                  57             200 1.1.1.3                  20             100      1.1.1.1                         80             50 1.1.1.2                 80             10  

what es is, first group ip , port, @ last sum visit , arrange visit desc inside port aggregator. not give top 10 visits rather give top 10 inside each port

solution:

use script merge 2 fields , apply group by

 {   "size": 0,   "aggs": {     "destination": {       "terms": {         "script": "doc['ip'].value +  ':'  + doc['port'].value", "order": {           "visits": "desc"         }       },                 "aggregations":                  {                     "visits":                      {                         "sum":                          {                             "field": "visits"                         }                      }                 }     }   } } 

hope helpful.