I want to get count of grade for student index in elastic search as :

for :

grade 1: count = 4,

grade 2: count = 1

grade 3: count = 1

grade 4: count = 1

Used the below query:

{
    "aggs":
    {
        "grade":
        {
            "terms":
            {
                "field": "marks.grade"
            }
        }
    }
}

Output:

{
    "took": 9,
    "timed_out": false,
    "_shards":
    {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits":
    {
        "total":
        {
            "value": 2,
            "relation": "eq"
        },
        "max_score": 1.0,
        "hits":
        [
            {
                "_index": "student",
                "_type": "doc",
                "_id": "001",
                "_score": 1.0,
                "_source":
                {
                    "name": "abc",
                    "marks":
                    [
                        {
                            "grade": 1,
                            "score": 95
                        },
                        {
                            "grade": 2,
                            "score": 75
                        },
                        {
                            "grade": 2,
                            "score": 72
                        },
                        {
                            "grade": 3,
                            "score": 55
                        }
                    ]
                }
            },
            {
                "_index": "student",
                "_type": "doc",
                "_id": "002",
                "_score": 1.0,
                "_source":
                {
                    "name": "xyz",
                    "marks":
                    [
                        {
                            "grade": 4,
                            "score": 35
                        },
                        {
                            "grade": 2,
                            "score": 79
                        },
                        {
                            "grade": 2,
                            "score": 65
                        }
                    ]
                }
            }
        ]
    },
    "aggregations":
    {
        "grade":
        {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets":
            [
                {
                    "key": 2,
                    "doc_count": 2
                },
                {
                    "key": 1,
                    "doc_count": 1
                },
                {
                    "key": 3,
                    "doc_count": 1
                },
                {
                    "key": 4,
                    "doc_count": 1
                }
            ]
        }
    }
}

Here, it counts grade 2 count as 2 only instead of 4.

Is there any query to get the output grade 2 count as 4?

CodePudding user response：

You can use a combination of terms aggregation with cardinality aggregation

{
    "size":0,
    "aggs": {
        "grade": {
            "terms": {
                "field": "marks.grade"
            },
            "aggs": {
                "count_of_grade": {
                    "cardinality": {
                        "field": "marks.grade"
                    }
                }
            }
        }
    }
}

Response will be

 "aggregations": {
        "grade": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
                {
                    "key": 2,
                    "doc_count": 2,
                    "count_of_grade": {
                        "value": 4
                    }
                },
                {
                    "key": 1,
                    "doc_count": 1,
                    "count_of_grade": {
                        "value": 3
                    }
                },
                {
                    "key": 3,
                    "doc_count": 1,
                    "count_of_grade": {
                        "value": 3
                    }
                },
                {
                    "key": 4,
                    "doc_count": 1,
                    "count_of_grade": {
                        "value": 2
                    }
                }
            ]
        }
    }

CodePudding user response：

You can do this by making marks array nested. Here is an example mapping:

{
  "mappings": {
    "properties": {
      "marks": { "type": "nested" }
    }
  }
}

And you can make a nested aggregation:

{
  "size": 0,
  "aggs": {
    "counts": {
      "nested": {
        "path": "marks"
      },
      "aggs": {
        "counts": {
          "terms": {
            "field": "marks.grade",
            "size": 10
          }
        }
      }
    }
  }
}

This is the result of the aggregation:

{
  "key" : 2,
  "doc_count" : 4
},
{
  "key" : 1,
  "doc_count" : 1
},
{
  "key" : 3,
  "doc_count" : 1
},
{
  "key" : 4,
  "doc_count" : 1
}