# How to join a index to filter aggregations

**URL:** <https://discuss.elastic.co/t/how-to-join-a-index-to-filter-aggregations/226250>\
**Category:** Elasticsearch\
**Created:** [April 2, 2020, 4:30pm UTC](https://discuss.elastic.co/t/how-to-join-a-index-to-filter-aggregations/226250 "2020-04-02T16:30:36Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![maxheyer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/maxheyer/32/65573_2.png) [@maxheyer](https://discuss.elastic.co/u/maxheyer)\
**Post date:** [April 2, 2020, 4:30pm UTC](https://discuss.elastic.co/t/how-to-join-a-index-to-filter-aggregations/226250/1 "2020-04-02T16:30:36Z")

</div>

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:

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

```auto
    {
      "_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:

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

```

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [April 3, 2020, 2:12pm UTC](https://discuss.elastic.co/t/how-to-join-a-index-to-filter-aggregations/226250/2 "2020-04-03T14:12:47Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![maxheyer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/maxheyer/32/65573_2.png) [@maxheyer](https://discuss.elastic.co/u/maxheyer)\
**Post date:** [April 3, 2020, 5:41pm UTC](https://discuss.elastic.co/t/how-to-join-a-index-to-filter-aggregations/226250/3 "2020-04-03T17:41:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [April 6, 2020, 1:18pm UTC](https://discuss.elastic.co/t/how-to-join-a-index-to-filter-aggregations/226250/4 "2020-04-06T13:18:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![maxheyer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/maxheyer/32/65573_2.png) [@maxheyer](https://discuss.elastic.co/u/maxheyer)\
**Post date:** [April 6, 2020, 1:52pm UTC](https://discuss.elastic.co/t/how-to-join-a-index-to-filter-aggregations/226250/5 "2020-04-06T13:52:25Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 4, 2020, 1:52pm UTC](https://discuss.elastic.co/t/how-to-join-a-index-to-filter-aggregations/226250/6 "2020-05-04T13:52:32Z")

</div>

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