# Fetch Related Data w/o Applying Query inside an Aggregation

**URL:** <https://discuss.elastic.co/t/fetch-related-data-w-o-applying-query-inside-an-aggregation/190101>\
**Category:** Elasticsearch\
**Created:** [July 11, 2019, 7:35pm UTC](https://discuss.elastic.co/t/fetch-related-data-w-o-applying-query-inside-an-aggregation/190101 "2019-07-11T19:35:16Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![elasticpatrick](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/elasticpatrick/32/49939_2.png) [@elasticpatrick](https://discuss.elastic.co/u/elasticpatrick)\
**Post date:** [July 11, 2019, 7:35pm UTC](https://discuss.elastic.co/t/fetch-related-data-w-o-applying-query-inside-an-aggregation/190101/1 "2019-07-11T19:35:17Z")

</div>

Here is what my data looks like:

Store Index

```auto
 {
  "store_id": integer,
  "store_name": string,
  "address": text
}

```

Product Index

```auto
 {
  "product_id": integer,
  "product_name": string,
  "product_sku": string,
  "store_id" : integer,
  "store_name" : string
}

```

I've already tried to bring the store and products but separately. Have used alias for common fields too to get store and product names. There is also a problem that the search query is also applied on SKUs which I do not want.

What I'am expecting is a query which brings the aggregated store as well as the products in single hit. Which may look like below:

Expected Results (Rough Idea)

```auto
 {
  "results": [
    {
      "type": "store",
      "name": "Some chicken store",
      "latest_SKUs": ["45345435435", "5654645645"]
    },
     {
      "type": "product",
      "name": "Spicy Nuggets",
      "latest_SKUs": ["4asda5435435"] //Only single SKU in case of products
    },
     {
      "type": "store",
      "name": "Shoe Store",
      "latest_SKUs": ["46621278136", "613187979"]
    }

    ]
}

```

---

<div class="post-metadata">

**Author:** ![abhigyan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abhigyan/32/47917_2.png) [@abhigyan](https://discuss.elastic.co/u/abhigyan)\
**Post date:** [July 13, 2019, 5:58am UTC](https://discuss.elastic.co/t/fetch-related-data-w-o-applying-query-inside-an-aggregation/190101/2 "2019-07-13T05:58:43Z")

</div>

@elasticpatrick Please specify your product and store index attributes and what have you already tried so that we can get insight in your data to help you with it.

---

<div class="post-metadata">

**Author:** ![elasticpatrick](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/elasticpatrick/32/49939_2.png) [@elasticpatrick](https://discuss.elastic.co/u/elasticpatrick)\
**Post date:** [July 13, 2019, 6:28am UTC](https://discuss.elastic.co/t/fetch-related-data-w-o-applying-query-inside-an-aggregation/190101/3 "2019-07-13T06:28:37Z")

</div>

Here, I have updated my data which look like above

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [July 13, 2019, 9:03am UTC](https://discuss.elastic.co/t/fetch-related-data-w-o-applying-query-inside-an-aggregation/190101/4 "2019-07-13T09:03:12Z")

</div>

What you are looking to do is basically a join between two indices. Elasticsearch does not support joins so you will likely need to denormalize your data to run the query you specified.

---

<div class="post-metadata">

**Author:** ![elasticpatrick](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/elasticpatrick/32/49939_2.png) [@elasticpatrick](https://discuss.elastic.co/u/elasticpatrick)\
**Post date:** [July 13, 2019, 9:21am UTC](https://discuss.elastic.co/t/fetch-related-data-w-o-applying-query-inside-an-aggregation/190101/5 "2019-07-13T09:21:34Z")

</div>

Thanks for the answer. But can you please suggest me the approach of how to do it . As you must already got the idea of how my index must be looked like.

---

<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:** [August 10, 2019, 9:36am UTC](https://discuss.elastic.co/t/fetch-related-data-w-o-applying-query-inside-an-aggregation/190101/7 "2019-08-10T09:36:46Z")

</div>

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