0

I need to make an aggregation per category on the following example that I've taken from a similar issue

{
  "category": 1,
  "name": "apple",
  "price": 3,
},
{
  "category": 2,
  "name": "orange",
  "price": 2,
},
{
  "category": 3,
  "name": "tomato",
  "price": 1,
}

I've tried to do an aggregation per category, adding the price for all documents, using this aggregation :

"aggs": {
    "1": {
      "global": {
      },
      "aggs": {
        "2": {
      "terms": {
        "field": category,
        "size": 10000
          },
          "aggs": {
            "3": {
             "sum" : {
             "field" :  "price"
              }
            }
          }
        }
      }
    }
}

I expected something like this (for 3 documents having the same information):

{
  "category": 1,
  "sum": 9,
},
{
  "category": 2,
  "sum": 6,
},
{
  "category": 3,
  "sum": 3,
}

But instead it's adding all the values in all the documents. The value 18 it's (3+2+1) * 3. What is wrong with the query I'm executing?

       "buckets" : [
          {
            "key" : 1,
            "doc_count" : 3,
            "3" : {
              "value" : 18.0
            }
          },
          {
            "key" : 2,
            "doc_count" : 3,
            "3" : {
              "value" : 18.0
            }
          },
          {
            "key" : 3,
            "doc_count" : 3,
            "3" : {
              "value" : 18.0
            }
Rui Gonçalves
  • 11
  • 1
  • 1
    To fully understand, each document contains an array of JSON objects and has all three categories? – Benjamin Trent Oct 26 '21 at 19:37
  • Hi @BenjaminTrent, thank you for your message. I've found the answer here : https://stackoverflow.com/questions/30583679/elasticsearch-terms-and-sum-aggregation – Rui Gonçalves Oct 27 '21 at 13:47
  • Yep! I was going to suggest using `nested` but I wanted to make sure of your question :) – Benjamin Trent Oct 28 '21 at 11:59
  • Thanks :) @BenjaminTrent. If it's not pushing to much I wanted to ask you if you know how to access the buckets from a painless script. I needed to iterate them and update the documents that have the same keys with that aggregated sum. I saw some solutions for js and ruby but not for painless scripts – Rui Gonçalves Oct 28 '21 at 12:11

0 Answers0