# Implement range filter on usdValue which will be calcuated based on conditions

**URL:** <https://forum.opensearch.org/t/implement-range-filter-on-usdvalue-which-will-be-calcuated-based-on-conditions/23175>\
**Category:** OpenSearch\
**Tags:** discuss, feature-request\
**Created:** [January 22, 2025, 7:24am UTC](https://forum.opensearch.org/t/implement-range-filter-on-usdvalue-which-will-be-calcuated-based-on-conditions/23175 "2025-01-22T07:24:08Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![gsunil](https://avatars.discourse-cdn.com/v4/letter/g/bc79bd/32.png) [@gsunil](https://forum.opensearch.org/u/gsunil)\
**Post date:** [January 22, 2025, 7:24am UTC](https://forum.opensearch.org/t/implement-range-filter-on-usdvalue-which-will-be-calcuated-based-on-conditions/23175/1 "2025-01-22T07:24:08Z")

</div>

**Versions** (relevant - OpenSearch/Dashboard/Server OS/Browser):  
OpenSearch Version - 7.10.2

**Describe the issue** :  
I wanted to implement range filter for usdValue. It is not going inside if condition tried using of doc and params.\_source still facing the same issue. Although when we implement similar sort condition this was working with params.\_source

Really stuck in this for quite long time. Any solution really helpful

**Configuration** :

```auto
{
  "transactions-managers-testing_entities_20250116144143173": {
    "mappings": {
      "properties": {
        "assetsUnderManagement": {
          "type": "nested",
          "properties": {
            "investmentAmount": {
              "type": "nested",
              "properties": {
                "currency": {
                  "properties": {
                    "currencyCode": {
                      "type": "keyword",
                      "index": false,
                      "store": true
                    },
                    "id": {
                      "type": "keyword",
                      "store": true
                    }
                  }
                },
                "usdValue": {
                  "type": "long",
                  "store": true
                },
                "value": {
                  "type": "long",
                  "store": true
                }
              }
            },
            "investmentDate": {
              "type": "date",
              "store": true,
              "format": "date_optional_time"
            },
            "lastModifiedDate": {
              "type": "date",
              "store": true,
              "format": "date_optional_time"
            }
          }
        },
        "id": {
          "type": "keyword",
          "store": true
        },
        "investmentAmount": {
          "type": "nested",
          "properties": {
            "currency": {
              "properties": {
                "currencyCode": {
                  "type": "keyword",
                  "index": false,
                  "store": true
                },
                "id": {
                  "type": "keyword",
                  "store": true
                }
              }
            },
            "usdValue": {
              "type": "long",
              "store": true
            },
            "value": {
              "type": "long",
              "store": true
            }
          }
        },
        "namedGraph": {
          "type": "keyword",
          "store": true
        },
        "pereAssetsUnderManagement": {
          "type": "nested",
          "properties": {
            "investmentAmount": {
              "type": "nested",
              "properties": {
                "currency": {
                  "properties": {
                    "currencyCode": {
                      "type": "keyword",
                      "index": false,
                      "store": true
                    },
                    "id": {
                      "type": "keyword",
                      "store": true
                    }
                  }
                },
                "usdValue": {
                  "type": "long",
                  "store": true
                },
                "value": {
                  "type": "long",
                  "store": true
                }
              }
            },
            "investmentAssetClassId": {
              "type": "keyword",
              "store": true
            },
            "investmentDate": {
              "type": "date",
              "store": true,
              "format": "date_optional_time"
            },
            "lastModifiedDate": {
              "type": "date",
              "store": true,
              "format": "date_optional_time"
            }
          }
        }
      }
    }
  }
}

```

The below is sorting query which is working fine

```auto
GET transactions-managers-testing_entities_20250116144143173/_search
{
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "_script": {
        "order": "desc",
        "script": {
          "lang": "painless",
          "source": """
                  long getLatestUsdValue(List investments) {
                      if (investments == null || investments.isEmpty()) {
                          return 100;
                      }
                  
                      investments.sort((a, b) -> b["lastModifiedDate"].compareTo(a["lastModifiedDate"]));
                  
                      List amounts = investments[0]["investmentAmount"];
                      if (amounts != null && !amounts.isEmpty()) {
                          return amounts[0]["usdValue"];
                      }
                  
                      return 10; 
                  }
                  
                  long usdValue = 0;
                  
                  // Accessing nested fields using params._source
                  if (params._source.containsKey("pereAssetsUnderManagement")) {
                      List pereAssets = params._source.pereAssetsUnderManagement;
                      if (pereAssets != null && !pereAssets.isEmpty()) {
                          usdValue = getLatestUsdValue(pereAssets);
                      }
                  }
                  else if (params._source.containsKey("assetsUnderManagement")) {
                      List assets = params._source.assetsUnderManagement;
                      if (assets != null && !assets.isEmpty()) {
                          usdValue = getLatestUsdValue(assets);
                      }
                  }
                  else {
                      usdValue = 100;
                  }
                  
                  return usdValue;
                  """
        },
        "type": "number"
      }
    }
  ],
  "_source": "id"
}

```

The below is range query which is not working as expected

```auto
GET transactions-managers-testing_entities_20250116144143173/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "script": {
            "script": {
              "lang": "painless",
              "source": """
                  long getLatestUsdValue(List investments) {
                      if (investments == null || investments.isEmpty()) {
                          return 100;
                      }
                  
                      investments.sort((a, b) -> b["lastModifiedDate"].compareTo(a["lastModifiedDate"]));
                  
                      List amounts = investments[0]["investmentAmount"];
                      if (amounts != null && !amounts.isEmpty()) {
                          return amounts[0]["usdValue"];
                      }
                  
                      return 10; 
                  }
                  
                  long usdValue = 100;

                  if (params._source != null) {
                      if (params._source.containsKey("pereAssetsUnderManagement")) {
                          List pereAssets = params._source.pereAssetsUnderManagement;
                          if (pereAssets != null && !pereAssets.isEmpty()) {
                              usdValue = getLatestUsdValue(pereAssets);
                          }
                      } else if (params._source.containsKey("assetsUnderManagement")) {
                          List assets = params._source.assetsUnderManagement;
                          if (assets != null && !assets.isEmpty()) {
                              usdValue = getLatestUsdValue(assets);
                          }
                      } else {
                          usdValue = 100;
                      }
                  } else {
                      usdValue = 1; // Default value when _source is null
                  }

                  return usdValue >= params.minUsdValue && usdValue <= params.maxUsdValue;
              """,
              "params": {
                "minUsdValue": 50,
                "maxUsdValue": 20000000000
              }
            }
          }
        }
      ]
    }
  },
  "_source": "id"
}

```

**Relevant Logs or Screenshots** :

 ![image](https://us1.discourse-cdn.com/flex019/uploads/mauve_hedgehog/original/2X/9/93ed836bf3eaef1f54abe73acf338f2624291c58.png)