# Searching for data over multiple indexes

**URL:** <https://discuss.elastic.co/t/searching-for-data-over-multiple-indexes/237182>\
**Category:** Kibana\
**Created:** [June 15, 2020, 7:43pm UTC](https://discuss.elastic.co/t/searching-for-data-over-multiple-indexes/237182 "2020-06-15T19:43:38Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![alphalol](https://avatars.discourse-cdn.com/v4/letter/a/53a042/32.png) [@alphalol](https://discuss.elastic.co/u/alphalol)\
**Post date:** [June 15, 2020, 7:43pm UTC](https://discuss.elastic.co/t/searching-for-data-over-multiple-indexes/237182/1 "2020-06-15T19:43:38Z")

</div>

Lets say I have two indices: `foo` and `bar`.

- `foo` has an `id` and `type` field.
- `bar` has an `id` and `total` field.

This is obviously a contrived minimal example, so while you may think the data could be refactored into one document, please for the sake of this question assume you can't (unless there is a really good reason not to).

I want to search `bar` for all documents that have a `total` more than `10`. I then want to use the `id` of these documents to get the corresponding documents from `foo` (so I can use the `type`).

For example:

```auto
foo: [
    {id: 1, type: "aaa"},
    {id: 2, type: "bbb"},
    {id: 2, type: "ccc"}
    {id: 3, type: "ccc"}
]

bar: [
    {id: 1, total: 100},
    {id: 2, type: 100},
    {id: 3, type: 5}
]

```

I search for all documents in `bar` with a total greater than `10`, and get `id`s `1` and `2`. I then use these to get the corresponding documents from `foo`: `{id: 1, type: "aaa"}`, `{id: 2, type: "bbb"}`, `{id: 2, type: "ccc"}`.

Is there a way to do this using Kibana/Elasticsearch?

---

<div class="post-metadata">

**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\
**Post date:** [June 15, 2020, 7:51pm UTC](https://discuss.elastic.co/t/searching-for-data-over-multiple-indexes/237182/2 "2020-06-15T19:51:41Z")

</div>

You are asking for an expensive lookup operation: using the results of one query to construct a new query by ID. There are some options you have, but the best option is to change the shape of your data to better fit the index-oriented nature of Elasticsearch. If that is impossible, then it is theoretically possible to construct this kind of lookup using a series of functions in Canvas- but I haven't tested this functionality.

---

<div class="post-metadata">

**Author:** ![alphalol](https://avatars.discourse-cdn.com/v4/letter/a/53a042/32.png) [@alphalol](https://discuss.elastic.co/u/alphalol)\
**Post date:** [June 15, 2020, 7:59pm UTC](https://discuss.elastic.co/t/searching-for-data-over-multiple-indexes/237182/3 "2020-06-15T19:59:21Z")

</div>

> [@wylie](#):
>
> There are some options you have, but the best option is to change the shape of your data to better fit the index-oriented nature of Elasticsearch

Do you have any suggestions to go about doing this?

To give some more info, I have data coming from multiple sources (e.g. it cannot be added at the same time to a single document) and at some point in the future, somebody is going to ask me "please get us a list of X that has Y above Z" (or some other obscure query). And this information is going to be spread across separate indices. In the day-to-day case, the data we need for our visualisation are all within a single index.

Would the best option to be to re-index data from separate indexes into one, if that is possible, then perform the query on that?

> [@wylie](#):
>
> If that is impossible, then it is theoretically possible to construct this kind of lookup using a series of functions in Canvas- but I haven't tested this functionality.

I don't like the sound of that!

---

<div class="post-metadata">

**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\
**Post date:** [June 15, 2020, 8:24pm UTC](https://discuss.elastic.co/t/searching-for-data-over-multiple-indexes/237182/4 "2020-06-15T20:24:12Z")

</div>

You are allowed to combine visualizations or individual documents from multiple indices into a dashboard in Kibana: would that work?

Alternatively, could you change the way you update documents? Updating a single doc by ID is relatively fast.

---

<div class="post-metadata">

**Author:** ![alphalol](https://avatars.discourse-cdn.com/v4/letter/a/53a042/32.png) [@alphalol](https://discuss.elastic.co/u/alphalol)\
**Post date:** [June 15, 2020, 8:39pm UTC](https://discuss.elastic.co/t/searching-for-data-over-multiple-indexes/237182/5 "2020-06-15T20:39:47Z")

</div>

> [@wylie](#):
>
> You are allowed to combine visualizations or individual documents from multiple indices into a dashboard in Kibana: would that work?
> 
> Alternatively, could you change the way you update documents? Updating a single doc by ID is relatively fast.

The problem is when someone asks for something really specific, e.g. in the past month, get me a list of all Y for people who have X higher Z. Multiple visualisations wouldn't give that data - it's usually someone in a specific department who needs a list to do something with.

Putting the data into single documents and using update could potentially work, however it would be quite complicated, because the data isn't actually really related (from very separate sources, but generated by the same user), is updated at different times and as with my example using `foo` and `bar`, there may be multiple documents, or no documents in `foo` for a given `id`, which exists thousand of times already in `bar`, or vice versa.

However (please correct me if I'm wrong), I think I could re-index the data from all the relevant indices (that contain the data I need), somehow merging all the connected documents into one document, on the case-by-case basis that one of these specific queries needs to be done?

---

<div class="post-metadata">

**Author:** ![wylie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/wylie/32/81794_2.png) [@wylie](https://discuss.elastic.co/u/wylie)\
**Post date:** [June 15, 2020, 8:44pm UTC](https://discuss.elastic.co/t/searching-for-data-over-multiple-indexes/237182/6 "2020-06-15T20:44:22Z")

</div>

Yes, I think a slightly more manual approach like the one you're suggesting would work.

---

<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:** [July 13, 2020, 8:44pm UTC](https://discuss.elastic.co/t/searching-for-data-over-multiple-indexes/237182/7 "2020-07-13T20:44:23Z")

</div>

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