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:
{
"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
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
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:
