# Migrating lucene drill sideways query to elasticsearch **URL:** \ **Category:** Elasticsearch\ **Created:** [January 16, 2015, 3:15pm UTC](https://discuss.elastic.co/t/migrating-lucene-drill-sideways-query-to-elasticsearch/21690 "2015-01-16T15:15:03Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Bo\_Finnerup\_Madsen](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@Bo\_Finnerup\_Madsen](https://discuss.elastic.co/u/Bo_Finnerup_Madsen)\ **Post date:** [January 16, 2015, 3:15pm UTC](https://discuss.elastic.co/t/migrating-lucene-drill-sideways-query-to-elasticsearch/21690/1 "2015-01-16T15:15:03Z")
Hi, I am trying to migrate a project from Lucene to elasticsearch, and for the most part it is a pleasure 🙂 However, I cannot wrap my head around how to recreate the drill sideways queries we currently use in Lucene. The scenario is a basic search page with a free text search and a bunch of drill down/sideways facets. In Lucene, the hits that we get for each facet, is a correct representation of how many results we would get if that facet is used as a limit, but I am unable to do this in elasticsearch... As an example (full gist available here: [https://gist.github.com/bogundersen/e9bac02779e1c4a089dc](https://gist.github.com/bogundersen/e9bac02779e1c4a089dc)) I have three items: Item 1: language : en\_GB, year: 2013, author: [John, Paul] Item 2: language : en\_GB, year: 2012, author: [John, George] Item 3: language : da\_DK, year: 2012, author: [Ringo] Now lets imagine that the user limits to year 2012. If I just include the facet in the query ("Search 2" in the gist), I would get the following facets: year 2012 : 2 author George 1 John 1 Ringo 1 language da\_DK 1 en\_GB 1 The author and language facets show the correct numbers, but the year facet only shows year 2012 thereby not allowing the user to select another year without deselecting 2012. A way around this is to use post filters ("Search 3" in the gist), using those I get the following facet results: year 2012 : 2 2013 : 1 author John 2 George 1 Paul 1 Ringo 1 language en\_GB 2 da\_DK 1 Here the user is still presented with other years, but the numbers for author and language are not correct (e.g. selecting "John" will only give 1 result, and not two) The only way I can think of to make this work, is to do separate queries for each facet, but that seems counter intuitive and not very performance friendly. Any ideas on how to do this in elasticsearch? -- Bo Madsen -- 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). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [January 16, 2015, 3:46pm UTC](https://discuss.elastic.co/t/migrating-lucene-drill-sideways-query-to-elasticsearch/21690/2 "2015-01-16T15:46:39Z")
Have you considered to use filters / filter buckets like described in the guide? > **[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. Jörg On Fri, Jan 16, 2015 at 4:15 PM, Bo Finnerup Madsen [bo.gundersen@gmail.com](mailto:bo.gundersen@gmail.com) wrote: > Hi, > > I am trying to migrate a project from Lucene to elasticsearch, and for the > most part it is a pleasure 🙂 > However, I cannot wrap my head around how to recreate the drill sideways > queries we currently use in Lucene. > > The scenario is a basic search page with a free text search and a bunch of > drill down/sideways facets. In Lucene, the hits that we get for each facet, > is a correct representation of how many results we would get if that facet > is used as a limit, but I am unable to do this in elasticsearch... > > As an example (full gist available here: > [gist:e9bac02779e1c4a089dc · GitHub](https://gist.github.com/bogundersen/e9bac02779e1c4a089dc)) > > I have three items: > Item 1: > language : en\_GB, > year: 2013, > author: [John, Paul] > Item 2: > language : en\_GB, > year: 2012, > author: [John, George] > Item 3: > language : da\_DK, > year: 2012, > author: [Ringo] > > Now lets imagine that the user limits to year 2012. If I just include the > facet in the query ("Search 2" in the gist), I would get the following > facets: > year > 2012 : 2 > author > George 1 > John 1 > Ringo 1 > language > da\_DK 1 > en\_GB 1 > The author and language facets show the correct numbers, but the year > facet only shows year 2012 thereby not allowing the user to select another > year without deselecting 2012. > > A way around this is to use post filters ("Search 3" in the gist), using > those I get the following facet results: > year > 2012 : 2 > 2013 : 1 > author > John 2 > George 1 > Paul 1 > Ringo 1 > language > en\_GB 2 > da\_DK 1 > Here the user is still presented with other years, but the numbers for > author and language are not correct (e.g. selecting "John" will only give 1 > result, and not two) > > The only way I can think of to make this work, is to do separate queries > for each facet, but that seems counter intuitive and not very performance > friendly. Any ideas on how to do this in elasticsearch? > > -- > Bo Madsen > > -- > 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). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHMj%2BW3McNw\_qh\_iN9dQGyqAMTvVimre-g\_Q%3D-xzS%2BNmA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoHMj%2BW3McNw_qh_iN9dQGyqAMTvVimre-g_Q%3D-xzS%2BNmA%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![mikemccand](https://avatars.discourse-cdn.com/v4/letter/m/f04885/32.png) [@mikemccand](https://discuss.elastic.co/u/mikemccand)\ **Post date:** [January 16, 2015, 5:13pm UTC](https://discuss.elastic.co/t/migrating-lucene-drill-sideways-query-to-elasticsearch/21690/3 "2015-01-16T17:13:06Z")
I think you must do separate filters to compute the sideways facet counts. Mike McCandless [http://blog.mikemccandless.com](http://blog.mikemccandless.com) On Fri, Jan 16, 2015 at 10:15 AM, Bo Finnerup Madsen \<[bo.gundersen@gmail.com](mailto:bo.gundersen@gmail.com) > wrote: > Hi, > > I am trying to migrate a project from Lucene to elasticsearch, and for the > most part it is a pleasure 🙂 > However, I cannot wrap my head around how to recreate the drill sideways > queries we currently use in Lucene. > > The scenario is a basic search page with a free text search and a bunch of > drill down/sideways facets. In Lucene, the hits that we get for each facet, > is a correct representation of how many results we would get if that facet > is used as a limit, but I am unable to do this in elasticsearch... > > As an example (full gist available here: > [gist:e9bac02779e1c4a089dc · GitHub](https://gist.github.com/bogundersen/e9bac02779e1c4a089dc)) > > I have three items: > Item 1: > language : en\_GB, > year: 2013, > author: [John, Paul] > Item 2: > language : en\_GB, > year: 2012, > author: [John, George] > Item 3: > language : da\_DK, > year: 2012, > author: [Ringo] > > Now lets imagine that the user limits to year 2012. If I just include the > facet in the query ("Search 2" in the gist), I would get the following > facets: > year > 2012 : 2 > author > George 1 > John 1 > Ringo 1 > language > da\_DK 1 > en\_GB 1 > The author and language facets show the correct numbers, but the year > facet only shows year 2012 thereby not allowing the user to select another > year without deselecting 2012. > > A way around this is to use post filters ("Search 3" in the gist), using > those I get the following facet results: > year > 2012 : 2 > 2013 : 1 > author > John 2 > George 1 > Paul 1 > Ringo 1 > language > en\_GB 2 > da\_DK 1 > Here the user is still presented with other years, but the numbers for > author and language are not correct (e.g. selecting "John" will only give 1 > result, and not two) > > The only way I can think of to make this work, is to do separate queries > for each facet, but that seems counter intuitive and not very performance > friendly. Any ideas on how to do this in elasticsearch? > > -- > Bo Madsen > > -- > 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). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com) > [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAD7smRczzTifRND7XokyddfNH%2B050jBUnn%2ByhCLxe-jtYKpYeQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAD7smRczzTifRND7XokyddfNH%2B050jBUnn%2ByhCLxe-jtYKpYeQ%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Bo\_Finnerup\_Madsen](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@Bo\_Finnerup\_Madsen](https://discuss.elastic.co/u/Bo_Finnerup_Madsen)\ **Post date:** [January 16, 2015, 6:31pm UTC](https://discuss.elastic.co/t/migrating-lucene-drill-sideways-query-to-elasticsearch/21690/4 "2015-01-16T18:31:53Z")
Hi Jörg, That might actually do the trick. I have updated the gist ( [gist:e9bac02779e1c4a089dc · GitHub](https://gist.github.com/bogundersen/e9bac02779e1c4a089dc)) with a "Search 4" which uses this method. It gives the expected results, so that is good 🙂 How about the cost of this? We will be doing this for 4-5 facets, and using this method they will all be computed using their own set of filters... Den fredag den 16. januar 2015 kl. 16.46.46 UTC+1 skrev Jörg Prante: > Have you considered to use filters / filter buckets like described in the > guide? > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/en/elasticsearch/guide/current/_filtering_queries_and_aggregations.html) > > Jörg > > On Fri, Jan 16, 2015 at 4:15 PM, Bo Finnerup Madsen \<[bo.gun...@gmail.com](mailto:bo.gun...@gmail.com) > \\> wrote: > > > Hi, > > > > I am trying to migrate a project from Lucene to elasticsearch, and for > > the most part it is a pleasure 🙂 > > However, I cannot wrap my head around how to recreate the drill sideways > > queries we currently use in Lucene. > > > > The scenario is a basic search page with a free text search and a bunch > > of drill down/sideways facets. In Lucene, the hits that we get for each > > facet, is a correct representation of how many results we would get if that > > facet is used as a limit, but I am unable to do this in elasticsearch... > > > > As an example (full gist available here: > > [gist:e9bac02779e1c4a089dc · GitHub](https://gist.github.com/bogundersen/e9bac02779e1c4a089dc)) > > > > I have three items: > > Item 1: > > language : en\_GB, > > year: 2013, > > author: [John, Paul] > > Item 2: > > language : en\_GB, > > year: 2012, > > author: [John, George] > > Item 3: > > language : da\_DK, > > year: 2012, > > author: [Ringo] > > > > Now lets imagine that the user limits to year 2012. If I just include the > > facet in the query ("Search 2" in the gist), I would get the following > > facets: > > year > > 2012 : 2 > > author > > George 1 > > John 1 > > Ringo 1 > > language > > da\_DK 1 > > en\_GB 1 > > The author and language facets show the correct numbers, but the year > > facet only shows year 2012 thereby not allowing the user to select another > > year without deselecting 2012. > > > > A way around this is to use post filters ("Search 3" in the gist), using > > those I get the following facet results: > > year > > 2012 : 2 > > 2013 : 1 > > author > > John 2 > > George 1 > > Paul 1 > > Ringo 1 > > language > > en\_GB 2 > > da\_DK 1 > > Here the user is still presented with other years, but the numbers for > > author and language are not correct (e.g. selecting "John" will only give 1 > > result, and not two) > > > > The only way I can think of to make this work, is to do separate queries > > for each facet, but that seems counter intuitive and not very performance > > friendly. Any ideas on how to do this in elasticsearch? > > > > -- > > Bo Madsen > > > > -- > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/bceb2634-df14-4e3a-bd64-a2e3158a4592%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/bceb2634-df14-4e3a-bd64-a2e3158a4592%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Bo\_Finnerup\_Madsen](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@Bo\_Finnerup\_Madsen](https://discuss.elastic.co/u/Bo_Finnerup_Madsen)\ **Post date:** [January 16, 2015, 6:35pm UTC](https://discuss.elastic.co/t/migrating-lucene-drill-sideways-query-to-elasticsearch/21690/5 "2015-01-16T18:35:29Z")
Hi Mike, Thanks, that is in line with what Jörg suggested. I have updated the gist with a search using this approach and it gives the correct result. However I am a bit concerned about the cost of this, as we will be running 4-5 facets each of which will require their own set of filters. But if it is the recommended way, I will try to implemented it and run a performance test 🙂 Den fredag den 16. januar 2015 kl. 18.13.34 UTC+1 skrev Michael McCandless: > I think you must do separate filters to compute the sideways facet counts. > > Mike McCandless > > [http://blog.mikemccandless.com](http://blog.mikemccandless.com) > > On Fri, Jan 16, 2015 at 10:15 AM, Bo Finnerup Madsen \<[bo.gun...@gmail.com](mailto:bo.gun...@gmail.com) > \\> wrote: > > > Hi, > > > > I am trying to migrate a project from Lucene to elasticsearch, and for > > the most part it is a pleasure 🙂 > > However, I cannot wrap my head around how to recreate the drill sideways > > queries we currently use in Lucene. > > > > The scenario is a basic search page with a free text search and a bunch > > of drill down/sideways facets. In Lucene, the hits that we get for each > > facet, is a correct representation of how many results we would get if that > > facet is used as a limit, but I am unable to do this in elasticsearch... > > > > As an example (full gist available here: > > [gist:e9bac02779e1c4a089dc · GitHub](https://gist.github.com/bogundersen/e9bac02779e1c4a089dc)) > > > > I have three items: > > Item 1: > > language : en\_GB, > > year: 2013, > > author: [John, Paul] > > Item 2: > > language : en\_GB, > > year: 2012, > > author: [John, George] > > Item 3: > > language : da\_DK, > > year: 2012, > > author: [Ringo] > > > > Now lets imagine that the user limits to year 2012. If I just include the > > facet in the query ("Search 2" in the gist), I would get the following > > facets: > > year > > 2012 : 2 > > author > > George 1 > > John 1 > > Ringo 1 > > language > > da\_DK 1 > > en\_GB 1 > > The author and language facets show the correct numbers, but the year > > facet only shows year 2012 thereby not allowing the user to select another > > year without deselecting 2012. > > > > A way around this is to use post filters ("Search 3" in the gist), using > > those I get the following facet results: > > year > > 2012 : 2 > > 2013 : 1 > > author > > John 2 > > George 1 > > Paul 1 > > Ringo 1 > > language > > en\_GB 2 > > da\_DK 1 > > Here the user is still presented with other years, but the numbers for > > author and language are not correct (e.g. selecting "John" will only give 1 > > result, and not two) > > > > The only way I can think of to make this work, is to do separate queries > > for each facet, but that seems counter intuitive and not very performance > > friendly. Any ideas on how to do this in elasticsearch? > > > > -- > > Bo Madsen > > > > -- > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/ce635bd0-7295-4369-96d9-9d60d7578e8a%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/ce635bd0-7295-4369-96d9-9d60d7578e8a%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [January 16, 2015, 9:18pm UTC](https://discuss.elastic.co/t/migrating-lucene-drill-sideways-query-to-elasticsearch/21690/6 "2015-01-16T21:18:11Z")
I do not think you have to worry, I use a dozen of aggregations with filters with success on 50m docs with 8G RAM and 3 nodes. But if your tests show a massive slowdown, you should come back with your findings including performance numbers, so the ES core team can have a look at it. Jörg On Fri, Jan 16, 2015 at 7:35 PM, Bo Finnerup Madsen [bo.gundersen@gmail.com](mailto:bo.gundersen@gmail.com) wrote: > Hi Mike, > > Thanks, that is in line with what Jörg suggested. I have updated the gist > with a search using this approach and it gives the correct result. However > I am a bit concerned about the cost of this, as we will be running 4-5 > facets each of which will require their own set of filters. > But if it is the recommended way, I will try to implemented it and run a > performance test 🙂 > > Den fredag den 16. januar 2015 kl. 18.13.34 UTC+1 skrev Michael McCandless: > > > I think you must do separate filters to compute the sideways facet counts. > > > > Mike McCandless > > > > [http://blog.mikemccandless.com](http://blog.mikemccandless.com) > > > > On Fri, Jan 16, 2015 at 10:15 AM, Bo Finnerup Madsen \<[bo.gun...@gmail.com](mailto:bo.gun...@gmail.com) > > > > > wrote: > > > > > Hi, > > > > > > I am trying to migrate a project from Lucene to elasticsearch, and for > > > the most part it is a pleasure 🙂 > > > However, I cannot wrap my head around how to recreate the drill sideways > > > queries we currently use in Lucene. > > > > > > The scenario is a basic search page with a free text search and a bunch > > > of drill down/sideways facets. In Lucene, the hits that we get for each > > > facet, is a correct representation of how many results we would get if that > > > facet is used as a limit, but I am unable to do this in elasticsearch... > > > > > > As an example (full gist available here: [https://gist.github.com/](https://gist.github.com/) > > > bogundersen/e9bac02779e1c4a089dc) > > > > > > I have three items: > > > Item 1: > > > language : en\_GB, > > > year: 2013, > > > author: [John, Paul] > > > Item 2: > > > language : en\_GB, > > > year: 2012, > > > author: [John, George] > > > Item 3: > > > language : da\_DK, > > > year: 2012, > > > author: [Ringo] > > > > > > Now lets imagine that the user limits to year 2012. If I just include > > > the facet in the query ("Search 2" in the gist), I would get the following > > > facets: > > > year > > > 2012 : 2 > > > author > > > George 1 > > > John 1 > > > Ringo 1 > > > language > > > da\_DK 1 > > > en\_GB 1 > > > The author and language facets show the correct numbers, but the year > > > facet only shows year 2012 thereby not allowing the user to select another > > > year without deselecting 2012. > > > > > > A way around this is to use post filters ("Search 3" in the gist), using > > > those I get the following facet results: > > > year > > > 2012 : 2 > > > 2013 : 1 > > > author > > > John 2 > > > George 1 > > > Paul 1 > > > Ringo 1 > > > language > > > en\_GB 2 > > > da\_DK 1 > > > Here the user is still presented with other years, but the numbers for > > > author and language are not correct (e.g. selecting "John" will only give 1 > > > result, and not two) > > > > > > The only way I can think of to make this work, is to do separate queries > > > for each facet, but that seems counter intuitive and not very performance > > > friendly. Any ideas on how to do this in elasticsearch? > > > > > > -- > > > Bo Madsen > > > > > > -- > > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c% > > > [40googlegroups.com](http://40googlegroups.com) > > > [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > -- > > 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). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/ce635bd0-7295-4369-96d9-9d60d7578e8a%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/ce635bd0-7295-4369-96d9-9d60d7578e8a%40googlegroups.com) > > [https://groups.google.com/d/msgid/elasticsearch/ce635bd0-7295-4369-96d9-9d60d7578e8a%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/ce635bd0-7295-4369-96d9-9d60d7578e8a%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoETUF%2BbLo9z4WtFGTSCpJL9A0c0Lcys5gtrxyQGn\_VEpA%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoETUF%2BbLo9z4WtFGTSCpJL9A0c0Lcys5gtrxyQGn_VEpA%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Bo\_Finnerup\_Madsen](https://avatars.discourse-cdn.com/v4/letter/b/13edae/32.png) [@Bo\_Finnerup\_Madsen](https://discuss.elastic.co/u/Bo_Finnerup_Madsen)\ **Post date:** [January 20, 2015, 12:58pm UTC](https://discuss.elastic.co/t/migrating-lucene-drill-sideways-query-to-elasticsearch/21690/7 "2015-01-20T12:58:51Z")
I think you are right 🙂 I tried to implement it, and performance seems to be good. Thank you (and Michael) very much for the help. Den fredag den 16. januar 2015 kl. 22.18.17 UTC+1 skrev Jörg Prante: > I do not think you have to worry, I use a dozen of aggregations with > filters with success on 50m docs with 8G RAM and 3 nodes. But if your tests > show a massive slowdown, you should come back with your findings including > performance numbers, so the ES core team can have a look at it. > > Jörg > > On Fri, Jan 16, 2015 at 7:35 PM, Bo Finnerup Madsen \<[bo.gun...@gmail.com](mailto:bo.gun...@gmail.com) > \\> wrote: > > > Hi Mike, > > > > Thanks, that is in line with what Jörg suggested. I have updated the gist > > with a search using this approach and it gives the correct result. However > > I am a bit concerned about the cost of this, as we will be running 4-5 > > facets each of which will require their own set of filters. > > But if it is the recommended way, I will try to implemented it and run a > > performance test 🙂 > > > > Den fredag den 16. januar 2015 kl. 18.13.34 UTC+1 skrev Michael > > McCandless: > > > > > I think you must do separate filters to compute the sideways facet > > > counts. > > > > > > Mike McCandless > > > > > > [http://blog.mikemccandless.com](http://blog.mikemccandless.com) > > > > > > On Fri, Jan 16, 2015 at 10:15 AM, Bo Finnerup Madsen \< > > > [bo.gun...@gmail.com](mailto:bo.gun...@gmail.com)\> wrote: > > > > > > > Hi, > > > > > > > > I am trying to migrate a project from Lucene to elasticsearch, and for > > > > the most part it is a pleasure 🙂 > > > > However, I cannot wrap my head around how to recreate the drill > > > > sideways queries we currently use in Lucene. > > > > > > > > The scenario is a basic search page with a free text search and a bunch > > > > of drill down/sideways facets. In Lucene, the hits that we get for each > > > > facet, is a correct representation of how many results we would get if that > > > > facet is used as a limit, but I am unable to do this in elasticsearch... > > > > > > > > As an example (full gist available here: [https://gist.github.com/](https://gist.github.com/) > > > > bogundersen/e9bac02779e1c4a089dc) > > > > > > > > I have three items: > > > > Item 1: > > > > language : en\_GB, > > > > year: 2013, > > > > author: [John, Paul] > > > > Item 2: > > > > language : en\_GB, > > > > year: 2012, > > > > author: [John, George] > > > > Item 3: > > > > language : da\_DK, > > > > year: 2012, > > > > author: [Ringo] > > > > > > > > Now lets imagine that the user limits to year 2012. If I just include > > > > the facet in the query ("Search 2" in the gist), I would get the following > > > > facets: > > > > year > > > > 2012 : 2 > > > > author > > > > George 1 > > > > John 1 > > > > Ringo 1 > > > > language > > > > da\_DK 1 > > > > en\_GB 1 > > > > The author and language facets show the correct numbers, but the year > > > > facet only shows year 2012 thereby not allowing the user to select another > > > > year without deselecting 2012. > > > > > > > > A way around this is to use post filters ("Search 3" in the gist), > > > > using those I get the following facet results: > > > > year > > > > 2012 : 2 > > > > 2013 : 1 > > > > author > > > > John 2 > > > > George 1 > > > > Paul 1 > > > > Ringo 1 > > > > language > > > > en\_GB 2 > > > > da\_DK 1 > > > > Here the user is still presented with other years, but the numbers for > > > > author and language are not correct (e.g. selecting "John" will only give 1 > > > > result, and not two) > > > > > > > > The only way I can think of to make this work, is to do separate > > > > queries for each facet, but that seems counter intuitive and not very > > > > performance friendly. Any ideas on how to do this in elasticsearch? > > > > > > > > -- > > > > Bo Madsen > > > > > > > > -- > > > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > > msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c% > > > > [40googlegroups.com](http://40googlegroups.com) > > > > [https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/2e97801f-a091-4f1d-8e31-1ffb777f287c%40googlegroups.com?utm_medium=email&utm_source=footer) > > > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > > > -- > > > 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 [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/ce635bd0-7295-4369-96d9-9d60d7578e8a%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/ce635bd0-7295-4369-96d9-9d60d7578e8a%40googlegroups.com) > > > [https://groups.google.com/d/msgid/elasticsearch/ce635bd0-7295-4369-96d9-9d60d7578e8a%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/ce635bd0-7295-4369-96d9-9d60d7578e8a%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- 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). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/020d2bfc-0cf5-4439-992f-e507ab6b5582%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/020d2bfc-0cf5-4439-992f-e507ab6b5582%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**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, 12:37am UTC](https://discuss.elastic.co/t/migrating-lucene-drill-sideways-query-to-elasticsearch/21690/8 "2017-07-06T00:37:46Z")