How to join a index to filter aggregations

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"
              }
            }
          }
        }
      }
    }

One way to solve this, would be to join the product information into your sales index and then query a single index. Would that be feasible in your case?

How can I join another index? Can you give me a link to the documentation?

sorry for being unclear. With joining I meant the process of merging that data before indexing (as part of your ingest mechanism), so that you can query those fields at the same time.

Hi Alex,
your last post was absolutely clear, but I didnt read it correctly. I will try to build a _parent, child relationships within in the same index. Thank you!

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