I'm trying to filter my result by a second index.
Elasticsearch version 6.2.2
Can I filter my aggregations by "joining" a second index named product? Something like:
{
"query": {
"bool": {
"must": [
{
"term": {
"sales.variant_id": "xxx"
}
},
{
"term": {
**"product.color": "red"**
}
}
],
"filter": {
"range": {
"sales.date": {
"gte": "2020-04-03 00:00:00",
"lte": "2020-04-04 19:33:20",
"format": "yyyy-MM-dd HH:mm:ss"
}
}
}
}
}
}
Currently I have a index named "sales" with the following type:
{
"_index": "sales",
"_type": "sale",
"_id": "xxx",
"_score": 1,
"_source": {
"quantity": 5,
"price": 4,
"date": "2020-12-31 17:28:45",
"shop": "xxx",
"sku": "xxx",
"variant_id": xxx,
"returned": true
},
"fields": {
"date": [
"2020-12-31T17:28:45.000Z"
]
}
}
I'm querying my sales with the following query to aggregate them:
POST sales/sale/_search?size=0
{
"query": {
"bool": {
"must": {
"term": {
"variant_id": „xxx“
}
},
"filter": {
"range": {
"date": {
"gte": "2020-04-03 00:00:00",
"lte": "2020-04-04 19:33:20",
"format": "yyyy-MM-dd HH:mm:ss"
}
}
}
}
},
"aggs": {
"sales": {
"filters": {
"filters": {
"all": {
"match_all": {}
}
}
},
"aggs": {
"by_shop": {
"terms": {
"field": "shop"
},
"aggs": {
"quantity_shop": {
"sum": {
"field": "quantity"
}
}
}
},
"by_sku": {
"terms": {
"field": "sku"
},
"aggs": {
"by_shops": {
"terms": {
"field": "shop"
},
"aggs": {
"quantity": {
"sum": {
"field": "quantity"
}
}
}
},
"quantity_sku": {
"sum": {
"field": "quantity"
}
},
"return_sku": {
"sum": {
"field": "returned"
}
},
"sales_value_sku": {
"sum": {
"script": {
"source": "doc.quantity.value * doc.price.value"
}
}
},
"return_rate": {
"bucket_script": {
"buckets_path": {
"sales": "quantity_sku",
"returns": "return_sku"
},
"script": "params.returns * 100 / params.sales"
}
}
}
},
"return_variant": {
"sum": {
"field": "returned"
}
},
"quantity_variant": {
"sum": {
"field": "quantity"
}
},
"sales_value_variant": {
"sum": {
"script": {
"source": "doc.quantity.value * doc.price.value"
}
}
},
"return_rate_variant": {
"bucket_script": {
"buckets_path": {
"salesVariant": "quantity_variant",
"returnsVariant": "return_variant"
},
"script": "params.returnsVariant * 100 / params.salesVariant"
}
},
"sales_bucket_filter": {
"bucket_selector": {
"buckets_path": {
"totalSales": "quantity_variant"
},
"script": "params.totalSales > 1"
}
}
}
}
}
}