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
}

{
  "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!