# Can i filter with "and" and "or"? **URL:** \ **Category:** Elasticsearch\ **Created:** [December 28, 2011, 11:25pm UTC](https://discuss.elastic.co/t/can-i-filter-with-and-and-or/6247 "2011-12-28T23:25:08Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![mrblue](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mrblue/32/23268_2.png) [@mrblue](https://discuss.elastic.co/u/mrblue)\ **Post date:** [December 28, 2011, 11:25pm UTC](https://discuss.elastic.co/t/can-i-filter-with-and-and-or/6247/1 "2011-12-28T23:25:08Z")
Hi, i have a query with 3 "and". I´d like one of them to be "OR". So results would be for tag=green AND tag=blue AND (name= John OR name =Dennis). Can´t figure the correct syntax.. Here is my query: { "query" : { "match\_all" : { } } , ``` "filter" : { "and" : [ { "term" : { "bla" : "foo" }} { "term" : { "foo" : "blabla" }}, { "term" : { "color" : "blue" }} ] }, "facets" : { "uhren-facetten" : { "terms" : {"fields" : ["Hersteller" , ``` "Ziffernblattfarbe" , "Anzeige" , "Uhrwerk" , "Armbandfarbe" , "Funktionen" , "Armbandmaterial" , "Wasserdichte" ], "size" : "100" , "order" : "term" } , "all\_terms" : true } } } NOTE: there might be a typo or two because i build the query with PHP usually but it works fine so far 🙂 The Goal here would be to return results for color: blue OR color: yellow as long as all results are either foo-blabla-blue or foo-blabla yello. Thx a lot ! ---
**Author:** ![Ivan](https://avatars.discourse-cdn.com/v4/letter/i/df788c/32.png) [@Ivan](https://discuss.elastic.co/u/Ivan)\ **Post date:** [December 28, 2011, 11:49pm UTC](https://discuss.elastic.co/t/can-i-filter-with-and-and-or/6247/2 "2011-12-28T23:49:49Z")
Use "must" and "should" instead of "and" and "or". > **[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. > **[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. A blog post about the subject just came out when you posted: > **[Home](https://lucidworks.com/)** > > Lucidworks' Fusion platform uses industry-leading search technology to power search & discovery for the largest & most successful companies. Request a demo today. Cheers, Ivan On Wed, Dec 28, 2011 at 3:25 PM, mrblue [john.agricola.27@googlemail.com](mailto:john.agricola.27@googlemail.com) wrote: > Hi, > > i have a query with 3 "and". I´d like one of them to be "OR". > > So results would be for tag=green AND tag=blue AND (name= John OR name > =Dennis). Can´t figure the correct syntax.. > > Here is my query: > > { > "query" : { "match\_all" : { } } , > > ``` > "filter" : { > "and" : [ > { "term" : { "bla" : "foo" }} > { "term" : { "foo" : "blabla" }}, > { "term" : { "color" : "blue" }} > ] > }, > > "facets" : { > "uhren-facetten" : { "terms" : {"fields" : ["Hersteller" , > > ``` > > "Ziffernblattfarbe" , "Anzeige" , "Uhrwerk" , "Armbandfarbe" , > "Funktionen" , "Armbandmaterial" , "Wasserdichte" ], "size" : > "100" , "order" : "term" } , "all\_terms" : true } } > } > > NOTE: there might be a typo or two because i build the query with PHP > usually but it works fine so far 🙂 > > The Goal here would be to return results for color: blue OR color: > yellow as long as all results are either foo-blabla-blue or foo-blabla > yello. > > Thx a lot ! ---
**Author:** ![mrblue](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mrblue/32/23268_2.png) [@mrblue](https://discuss.elastic.co/u/mrblue)\ **Post date:** [December 29, 2011, 3:17am UTC](https://discuss.elastic.co/t/can-i-filter-with-and-and-or/6247/3 "2011-12-29T03:17:45Z")
Very interesting read and it probably fits my needs much better ! However, there is no way i can build the actual query from that doc inside elasticsearch.. Are you willing to help me out here ? I need a sample query for X is a must, Y is a must, A or B or C. In this case valid results would be X-Y-A , X-Y-B , X-Y-C (I need to dynamically add AND (=should ?) conditions to my query. (corresponding to the facets used). Then, some will have only one value to match, others might have up to ~12.) I start to like this 🙂 On Dec 29, 12:49 am, Ivan Brusic [i...@brusic.com](mailto:i...@brusic.com) wrote: > Use "must" and "should" instead of "and" and "or". > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/bool-query.htmlhttp://www.elasticsearch.org/guide/reference/query-dsl/bool-filter.html) > > A blog post about the subject just came out when you posted:[Search Technology & Search Platform | Lucidworks](http://www.lucidimagination.com/blog/2011/12/28/why-not-and-or-and-not/) > > Cheers, > > Ivan > > On Wed, Dec 28, 2011 at 3:25 PM, mrblue [john.agricola...@googlemail.com](mailto:john.agricola...@googlemail.com) wrote: > > > Hi, > > > i have a query with 3 "and". I´d like one of them to be "OR". > > > So results would be for tag=green AND tag=blue AND (name= John OR name > > =Dennis). Can´t figure the correct syntax.. > > > Here is my query: > > > { > > "query" : { "match\_all" : { } } , > > > ``` > > "filter" : { > > "and" : [ > > { "term" : { "bla" : "foo" }} > > { "term" : { "foo" : "blabla" }}, > > { "term" : { "color" : "blue" }} > > ] > > }, > > > > ``` > > > ``` > > "facets" : { > > "uhren-facetten" : { "terms" : {"fields" : ["Hersteller" , > > > > ``` > > > > "Ziffernblattfarbe" , "Anzeige" , "Uhrwerk" , "Armbandfarbe" , > > "Funktionen" , "Armbandmaterial" , "Wasserdichte" ], "size" : > > "100" , "order" : "term" } , "all\_terms" : true } } > > } > > > NOTE: there might be a typo or two because i build the query with PHP > > usually but it works fine so far 🙂 > > > The Goal here would be to return results for color: blue OR color: > > yellow as long as all results are either foo-blabla-blue or foo-blabla > > yello. > > > Thx a lot ! ---
**Author:** ![Frederic](https://avatars.discourse-cdn.com/v4/letter/f/4491bb/32.png) [@Frederic](https://discuss.elastic.co/u/Frederic)\ **Post date:** [December 29, 2011, 1:14pm UTC](https://discuss.elastic.co/t/can-i-filter-with-and-and-or/6247/4 "2011-12-29T13:14:55Z")
Hi John, I'm not sure how to build your query using 'must'/'should' but, with AND/OR it is pretty simple I guess: If you want somenthing like "X and Y and (A or B or C), you may have something like (a 'constant\_score' query is equivalent to a 'match\_all' one. [https://groups.google.com/group/elasticsearch/browse\_frm/thread/4dfdb2c2a2b30fde/26913f5863902251](https://groups.google.com/group/elasticsearch/browse_frm/thread/4dfdb2c2a2b30fde/26913f5863902251)): "query": { "constant\_score" : { "filter" : { "and" : [ { "term" : { "tagX" : "X" }} { "term" : { "tagY" : "Y" }}, { "or" : [ { "term" : { "tagABC" : "A" }} { "term" : { "tagABC" : "B" }} { "term" : { "tagABC" : "C" }} ] } ] } } } Off course I can't guarantee this is the most performant option, but it would do the work. Cheers, On 29 dic, 00:17, mrblue [john.agricola...@googlemail.com](mailto:john.agricola...@googlemail.com) wrote: > Very interesting read and it probably fits my needs much better ! > However, there is no way i can build the actual query from that doc > inside elasticsearch.. Are you willing to help me out here ? > > I need a sample query for X is a must, Y is a must, A or B or C. > > In this case valid results would be X-Y-A , X-Y-B , X-Y-C > > (I need to dynamically add AND (=should ?) conditions to my query. > (corresponding to the facets used). Then, some will have only one > value to match, others might have up to ~12.) > > I start to like this 🙂 > > On Dec 29, 12:49 am, Ivan Brusic [i...@brusic.com](mailto:i...@brusic.com) wrote: > > > Use "must" and "should" instead of "and" and "or". > > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/query-dsl/bool-query.htm)... > > > A blog post about the subject just came out when you posted:[Search Technology & Search Platform | Lucidworks](http://www.lucidimagination.com/blog/2011/12/28/why-not-and-or-and-not/) > > > Cheers, > > > Ivan > > > On Wed, Dec 28, 2011 at 3:25 PM, mrblue [john.agricola...@googlemail.com](mailto:john.agricola...@googlemail.com) wrote: > > > > > Hi, > > > > i have a query with 3 "and". I´d like one of them to be "OR". > > > > So results would be for tag=green AND tag=blue AND (name= John OR name > > > =Dennis). Can´t figure the correct syntax.. > > > > Here is my query: > > > > { > > > "query" : { "match\_all" : { } } , > > > > ``` > > > "filter" : { > > > "and" : [ > > > { "term" : { "bla" : "foo" }} > > > { "term" : { "foo" : "blabla" }}, > > > { "term" : { "color" : "blue" }} > > > ] > > > }, > > > > > > ``` > > > > ``` > > > "facets" : { > > > "uhren-facetten" : { "terms" : {"fields" : ["Hersteller" , > > > > > > ``` > > > > > > "Ziffernblattfarbe" , "Anzeige" , "Uhrwerk" , "Armbandfarbe" , > > > "Funktionen" , "Armbandmaterial" , "Wasserdichte" ], "size" : > > > "100" , "order" : "term" } , "all\_terms" : true } } > > > } > > > > NOTE: there might be a typo or two because i build the query with PHP > > > usually but it works fine so far 🙂 > > > > The Goal here would be to return results for color: blue OR color: > > > yellow as long as all results are either foo-blabla-blue or foo-blabla > > > yello. > > > > Thx a lot ! ---
**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, 3:44am UTC](https://discuss.elastic.co/t/can-i-filter-with-and-and-or/6247/5 "2017-07-06T03:44:11Z")