How to get values count in different ranges from array in elasticsearch which is separate for each document?

Question

I want to get count of values for different ranges from an array for each document. For example, a document of student contains array "grades" which has different score per subject e.g. Maths - score 71, Science - score 91, etc. So, I want to get ranges per student like Grade A - 2 subject, Grade B - 1 subject.

So, the mapping is something like this:

{
  "student-grades": {
    "mappings": {
      "properties": {
        "grades": {
          "type": "nested",
          "properties": {
            "subject": {
              "type": "text",
              "fields": {
                "keyword": {
                  "type": "keyword",
                  "ignore_above": 256
                }
              }
            },
            "score": {
              "type": "float"
            }
          }
        },...
      }
    }
  }
}

For counting ranges I have created a query something like this:

GET student-grades/_search
{
  "aggs": {
    "nestedAgg": {
      "nested": {
        "path": "grades"
      },
      "aggs": {
        "gradeRanges": {
          "range": {
            "field": "grades.score",
            "ranges": [
              {
                "key": "range1",
                "to": 35.01
              },
              {
                "key": "range2",
                "from": 35.01,
                "to": 50.01
              },
              {
                "key": "range3",
                "from": 50.01,
                "to": 60.01
              },
              {
                "key": "range4",
                "from": 60.01,
                "to": 70.01
              },
              {
                "key": "range5",
                "from": 70.01
              }
            ]
          },
          "aggs": {
            "perDoc": {
              "top_hits": {
                "size": 10
              }
            }
          }
        }
      }
    }
  }
}

But, it is giving a single list of ranges for all documents combined. The number of subjects is not fixed, so I can't set a random size in top_hits. So, how can I get all ranges per document? (I am new to elasticsearch, so I am not aware about its all features.)

Ashish Tanna · Accepted Answer · 2022-12-27 12:37:43Z

1

I used a painless script to solve this problem. Here is the query:

GET grades/_search
{
  "script_fields": {
    "gradeRanges": {
      "script": {
        "source": """
            List gradeList = doc['grades.score'];
            List gradeRanges= new ArrayList();
           
            for(int i=0; i<5; i++) {
              gradeRanges.add(0);
            }
           
            for(int i=0; i<gradeList.length; i++) {
              if(gradeList[i]<=35.0) {
                gradeRanges[0]++;
              } else if(gradeList[i]<=50.0) {
                gradeRanges[1]++;
              } else if(gradeList[i]<=60.0) {
                gradeRanges[2]++;
              } else if(gradeList[i]<=70.0) {
                gradeRanges[3]++;
              } else {
                gradeRanges[4]++;
              }
            }
            return gradeRanges;
            """
        }
      }
    },
    "size": 100
  }

This will give me separate grade ranges for each document, like this:

{
  "took": 38,
  "timed_out": false,
  "_shards": {
    "total": 2,
    "successful": 2,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 12,
      "relation": "eq"
    },
    "max_score": 1,
    "hits": [
      {
        "_index": "grades",
        "_id": "11",
        "_score": 1,
        "fields": {
          "gradeRanges": [
            0,
            2,
            0,
            3,
            1
          ]
        }
      },
      {
        "_index": "grades",
        "_id": "12",
        "_score": 1,
        "fields": {
          "gradeRanges": [
            0,
            1,
            0,
            1,
            1
          ]
        }
      }
    ]
  }
}

edited Dec 27, 2022 at 12:37

answered Dec 27, 2022 at 5:10

Ashish Tanna

6771 gold badge10 silver badges25 bronze badges

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

2 Comments

karthick S Over a year ago

please share the entire query for better understanding of how you implemented.

Ashish Tanna Over a year ago

@karthickS I have added the query along with the output.

Collectives™ on Stack Overflow

How to get values count in different ranges from array in elasticsearch which is separate for each document?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related