Multiple aggregation in single query on Elasticsearch

Question

I have log data in the Elasticsearch index.

`"hits": [
  {
    "_index": "event_log",
    "_type": "log_type",
    "_id": "2-d-kmoBazYRVz7KCQIj",
    "_score": 1,
    "_source": {
      "user_id": 123,
      "event": "click",
      "category": "abc",
      "product_id": 1112,
      "bkt": "A"
    }
  },
  {
    "_index": "event_log",
    "_type": "log_type",
    "_id": "3ed-kmoBazYRVz7KCQLX",
    "_score": 1,
    "_source": {
      "user_id": 456,
      "event": "click",
      "category": "abc",
      "product_id": 112,
      "bkt": "A"
    }
  },
  {
    "_index": "event_log",
    "_type": "log_type",
    "_id": "3ud-kmoBazYRVz7KCgIy",
    "_score": 1,
    "_source": {
      "user_id": 1234,
      "event": "click",
      "category": "abc",
      "product_id": 1112,
      "bkt": "B"
    }
  },
  {
    "_index": "event_log",
    "_type": "log_type",
    "_id": "4Od-kmoBazYRVz7KCgLr",
    "_score": 1,
    "_source": {
      "user_id": 4567,
      "event": "click",
      "category": "xyz",
      "product_id": 1118,
      "bkt": "B"
    }
  },
  {
    "_index": "event_log",
    "_type": "log_type",
    "_id": "4ud-kmoBazYRVz7KkwL2",
    "_score": 1,
    "_source": {
      "user_id": 123,
      "event": "cart",
      "category": "xyz",
      "product_id": 1,
      "bkt": "A"
    }
  },
  {
    "_index": "event_log",
    "_type": "log_type",
    "_id": "2ud-kmoBazYRVz7KCALB",
    "_score": 1,
    "_source": {
      "user_id": 123,
      "event": "cart",
      "category": "xyz",
      "product_id": 11,
      "bkt": "A"
    }
  },
  {
    "_index": "event_log",
    "_type": "log_type",
    "_id": "3-d-kmoBazYRVz7KCgKP",
    "_score": 1,
    "_source": {
      "user_id": 4567,
      "event": "click",
      "category": "abc",
      "product_id": 111,
      "bkt": "B"
    }
  },
  {
    "_index": "event_log",
    "_type": "log_type",
    "_id": "3Od-kmoBazYRVz7KCQJ8",
    "_score": 1,
    "_source": {
      "user_id": 456,
      "event": "click",
      "category": "abc",
      "product_id": 111,
      "bkt": "A"
    }
  },
  {
    "_index": "event_log",
    "_type": "log_type",
    "_id": "4ed-kmoBazYRVz7KCwJH",
    "_score": 1,
    "_source": {
      "user_id": 4567,
      "event": "click",
      "category": "xyz",
      "product_id": 1128,
      "bkt": "B"
    }
  }
]}

I want to get the aggregation by category, bkt, event. As well as I want to aggregate user_id by category, bkt. I have two separate queries for that

Count of record aggregated by category, bkt, event.

GET event_log/_search
{"size" : 0,
    "aggs": {
            "category_id": {
              "terms": { "field": "category.keyword" },
              "aggs": {
                "ab_bucket": {
                "terms": { "field": "bkt.keyword" },
                  "aggs": {
                    "event_type": {
                      "terms": { "field": "event.keyword" }
                   }
                  }
                }
              }
            }
          }
    }

The result is

"aggregations": {
"category_id": {
  "doc_count_error_upper_bound": 0,
  "sum_other_doc_count": 0,
  "buckets": [
    {
      "key": "abc",
      "doc_count": 5,
      "ab_bucket": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "A",
            "doc_count": 3,
            "event_type": {
              "doc_count_error_upper_bound": 0,
              "sum_other_doc_count": 0,
              "buckets": [
                {
                  "key": "click",
                  "doc_count": 3
                }
              ]
            }
          },
          {
            "key": "B",
            "doc_count": 2,
            "event_type": {
              "doc_count_error_upper_bound": 0,
              "sum_other_doc_count": 0,
              "buckets": [
                {
                  "key": "click",
                  "doc_count": 2
                }
              ]
            }
          }
        ]
      }
    },

Users aggregated by category, bkt.

GET event_log/_search
{"size" : 0,
"aggs": {
    "category_id": {
      "terms": { "field": "category.keyword" },
      "aggs": {
        "ab_bucket": {
        "terms": { "field": "bkt.keyword" },
          "aggs": {
            "total_uniq_users" : {
              "cardinality": {
                  "field" : "user_id"
              }
            }
          }
        }
      }
    }
  }
}

The result is

"aggregations": {
    "category_id": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "abc",
          "doc_count": 5,
          "ab_bucket": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": "A",
                "doc_count": 3,
                "total_uniq_users": {
                  "value": 2
                }
              },
              {
                "key": "B",
                "doc_count": 2,
                "total_uniq_users": {
                  "value": 2
                }
              }
            ]
          }
        },

Is there a way to combine both the queries and obtain the expected result as a single result

Val · Accepted Answer · 2019-05-07 14:55:28Z

1

Yes, you can do it like this:

GET event_log/_search
{
  "size": 0,
  "aggs": {
    "category_id": {
      "terms": {
        "field": "category.keyword"
      },
      "aggs": {
        "ab_bucket": {
          "terms": {
            "field": "bkt.keyword"
          },
          "aggs": {
            "total_uniq_users": {
              "cardinality": {
                "field": "user_id"
              }
            },
            "event_type": {
              "terms": {
                "field": "event.keyword"
              }
            }
          }
        }
      }
    }
  }
}

answered May 7, 2019 at 14:55

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple aggregation in single query on Elasticsearch

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related