ElasticSearch script query incorrect values

Elastic Stack Elasticsearch
1

Hello everyone, I'm trying to apply this query

GET rdbms_sync_idx/_search
{
   "query":{
      "nested":{
         "path":"bogoDeals",
         "query":{
            "bool":{
               "filter":[
                  {
                     "range":{
                        "bogoDeals.startAt":{
                           "lte":"now"
                        }
                     }
                  },
                  {
                     "bool":{
                        "should":[
                           {
                              "bool":{
                                 "must_not":{
                                    "exists":{
                                       "field":"bogoDeals.endAt"
                                    }
                                 }
                              }
                           },
                           {
                              "range":{
                                 "bogoDeals.endAt":{
                                    "gt":"now"
                                 }
                              }
                           }
                        ]
                     }
                  },
                  {
                     "nested":{
                        "path":"bogoDeals.bogoDealOptions",
                        "query":{
                           "bool":{
                              "filter":{
                                 "script":{
                                    "script":{
                                       "source":"(doc['bogoDeals.bogoDealOptions.bonusQuantity'].value + doc['bogoDeals.bogoDealOptions.minQuantity'].value) >= doc['quantity'].value",
                                       "lang":"expression"
                                    }
                                 }
                              }
                           }
                        }
                     }
                  }
               ]
            }
         }
      }
   }
}

I'm trying to retrieve the documents where (bogoDeal.bogoDealOption.bonusQuantity + bogoDeal.bogoDealOption.minQuantity) >= quantity, the only problem with the query above is that no matter what I do doc['quantity'] always returns the value of 0.

Here is what my document looks like

{
   "_index":"rdbms_sync_idx",
   "_type":"_doc",
   "_id":"10",
   "_version":5,
   "_seq_no":63549,
   "_primary_term":3,
   "found":true,
   "_source":{
      "modifiedAt":"2021-09-04T19:14:13.000Z",
      "quantity":10,
      "id":10,
      "createdAt":"2021-03-20T20:32:03.033Z",
      "price":2.15,
      "bogoDeals":[
         {
            "sourceProductId":10,
            "targetProductId":10,
            "bogoDealOptions":[
               {
                  "createdAt":"2021-09-04T16:04:41.980Z",
                  "minQuantity":10,
                  "modifiedAt":"2021-09-04T16:04:41.980Z",
                  "id":27,
                  "bonusQuantity":2
               }
            ],
            "createdAt":"2021-08-30T17:40:14.179Z",
            "modifiedAt":"2021-08-30T17:40:14.179Z",
            "startAt":"2021-08-30T17:40:14.000Z",
            "description":"",
            "endAt":"2021-11-30T21:00:00.000Z",
            "id":18
         },
         {
            "sourceProductId":10,
            "targetProductId":10,
            "bogoDealOptions":[
               
            ],
            "createdAt":"2021-08-30T17:40:56.287Z",
            "modifiedAt":"2021-08-30T17:40:56.287Z",
            "startAt":"2021-11-30T21:00:00.000Z",
            "description":"",
            "endAt":"2022-11-30T21:00:00.000Z",
            "id":19
         }
      ]
   }
}

I tried the painless & expression languages, with both I have the issue of not being able to retrieve the value of quantity, I think it might be because I'm doing a script in a nested of nested, is there any solution to this issue? if so please attach code with your comment, really appreciated!

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.