Hi,
Could you please help me to solve this problem. Really appreciate your help.
I want to multiply and sum of two properties (or fields). One is from main object and another one is from nested object. History object can have many other properties with same requirement. (Removed some other properties to simplify)
Index definition
{
"mappings": {
"properties": {
"description": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
},
"normalize": {
"type": "keyword",
"normalizer": "testnormalizer"
}
}
},
"id": {
"type": "long"
},
"market": {
"type": "nested",
"properties": {
"cost": {
"type": "float"
},
"description": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
},
"normalize": {
"type": "keyword",
"normalizer": "testnormalizer"
}
}
},
"history": {
"type": "nested",
"properties": {
"historydate": {
"type": "date"
},
"quantity": {
"type": "float"
}
}
},
"id": {
"type": "long"
},
"marketid": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
},
"normalize": {
"type": "keyword",
"normalizer": "testnormalizer"
}
}
},
"salesprice": {
"type": "float"
}
}
},
"productid": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
},
"normalize": {
"type": "keyword",
"normalizer": "testnormalizer"
}
}
}
}
},
"settings": {
"index": {
"number_of_shards": "1",
"analysis": {
"normalizer": {
"testnormalizer": {
"filter": [
"lowercase"
],
"type": "custom"
}
}
},
"number_of_replicas": "1"
}
}
}
Example data
{
"id": 1,
"productid": "1",
"description": "product 1",
"market": [
{
"id": 1,
"marketid": "1",
"description": "market 1",
"salesprice": 10.9,
"cost": 7.95957,
"history": [
{
"historydate": "2020-01-01",
"quantity": 1
},
{
"historydate": "2020-02-01",
"quantity": 2
}
]
},
{
"id": 2,
"marketid": "2",
"description": "market 2",
"salesprice": 12,
"cost": 12,
"history": [
{
"historydate": "2020-01-01",
"quantity": 1
},
{
"historydate": "2020-02-01",
"quantity": 2
}
]
},
{
"id": 3,
"marketid": "3",
"description": "market3",
"salesprice": 15,
"cost": 15,
"history": [
{
"historydate": "2020-01-01",
"quantity": 10
},
{
"historydate": "2020-02-01",
"quantity": 20
}
]
}
]
}
I have used sum using field and script. It works to sum all the quantities.
But I want to multiply quantity with cost or sales price and then sum.
I am guessing this cost is not in the nested object of history?
If so how can I mulitply the history quantity with cost or sales price please?
And also i have some requirement to multiply with some other unit of measurements or conversions etc.
should I store in history as another property? data can become large right?
But if i want multiply by some conversion factor which might change later then that means i have to reindex.(data is in millions)
Not Working Query to sum quantity using field in sum
{
"_source": false,
"query": {
"match_all": {}
},
"aggs": {
"quantity_per_month": {
"aggs": {
"quantity_per_month": {
"date_histogram": {
"field": "market.history.historydate",
"calendar_interval": "month"
},
"aggs": {
"quantity": {
"sum": {
"script": "doc['market.history.quantity'].value*doc['market.cost'].value"
}
}
}
},
"forecast_per_month_quantity": {
"sum_bucket": {
"buckets_path": "quantity_per_month>quantity"
}
}
},
"nested": {
"path": "market.history"
}
}
}
}
Working Query to sum quantity using field in sum
{
"_source": false,
"query": {
"match_all": {}
},
"aggs": {
"quantity_per_month": {
"aggs": {
"quantity_per_month": {
"date_histogram": {
"field": "market.history.historydate",
"calendar_interval": "month"
},
"aggs": {
"quantity": {
"sum": {
"field": "market.history.quantity"
}
}
}
},
"forecast_per_month_quantity": {
"sum_bucket": {
"buckets_path": "quantity_per_month>quantity"
}
}
},
"nested": {
"path": "market.history"
}
}
}
}
Working Query to sum quantity using field in sum
{
"_source": false,
"query": {
"match_all": {}
},
"aggs": {
"quantity_per_month": {
"aggs": {
"quantity_per_month": {
"date_histogram": {
"field": "market.history.historydate",
"calendar_interval": "month"
},
"aggs": {
"quantity": {
"sum": {
"script": "doc['market.history.quantity'].value"
}
}
}
},
"forecast_per_month_quantity": {
"sum_bucket": {
"buckets_path": "quantity_per_month>quantity"
}
}
},
"nested": {
"path": "market.history"
}
}
}
}
Thanks,
Krishna