How to get sum of diferent fields / array values in elasticsearch?

Question

Using Elasticsearch 7.9.0
My document looks like this

{
  "student": {
    "marks": [
      {
        "sub": 80
      },
      {
        "sub": 90
      },
      {
        "sub": 100
      }
    ]
  }
}

I need one more field total_marks in the response of GET API
Something like this

{
  "hits": [
    {
      "_index": "abc",
      "_type": "_doc",
      "_id": "blabla",
      "_score": null,
      "_source": {
        "student": {
          "marks": [
            {
              "sub": 80
            },
            {
              "sub": 90
            },
            {
              "sub": 100
            }
          ]
        }
      },
      "total_marks": 270
    }
  ]
}

I tried using script_fields My query is

GET sample/_search
{
  "query": {
    "match_all": {}
  },
  "script_fields": {
    "total_marks": {
      "script": {
         "source": """double sum = 0.0;
                    for( item in params._source.student.marks)
                    { sum = sum + item.sub }
                    return sum;"""
      }
    }
  }
}

I got response as

{
  "hits": [
    {
      "_index": "abc",
      "_type": "_doc",
      "_id": "blabla",
      "_score": null,
      "_source": {
        "student": {
          "marks": [
            {
              "sub": 80
            },
            {
              "sub": 90
            },
            {
              "sub": 100
            }
          ]
        }
      },
      "fields": {
        "total_marks": [
          270
        ]
      }
    }
  ]
}

Is thare any way to get as expected?
Any better/optimal solution would be helps a lot.
Thank you.

Okay, but aggregation is to get aggregated values for all the docs in the hits right? I need total marks for each document. Please correct me if I am wrong. and please suggest how to do using count aggregation. — Manonandan S K
– Manonandan S K, Commented Sep 13, 2021 at 4:33
sorry it should be sum aggregation. If you need it on group by basis, you can add terms aggregation — jaspreet chahal
– jaspreet chahal, Commented Sep 13, 2021 at 4:54
is there any unique field in each document? like student id etc. based on which a group by can be done — jaspreet chahal
– jaspreet chahal, Commented Sep 13, 2021 at 4:57

Manonandan S K · Accepted Answer · 2021-09-13 13:29:57Z

1

Terms aggregation and sum aggregation can be used to find total marks per group

{
  "aggs": {
    "students": {
      "terms": {
        "field": "student.id.keyword",
        "size": 10
      },
      "aggs": {
        "total_marks": {
          "sum": {
            "field": "student.marks.sub"
          }
        }
      }
    }
  }
}

Result

"aggregations" : {
    "students" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "1",
          "doc_count" : 1,
          "total_marks" : {
            "value" : 270.0
          }
        }
      ]
    }
  }

This will be faster than script but Pagination will be easier in query as compared to aggregation. So choose accordingly.

Best option may be to have it calculated at index time. If those fields are not changing frequently.

edited Sep 13, 2021 at 13:29

Manonandan S K

6621 gold badge8 silver badges19 bronze badges

answered Sep 13, 2021 at 8:53

jaspreet chahal

9,1492 gold badges14 silver badges30 bronze badges

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

1 Comment

Manonandan S K Over a year ago

Yes pagination will be dificult if we do aggrigations. If we do sort on some fields then aggrigations will not be sorted in that order. Now I am doing calculations at index time. I guess It will reduce the redundant calculations(and cpu usage) as the marks will not be changed later.

Collectives™ on Stack Overflow

How to get sum of diferent fields / array values in elasticsearch?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related