CODEWITHSUNDEEP
elasticsearch
Manonandan S K
Manonandan S K

Reputation: 652

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

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.

Upvotes: 0

Views: 849

Answers (1)

jaspreet chahal
jaspreet chahal

Reputation: 9099

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.

Upvotes: 1

Related Questions