Multiple group by

Elastic Stack Elasticsearch
1

How to write a efficient ES query for the below SQL query?

Select sum(visits) visits
from index
group by ip, port
order by visits desc
limit 10

I am using below ES query, but this will order the visit inside port bucket. I wan't to get top visits after grouping them by destination ip and port.

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

Can some one help me with this?

Thanks.

2

I don't believe you can currently do this in a single query, you will have to do two and then join them externally.

Pipeline aggs in 2.0 should do this though.

3

Couldn't you use a script in your agg to combine ip and port into a single token for grouping purposes?

4

I was able to do this using script. Below is the solution

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

As I see you need to nest the sum aggregation in the scope of the port aggregation, then you will get the sum (visits) inside the bucket of ip and port together.