Get an aggregate count in elasticsearch based on particular uniqueid field

Question

I have created an index and indexed the document in elasticsearch it's working fine but here the challenge is i have to get an aggregate count of category field based on uniqueid i have given my sample documents below.

{
"UserID":"A1001",
"Category":"initiated",
"policyno":"5221"
},
{
"UserID":"A1001",
"Category":"pending",
"policyno":"5222"
},
{
"UserID":"A1001",
"Category":"pending",
"policyno":"5223"
},
{
"UserID":"A1002",
"Category":"completed",
"policyno":"5224"
}



**Sample output for UserID - "A1001"**

initiated-1
pending-2

**Sample output for UserID - "A1002"**
completed-1

How to get the aggregate count from above given Json documents like the sample output mentioned above

Answer 1

I suggest a terms aggregation as shown in the following:

{
"size": 0,
"aggs": {
    "By_ID": {
    "terms": {
        "field": "UserID.keyword"
    },
    "aggs": {
        "By_Category": {
        "terms": {
            "field": "Category.keyword"
        }
        }
    }
    }
}
}

Here is a snippet of the response:

    "hits" : {
    "total" : {
    "value" : 4,
    "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
},
"aggregations" : {
    "By_ID" : {
    "doc_count_error_upper_bound" : 0,
    "sum_other_doc_count" : 0,
    "buckets" : [
        {
        "key" : "A1001",
        "doc_count" : 3,
        "By_Category" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
            {
                "key" : "pending",
                "doc_count" : 2
            },
            {
                "key" : "initiated",
                "doc_count" : 1
            }
            ]
        }
        },
        {
        "key" : "A1002",
        "doc_count" : 1,
        "By_Category" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
            {
                "key" : "completed",
                "doc_count" : 1
            }
            ]
        }
        }
    ]
    }
}