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

La nouvelle version du site naissance.fr développée s’appuie sur Symfony 2 et Elasticsearch. Cette refonte propose un tunnel d’achat spécialement développé pour l’application. Aujourd’hui, le site est équipé d’une gestion d’un mode d’envoi des faire-parts différé, de modification des compositions après paiement et de prise en charge de codes promotionnels…

À 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, …

Ouibus a pour ambition de devenir la référence du transport en bus longue distance. Dans cette optique, les enjeux à venir de la compagnie sont nombreux (vente multi-produit, agrandissement du réseau, diminution du time-to-market, amélioration de l’expérience et de la satisfaction client) et ont des conséquences sur la structuration de la nouvelle…