# Search across multiple indices for the same field
**URL:** https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349
**Category:** Elasticsearch
**Created:** [November 4, 2019, 7:56am UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349 "2019-11-04T07:56:44Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@Ashish1](https://discuss.elastic.co/u/Ashish1)
#### Post date: [November 4, 2019, 7:56am UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349/1 "2019-11-04T07:56:44Z")
Hello all,
I am working on an event based dataset where let's say there is a central customer index and multiple indexes for different events types, say index event\_1, event\_2, event\_3.
I want to search across all the event\_\* indices and find out the customers common to each of them.
I tried a query with a bool should clause and terms aggregations, however I feel that the aggregations would be the limiting factor here.
Is there any other way by which we can query the event indexes ?
Eg:
**Customer index:**
{
"id":1
"Name": XYZ
}
**Event\_1:**
{
"event1\_val": ABC,
"id": 1
},
{
"event1\_val": PQR,
"id":2
}
**Event\_2:**
{
"event2\_val": ,
"id": 1
},
{
"event2\_val": PQR,
"id":2
}
Let's say i want to get all the customer ids which have event\_1 value as ABC and event\_2 value as PQR.
Currently i am creating it by this query:
```
{
"size":0,
"query":{
"bool":{
"should": [
{
"term": {
"event1_val": {
"value": "ABC"
}
}
},
{
"term": {
"event2_val": {
"value": "PQR"
}
}
}
]
}
}
, "aggs": {
"customers": {
"terms": {
"field": "id",
"min_doc_count": 2
}
}
}
}
```
Any other way by which I can find common ids among the indices ?
---
### Author:  [@spinscale](https://discuss.elastic.co/u/spinscale)
#### Post date: [November 4, 2019, 10:19am UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349/2 "2019-11-04T10:19:51Z")
Have you thought about modeling your data differently, so that you can answer such a question with a single query over a single index?
How about including all the customer data in each event so that all of your data is available in every event? It seems that could possibly simplify your queries a lot!
--Alex
---
### Author:  [@Ashish1](https://discuss.elastic.co/u/Ashish1)
#### Post date: [November 4, 2019, 10:22am UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349/3 "2019-11-04T10:22:52Z")
Thanks Alex for the suggestion.
The customer data is a huge JSON and hence would be really expensive to duplicate them for each of the events. There are more than 2000 events per day appending customer data to each of them would create a huge dataset!
Any other modelling/querying technique would be really appreciated! thanks!
---
### Author:  [@spinscale](https://discuss.elastic.co/u/spinscale)
#### Post date: [November 4, 2019, 1:43pm UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349/4 "2019-11-04T13:43:11Z")
Hey,
you may want to check out the [join datatype](https://www.elastic.co/guide/en/elasticsearch/reference/7.4/parent-join.html) then.
--Alex
---
### Author:  [@DmitriV](https://discuss.elastic.co/u/DmitriV)
#### Post date: [November 4, 2019, 4:31pm UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349/5 "2019-11-04T16:31:34Z")
I have a similar problem where I need to find ids common to two indices. Currently I solve it by querying ES for all ids in each index (using scrolls) and comparing them. For indices containing millions of documents my current implementation takes less than a minute and is reasonable in terms of RAM.
Here's the pseudocode (using map-reduce paradigm):
- Get 10k elements from first index, sorted by the field I'm interested in
- Get first and last field value from those 10k
- Query second index for all documents whose values are between the first and the last (taking care to use the same sort in the query as well).
- Compare both batches. This is done in separate threads to take advantage of multiprocessing.
- In the main thread continue from step 1 by scrolling to the next batch of 10k.
---
### Author:  [@Ashish1](https://discuss.elastic.co/u/Ashish1)
#### Post date: [November 4, 2019, 6:35pm UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349/6 "2019-11-04T18:35:16Z")
Thanks! I had that in mind but seemed expensive computationally. Will try it to see the actual performance.
---
### Author:  [@Ashish1](https://discuss.elastic.co/u/Ashish1)
#### Post date: [November 4, 2019, 6:40pm UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349/7 "2019-11-04T18:40:21Z")
Thanks DmitriV for sharing your experience with this problem.
Interesting approach.
Are you implementing this approach in Apache Spark or other such Big data processing engines ?
If yes, how are you partitioning the data ? Are you keeping a fixed size of 10k documents per partition ?
---
### Author:  [@DmitriV](https://discuss.elastic.co/u/DmitriV)
#### Post date: [November 5, 2019, 8:22am UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349/8 "2019-11-05T08:22:44Z")
Currently it's just raw Python using scroll queries. The first index always returns 10k documents due to the size limit of the scroll request. The other index returns a variable number of documents (currently could be anything between 0 and 300k) depending on the beginning and end positions of the batch.
---
### Author:  [@Ashish1](https://discuss.elastic.co/u/Ashish1)
#### Post date: [November 6, 2019, 5:53am UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349/9 "2019-11-06T05:53:58Z")
Okay. Need to try this approach. Thanks for this!
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [December 4, 2019, 5:53am UTC](https://discuss.elastic.co/t/search-across-multiple-indices-for-the-same-field/206349/10 "2019-12-04T05:53:58Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.