Get sum of record count for inner bucket key in two level term aggregation

I have records with two keyword type field

  • user_id: String that identifies individual user,
  • result: String such as "success", "failure" or "pending" etc.

These are what I want to do:

  1. Get record count for each result.
  2. Exclude user, who do not have at least 5 record.

I've used "terms" aggregation to filtering out user.

GET /apple/_search
{
  "size": 0,
  "aggs": {
    "users": {
      "terms": {
        "field": "user_id.keyword",
        "min_doc_count": 5
      },
      "aggs": {
        "results": {
          "terms": {
            "field": "result.keyword",
            "size": 10
          }
        }
      }
    }
  }
}

And the result is looks like below:

{
  "aggregations": {
    "users": {
      "buckets": [
        {
          "key": "user_8",
          "doc_count": 9,
          "results": {
            "buckets": [
              {
                "key": "success",
                "doc_count": 4
              },
              {
                "key": "failure",
                "doc_count": 3
              },
              {
                "key": "pending",
                "doc_count": 2
              }
            ]
          }
        },
        {
          "key": "user_12",
          "doc_count": 7,
          "results": {
            "buckets": [
              {
                "key": "success",
                "doc_count": 2
              },
              {
                "key": "failure",
                "doc_count": 2
              },
              {
                "key": "pending",
                "doc_count": 2
              }
            ]
          }
        }
      }
    }
  }
}

Since the result, count of each "result", is calculated per user, I have to sum them.
For example, the overall summation of "success" record is 4+2 = 6. "failure" is 3+2 = 5 and so go on and on.

I think I can add "sum_bucket" aggregation to acheive it, and it works.

    "success-total": {
      "sum_bucket": {
        "buckets_path": "users>results['success']._count"
      }
    },

However the "result" field can have another string, so I can't use static string such as "success" and "pending" for it.
How can I make it to travel all bucket for each users, and get the count of each result, and result it?

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.