How to implement SQL 'group by' on multiple fields using aggregations?

Elastic Stack Elasticsearch
1

Hi all,

I'm trying to implement the following kind of SQL using ES aggs:

select id1, id2, count(*)
from mytable
group by id1, id2;

From what I read this can only accomplished using copy_to during indexing. Is it indeed the only way or I missed anything?

Thanks!

2

I don't think copy_to would work as it would still build one bucket per term while you are looking for one bucket per id1, id2 pair if I understand correctly. So you have two options: either an index-time solution and have a dedicated field that stores id1, id2 pairs or a search time solution by using a script that would concatenate those values to build the bucket label. While using scripts would be more flexible, it would also be significantly less efficient.

1 Like
3

It appears that ES supports sub aggregations, which basically is what I need:

{
  "size": 0,
  "aggs": {
    "id1_count": {
      "terms": { "field": "id1" },
      "aggs": {
	"id2_count": {
          "terms": { "field": "id2" }
             }  
           }
        }  
      }
    }
  }
}

Thanks!