# Joins on weekly rotated indexes **URL:** \ **Category:** Elasticsearch\ **Created:** [July 30, 2013, 9:04am UTC](https://discuss.elastic.co/t/joins-on-weekly-rotated-indexes/12998 "2013-07-30T09:04:02Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Harris](https://avatars.discourse-cdn.com/v4/letter/h/c37758/32.png) [@Harris](https://discuss.elastic.co/u/Harris)\ **Post date:** [July 30, 2013, 9:04am UTC](https://discuss.elastic.co/t/joins-on-weekly-rotated-indexes/12998/1 "2013-07-30T09:04:02Z")
I need to perform joins on indexes that are rotated per week, is that possible? By 'rotated per week' I mean that I only want to perform indexing on this week's indexes and not past ones. Every week I create a new index, optimize the past ones and stop writing on hem. Thus, i wanted to have 2 aliases, one for searching (including all weeks' indexes) and one for indexing (pointing to this week's index). The problem is that nested documents, parent-child solutions all need to exist on the same index(shard). Is there any better solution that 2-step-query? -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![mvg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mvg/32/98890_2.png) [@mvg](https://discuss.elastic.co/u/mvg)\ **Post date:** [July 30, 2013, 11:12am UTC](https://discuss.elastic.co/t/joins-on-weekly-rotated-indexes/12998/2 "2013-07-30T11:12:14Z")
I think the terms lookup (which is part of the terms filter) can to do join you need: > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. On 30 July 2013 11:04, Harris [dimitriou.xar@gmail.com](mailto:dimitriou.xar@gmail.com) wrote: > I need to perform joins on indexes that are rotated per week, is that > possible? By 'rotated per week' I mean that I only want to perform indexing > on this week's indexes and not past ones. Every week I create a new index, > optimize the past ones and stop writing on hem. Thus, i wanted to have 2 > aliases, one for searching (including all weeks' indexes) and one for > indexing (pointing to this week's index). > The problem is that nested documents, parent-child solutions all need to > exist on the same index(shard). > > Is there any better solution that 2-step-query? > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- Met vriendelijke groet, Martijn van Groningen -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![Harris](https://avatars.discourse-cdn.com/v4/letter/h/c37758/32.png) [@Harris](https://discuss.elastic.co/u/Harris)\ **Post date:** [July 30, 2013, 11:58am UTC](https://discuss.elastic.co/t/joins-on-weekly-rotated-indexes/12998/3 "2013-07-30T11:58:05Z")
Hmm I am not sure this is what I wanted. I am more like into "bring me all the users who have tweeted from 23:00 to 24:00", or "bring me all the tweets of the users who have at least once tweeted from 23:00-24:00" Thus, it's not that i do the query only for a given id, i want to do it for all ids. In addition i expect the tweets to exist on multiple indexes (let's say one index per week). On Tue, Jul 30, 2013 at 2:12 PM, Martijn v Groningen \< [martijn.v.groningen@gmail.com](mailto:martijn.v.groningen@gmail.com)\> wrote: > I think the terms lookup (which is part of the terms filter) can to do > join you need: > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/terms-filter/) > > On 30 July 2013 11:04, Harris [dimitriou.xar@gmail.com](mailto:dimitriou.xar@gmail.com) wrote: > > > I need to perform joins on indexes that are rotated per week, is that > > possible? By 'rotated per week' I mean that I only want to perform indexing > > on this week's indexes and not past ones. Every week I create a new index, > > optimize the past ones and stop writing on hem. Thus, i wanted to have 2 > > aliases, one for searching (including all weeks' indexes) and one for > > indexing (pointing to this week's index). > > The problem is that nested documents, parent-child solutions all need to > > exist on the same index(shard). > > > > Is there any better solution that 2-step-query? > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > -- > Met vriendelijke groet, > > Martijn van Groningen > > -- > You received this message because you are subscribed to a topic in the > Google Groups "elasticsearch" group. > To unsubscribe from this topic, visit > [https://groups.google.com/d/topic/elasticsearch/1OlWcrEXcbY/unsubscribe](https://groups.google.com/d/topic/elasticsearch/1OlWcrEXcbY/unsubscribe). > To unsubscribe from this group and all its topics, send an email to > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**Author:** ![mattweber](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattweber/32/44940_2.png) [@mattweber](https://discuss.elastic.co/u/mattweber)\ **Post date:** [July 30, 2013, 2:28pm UTC](https://discuss.elastic.co/t/joins-on-weekly-rotated-indexes/12998/4 "2013-07-30T14:28:33Z")
What would you be joining? Just searching across indices works just fine. Now if you have an index of users and an index for tweets that need to be "joined", you will need something like the terms lookup by query which is not supported by ES yet. > > > This PR adds support for generating a terms filter based on the field values > of …documents matching a specified lookup query/filter. The value of the > configurable "path" field is collected from the field data cache for each > document matching the lookup query/filter and is then used to filter the main > query. This is can also be called a join filter. > > This PR abstracts the TermsLookup functionality in order to support multiple > lookup methods. The existing functionality is moved into FieldTermsLookup and > the new query based lookup is in QueryTermsLookup. All existing caching > functionality works with the new query based lookup for increased performance. > > During testing of I found that one of the performance bottlenecks was > generating the Lucene TermsFilter on large sets of terms (probably since > it sorts the terms). I have created a FieldDataTermsFilter that uses the > field data cache to lookup value of the field being filtered and compare it to > the set of gathered terms. This significantly increased performance at the > cost of higher memory usage. Currently a TermsFilter is used when the number > of filtering terms is less than 1024 and the FieldDataTermsFilter is used > for everything else. This should eventually be configurable or we need to > perform some test to find the optimal value. > > Examples: > > Replicate a has\_child query by joining on the child's "pid" field to the > parent's "id" field for each child that has the tag "something". > > \`\`\` > curl -XPOST 'http://localhost:9200/parentIndex/\_search' -d '{ > "query": { > "constant\_score": { > "filter": { > "terms": { > "id": { > "index": "childIndex", > "type": "childType", > "path": "pid", > "query": { > "term": { > "tag": "something" > } > } > } > } > } > } > } > }' > \`\`\` > > Lookup companies that offer products or services mentioning elasticsearch. > Notice that products and services are kept in their own indices. > > \`\`\` > curl -XPOST 'http://localhost:9200/companies/\_search' -d '{ > "query": { > "constant\_score": { > "filter": { > "terms": { > "company\_id": { > "indices": \["products", "services"\], > "path": "company\_id", > "filter": { > "term": { > "description": "elasticsearch" > } > } > } > } > } > } > } > }' > \`\`\` Thanks, Matt Weber On Tuesday, July 30, 2013, Harris Dimitriou wrote: > Hmm I am not sure this is what I wanted. I am more like into "bring me all > the users who have tweeted from 23:00 to 24:00", or "bring me all the > tweets of the users who have at least once tweeted from 23:00-24:00" > > Thus, it's not that i do the query only for a given id, i want to do it > for all ids. In addition i expect the tweets to exist on multiple indexes > (let's say one index per week). > > On Tue, Jul 30, 2013 at 2:12 PM, Martijn v Groningen \< > [martijn.v.groningen@gmail.com](mailto:martijn.v.groningen@gmail.com) \ 'martijn.v.groningen@gmail.com');\>\> wrote: > > > I think the terms lookup (which is part of the terms filter) can to do > > join you need: > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/terms-filter/) > > > > On 30 July 2013 11:04, Harris \<[dimitriou.xar@gmail.com](mailto:dimitriou.xar@gmail.com)\ > > > > > wrote: > > > > > I need to perform joins on indexes that are rotated per week, is that > > > possible? By 'rotated per week' I mean that I only want to perform indexing > > > on this week's indexes and not past ones. Every week I create a new index, > > > optimize the past ones and stop writing on hem. Thus, i wanted to have 2 > > > aliases, one for searching (including all weeks' indexes) and one for > > > indexing (pointing to this week's index). > > > The problem is that nested documents, parent-child solutions all need to > > > exist on the same index(shard). > > > > > > Is there any better solution that 2-step-query? > > > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send > > > an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com)\ > > > . > > > > > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > > > -- > > Met vriendelijke groet, > > > > Martijn van Groningen > > > > -- > > You received this message because you are subscribed to a topic in the > > Google Groups "elasticsearch" group. > > To unsubscribe from this topic, visit > > [https://groups.google.com/d/topic/elasticsearch/1OlWcrEXcbY/unsubscribe](https://groups.google.com/d/topic/elasticsearch/1OlWcrEXcbY/unsubscribe). > > To unsubscribe from this group and all its topics, send an email to > > [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com) \ > 'elasticsearch%2Bunsubscribe@googlegroups.com');\>. > > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com) \ 'cvml', 'elasticsearch%2Bunsubscribe@googlegroups.com');\>. > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). -- Thanks, Matt Weber -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out). ---
**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, 2:23am UTC](https://discuss.elastic.co/t/joins-on-weekly-rotated-indexes/12998/5 "2017-07-06T02:23:46Z")
Closed on July 6, 2017, 2:23am UTC