How to build a OR condition in Elasticsearch Query DSL
Today I want to share a little piece of information about the Elasticsearch Query DSL we all love and cherish.
There is no “or” or “and” query.
There was one in Elasticsearch <= 2.4 but it’s long gone now.
So how do you build a “or” condition then?
Section intitulée introducing-the-quot-bool-should-quot-behaviorIntroducing the “Bool Should” behavior
Let’s start with a simple unique condition : name = foobar
:
GET _search
{
"query": {
"match": {
"name": "foobar"
}
}
}
Now we want to add another condition like AND price = 1000
.
To do that we use the “bool” query:
GET _search
{
"query": {
"bool": {
"filter": [
{
"match": {
"name": "foobar"
}
},
{
"match": {
"price": 1000
}
}
]
}
}
}
This translates to name = foobar AND price =1000
.
Now, I also want all documents with promoted = true
so my condition became:
(name = foobar AND price = 1000) OR promoted = true
This is where we struggle.
Simply adding a should on the query will not work:
GET _search
{
"query": {
"bool": {
"filter": [
{
"match": {
"name": "foobar"
}
},
{
"match": {
"price": 1000
}
}
],
+ "should": [
+ {
+ "match": {
+ "promoted": true
+ }
+ }
+ ]
}
}
}
This will translate to:
name = foobar AND price = 1000
Because we have two mandatory clauses in our Bool query already, the new one will never be considered (only to add some score but we don’t care about that here).
This is caused by the minimum_should_match option. This parameter specify the number or percentage of should clauses the documents must match.
If the Bool query includes at least one should clause and no must or filter clauses, the default value is 1. Otherwise, the default value is 0.
That mean we have minimum_should_match at 0, so the should clause is not used to retrieve documents.
The proper way to write our query is to go in a mode where minimum_should_match is 1:
GET _search
{
"query": {
"bool": {
"should": [
{
"bool": {
"filter": [
{
"match": {
"name": "foobar"
}
},
{
"match": {
"price": 1000
}
}
]
}
},
{
"match": {
"promoted": true
}
}
]
}
}
}
This time our root bool query only have a should option, so it will have to choose between documents matching name = foobar AND price = 1000
or documents matching promoted = true
thanks to the minimum_should_match of 1.
Section intitulée final-wordFinal word
- When doing “or” condition, just make sure to keep your Bool query clean, with no filter or must;
- You can play with the minimum_should_match parameter to make more than one clause mandatory;
- I have no idea if the “Bool Should” name is used outside of my brain 🤣 but that’s how I reference this tip when I explain this in my Elasticsearch trainings!
Section intitulée final-pro-tipFinal pro-tip
Struggling with the Query DSL? I know the feeling 🙏
Just know there is an API to translate SQL to Query DSL:
POST _sql/translate
{
"query": "SELECT first_name FROM users WHERE (MATCH(first_name, 'frank') OR MATCH(first_name, 'damien')) AND age < 100",
"fetch_size": 10
}
See the result of that query
{
"size": 10,
"query": {
"bool": {
"must": [
{
"bool": {
"should": [
{
"match": {
"first_name": {
"query": "frank"
}
}
},
{
"match": {
"first_name": {
"query": "damien"
}
}
}
],
"boost": 1
}
},
{
"range": {
"age": {
"lt": 100,
"boost": 1
}
}
}
],
"boost": 1
}
},
"_source": false,
"fields": [
{
"field": "first_name"
}
],
"sort": [
{
"_doc": {
"order": "asc"
}
}
],
"track_total_hits": -1
}
Happy searching!
Commentaires et discussions
Nos formations sur ce sujet
Notre expertise est aussi disponible sous forme de formations professionnelles !

Elasticsearch
Indexation et recherche avancée, scalable et rapide avec Elasticsearch
Ces clients ont profité de notre expertise

Dans le cadre d’une refonte complète de son architecture Web, le journal en ligne Mediapart a sollicité l’expertise de JoliCode afin d’accompagner ses équipes. Mediapart.fr est un des rares journaux 100% en ligne qui n’appartient qu’à ses lecteurs qui amène un fort traffic authentifiés et donc difficilement cachable. Pour effectuer cette migration, …

À la recherche du meilleur moyen de réaliser une fonctionnalité, Weglot à fait appel à nos équipes pour de l’expertise Elasticsearch. Fort de notre grande expérience avec Elasticsearch, nous avons pu itérer sur différentes implémentations qui répondaient au besoin, et choisir la meilleure. En plus de donner des axes d’amélioration concrets et argumentés, …

Dans le cadre du renouveau de sa stratégie digitale, Orpi France a fait appel à JoliCode afin de diriger la refonte du site Web orpi.com et l’intégration de nombreux nouveaux services. Pour effectuer cette migration, nous nous sommes appuyés sur une architecture de type “micro-services” à l’aide de PHP, Symfony, RabbitMQ, Elasticsearch et…