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
Après avoir monté une nouvelle équipe de développement, nous avons procédé à la migration de toute l’infrastructure technique sur une nouvelle architecture fortement dynamique à base de Symfony2, RabbitMQ, Elasticsearch et Chef. Les gains en performance, en stabilité et en capacité de développement permettent à l’entreprise d’engager de nouveaux marchés…
Dans le cadre d’une refonte complète de son architecture Web, Expertissim a sollicité l’expertise de JoliCode afin de tenir les délais et le niveau de qualité attendus. Le domaine métier d’Expertissim n’est pas trivial : les spécificités du marché de l’art apportent une logique métier bien particulière et un processus complexe. La plateforme propose…
Afin de soutenir le développement de son trafic, Qobuz a fait appel à JoliCode afin d’optimiser l’infrastructure technique du site et les échanges d’informations entre les composants de la plateforme. Suite à la mise en place de solution favorisant l’asynchronicité et la performance Web côté serveur, nous avons outillé la recherche de performance et…