ElasticSearch aggregation - filter and group by

Question

I'm new to elasticsearch, and trying to execute a query which do something similar to filter and group by.

I was able to filter (by using filter) and executing a group by query by using 'terms', but couldn't build a query that does both.

That's my query without grouping

{
  "size": 0,
  "aggs": {
    "group_by_city": {
        "filter": {
            "bool": {
              "must": [
                {
                  "term": {
                    "account": "a"
                  }
                },
                {
                  "term": {
                    "appName": "b"
                  }
                },
                {
                  "range": {
                    "timestamp": {
                      "from": 1464713893304,
                      "to": 1465022700000
                    }
                  }
                }
              ]
            }
          },
      "aggs": {
        "average_timing": {
          "avg": {
            "field": "t.timing1"
          }
        }
      }
    }
  }
}

For grouping I've used:

{
  "size": 0,
  "aggs": {
    "group_by_country": {
      "terms": {
        "field": "country"
      },
      "aggs": {
        "average_balance": {
          "avg": {
            "field": "t.timing1"
          }
        }
      }
    }
  }
}

Any ideas how can I combine the two?

Show your query that doesn't work. – AndreKR May 31 '16 at 19:09 — AndreKR, May 31 '16 at 19:09

score 9 · Accepted Answer · answered May 31 '16 at 22:28

We had a similar problem when we had to present some analytics on a data subset from ElasticSearch. I managed to solve this by combining the filter and the aggs. Based on your queries, I could think of something like this:

{
    "size": 0,
    "filter": {
        "bool": {
            "must": [
                {
                    "term": { "account": "a" }
                },
                {
                    "term": { "appName": "b" }
                },
                {
                    "range": {
                        "timestamp": {
                            "from": 1464713893304,
                            "to": 1465022700000
                        }
                    }
                }
            ]
        }
    },
    "aggs": {
        "group_by_country": {
            "terms": { "field": "country" },
            "aggs": {
                "average_balance": {
                    "avg": {
                        "field": "t.timing1"
                    }
                }
            }
        }
    }
}

I hope my understanding of your problem is right and this helps you.

ElasticSearch aggregation - filter and group by

1 Answers1