# Cross-index join **URL:** \ **Category:** Elasticsearch\ **Created:** [April 21, 2011, 8:53pm UTC](https://discuss.elastic.co/t/cross-index-join/4272 "2011-04-21T20:53:17Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![plaflamme](https://avatars.discourse-cdn.com/v4/letter/p/a88e57/32.png) [@plaflamme](https://discuss.elastic.co/u/plaflamme)\ **Post date:** [April 21, 2011, 8:53pm UTC](https://discuss.elastic.co/t/cross-index-join/4272/1 "2011-04-21T20:53:17Z")
Hi, When querying several indices, is there a way to get "unique" hits? By "unique" I mean based on entity+id equality. Say I have several CSV files that may contain data on common entities but have varying columns and I've indexed them in separate indices in ES (using the same "entity type" in all indices). The result is several indices where some contain the same entity+id document. Can I ask ES to search through all indices and return unique documents (entity+id)? I guess it's similar to an SQL join where you query several tables and specify how you join them (in this case, the join would always happen on "\_id"). I know putting everything in one index would solve my problem, but for the sake of management, it's simpler for me to manage several indices (one per CSV so to speak). I this a valid feature for ES? Is there a way to do this already? Thanks, Philippe ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [April 21, 2011, 10:52pm UTC](https://discuss.elastic.co/t/cross-index-join/4272/2 "2011-04-21T22:52:43Z")
There isn't an option to do that, since shards of different indices exists on different nodes. You can ask for more than you might want, and filter on the client side, or use a single index (as you suggested). On Thursday, April 21, 2011 at 11:53 PM, Philippe Laflamme wrote: > Hi, > > When querying several indices, is there a way to get "unique" hits? By "unique" I mean based on entity+id equality. > > Say I have several CSV files that may contain data on common entities but have varying columns and I've indexed them in separate indices in ES (using the same "entity type" in all indices). The result is several indices where some contain the same entity+id document. Can I ask ES to search through all indices and return unique documents (entity+id)? I guess it's similar to an SQL join where you query several tables and specify how you join them (in this case, the join would always happen on "\_id"). > > I know putting everything in one index would solve my problem, but for the sake of management, it's simpler for me to manage several indices (one per CSV so to speak). > > I this a valid feature for ES? Is there a way to do this already? > > Thanks, > Philippe ---
**Author:** ![plaflamme](https://avatars.discourse-cdn.com/v4/letter/p/a88e57/32.png) [@plaflamme](https://discuss.elastic.co/u/plaflamme)\ **Post date:** [April 22, 2011, 12:49pm UTC](https://discuss.elastic.co/t/cross-index-join/4272/3 "2011-04-22T12:49:22Z")
Ok, but is it something that could be implemented in ES directly? I don't mind writing a plugin for my own purposes, but I'd need to know whether it's doable. Seems like "has\_child" has a Collector implementation to do its thing. Would this be the place to start? Thanks, Philippe On Thu, Apr 21, 2011 at 18:52, Shay Banon [shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com)wrote: > There isn't an option to do that, since shards of different indices > exists on different nodes. You can ask for more than you might want, and > filter on the client side, or use a single index (as you suggested). > > On Thursday, April 21, 2011 at 11:53 PM, Philippe Laflamme wrote: > > Hi, > > When querying several indices, is there a way to get "unique" hits? By > "unique" I mean based on entity+id equality. > > Say I have several CSV files that may contain data on common entities but > have varying columns and I've indexed them in separate indices in ES (using > the same "entity type" in all indices). The result is several indices where > some contain the same entity+id document. Can I ask ES to search through all > indices and return unique documents (entity+id)? I guess it's similar to an > SQL join where you query several tables and specify how you join them (in > this case, the join would always happen on "\_id"). > > I know putting everything in one index would solve my problem, but for the > sake of management, it's simpler for me to manage several indices (one per > CSV so to speak). > > I this a valid feature for ES? Is there a way to do this already? > > Thanks, > Philippe ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [April 22, 2011, 5:07pm UTC](https://discuss.elastic.co/t/cross-index-join/4272/4 "2011-04-22T17:07:08Z")
No, you won't be able to do it since it will require talking to other shards over the wire, which will make this impractical. On Friday, April 22, 2011 at 3:49 PM, Philippe Laflamme wrote: > Ok, but is it something that could be implemented in ES directly? I don't mind writing a plugin for my own purposes, but I'd need to know whether it's doable. > > Seems like "has\_child" has a Collector implementation to do its thing. Would this be the place to start? > > Thanks, > Philippe > > On Thu, Apr 21, 2011 at 18:52, Shay Banon [shay.banon@elasticsearch.com](mailto:shay.banon@elasticsearch.com) wrote: > > > There isn't an option to do that, since shards of different indices exists on different nodes. You can ask for more than you might want, and filter on the client side, or use a single index (as you suggested). > > On Thursday, April 21, 2011 at 11:53 PM, Philippe Laflamme wrote: > > > > > Hi, > > > > > > When querying several indices, is there a way to get "unique" hits? By "unique" I mean based on entity+id equality. > > > > > > Say I have several CSV files that may contain data on common entities but have varying columns and I've indexed them in separate indices in ES (using the same "entity type" in all indices). The result is several indices where some contain the same entity+id document. Can I ask ES to search through all indices and return unique documents (entity+id)? I guess it's similar to an SQL join where you query several tables and specify how you join them (in this case, the join would always happen on "\_id"). > > > > > > I know putting everything in one index would solve my problem, but for the sake of management, it's simpler for me to manage several indices (one per CSV so to speak). > > > > > > I this a valid feature for ES? Is there a way to do this already? > > > > > > Thanks, > > > Philippe ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 4:07am UTC](https://discuss.elastic.co/t/cross-index-join/4272/5 "2017-07-06T04:07:51Z")