ElasticSearch sum up nested object field

Question

I have a nested field and I want to sum up the values of that nested object using script. I can not use aggregation, as this summation is done for range filtering.

My mappings:

    {
      "_doc" : {,
        "properties" : {
          "searchPrices" : {
            "type" : "nested",
            "properties" : {
              "price" : {
                "type" : "double"
              },
              "type" : {
                "type" : "keyword"
              }
            }
          }
        }
      }
    }

The query I tried:

{
  "size": 100,
  "query": {
    "bool": {
      "must": [
        
        {
          "nested": {
            "path": "searchPrices",
            "query": {
              "bool": {
                "must": [
                  {
                    "script": {
                      "script": """
                        int sum = 0;
                        for (obj in doc['searchPrices.price']) {
                          sum = sum + obj;
                        } 
                        sum >= 200;"""
                    }
                  },
                  {
                    "term": {
                      "searchPrices.type": "recurringPrice"
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

The problem is that this script is only picking the first nested document irrespective of the fact that there are multiple search prices within a document.

Sample doc:

        {
          "id" : "v2",
          "searchPrices" : [
            {
              "price" : 2000,
              "type" : "recurringPrice",
            },
            {
              "price" : 200,
              "type" : "recurringPrice",
              "conditions" : [
                "addon1"
              ]
            },
            {
              "price" : 400,
              "type" : "recurringPrice",
              "conditions" : [
                "addon2"
              ]
            }
          ]
        }

So, instead of considering 2600 as the price, it is only considering 2000 as the price.

occidere · Accepted Answer · 2020-10-01 12:31:14Z

1

Unfortunately, It seems not be possible by painless script.

According to Help for painless iterate nested fields document answered by Elastic Team Member, using painless script at query time only can access one nested object at a time.

So, The situation that you suffered - only considering 2000 as the price - was natural.

answered Oct 1, 2020 at 12:31

occidere

1881 gold badge6 silver badges21 bronze badges

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

1 Comment

rohanagarwal Over a year ago

Yeah, I realized it was natural after reading more. But could not find a workaround.

Collectives™ on Stack Overflow

ElasticSearch sum up nested object field

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