Combining Filtersedit

The previous two examples showed a single filter in use. In practice, you will probably need to filter on multiple values or fields. For example, how would you express this SQL in Elasticsearch?

SELECT product
FROM   products
WHERE  (price = 20 OR productID = "XHDK-A-1293-#fJ3")
  AND  (price != 30)

In these situations, you will need the bool filter. This is a compound filter that accepts other filters as arguments, combining them in various Boolean combinations.

Bool Filteredit

The bool filter is composed of three sections:

{
   "bool" : {
      "must" :     [],
      "should" :   [],
      "must_not" : [],
   }
}
must
All of these clauses must match. The equivalent of AND.
must_not
All of these clauses must not match. The equivalent of NOT.
should
At least one of these clauses must match. The equivalent of OR.

And that’s it! When you need multiple filters, simply place them into the different sections of the bool filter.

Each section of the bool filter is optional (for example, you can have a must clause and nothing else), and each section can contain a single filter or an array of filters.

To replicate the preceding SQL example, we will take the two term filters that we used previously and place them inside the should clause of a bool filter, and add another clause to deal with the NOT condition:

GET /my_store/products/_search
{
   "query" : {
      "filtered" : { 
         "filter" : {
            "bool" : {
              "should" : [
                 { "term" : {"price" : 20}}, 
                 { "term" : {"productID" : "XHDK-A-1293-#fJ3"}} 
              ],
              "must_not" : {
                 "term" : {"price" : 30} 
              }
           }
         }
      }
   }
}

Note that we still need to use a filtered query to wrap everything.

These two term filters are children of the bool filter, and since they are placed inside the should clause, at least one of them needs to match.

If a product has a price of 30, it is automatically excluded because it matches a must_not clause.

Our search results return two hits, each document satisfying a different clause in the bool filter:

"hits" : [
    {
        "_id" :     "1",
        "_score" :  1.0,
        "_source" : {
          "price" :     10,
          "productID" : "XHDK-A-1293-#fJ3" 
        }
    },
    {
        "_id" :     "2",
        "_score" :  1.0,
        "_source" : {
          "price" :     20, 
          "productID" : "KDKE-B-9947-#kL5"
        }
    }
]

Matches the term filter for productID = "XHDK-A-1293-#fJ3"

Matches the term filter for price = 20

Nesting Boolean Filtersedit

Even though bool is a compound filter and accepts children filters, it is important to understand that bool is just a filter itself. This means you can nest bool filters inside other bool filters, giving you the ability to make arbitrarily complex Boolean logic.

Given this SQL statement:

SELECT document
FROM   products
WHERE  productID      = "KDKE-B-9947-#kL5"
  OR (     productID = "JODL-X-1937-#pV7"
       AND price     = 30 )

We can translate it into a pair of nested bool filters:

GET /my_store/products/_search
{
   "query" : {
      "filtered" : {
         "filter" : {
            "bool" : {
              "should" : [
                { "term" : {"productID" : "KDKE-B-9947-#kL5"}}, 
                { "bool" : { 
                  "must" : [
                    { "term" : {"productID" : "JODL-X-1937-#pV7"}}, 
                    { "term" : {"price" : 30}} 
                  ]
                }}
              ]
           }
         }
      }
   }
}

Because the term and the bool are sibling clauses inside the first Boolean should, at least one of these filters must match for a document to be a hit.

These two term clauses are siblings in a must clause, so they both have to match for a document to be returned as a hit.

The results show us two documents, one matching each of the should clauses:

"hits" : [
    {
        "_id" :     "2",
        "_score" :  1.0,
        "_source" : {
          "price" :     20,
          "productID" : "KDKE-B-9947-#kL5" 
        }
    },
    {
        "_id" :     "3",
        "_score" :  1.0,
        "_source" : {
          "price" :      30, 
          "productID" : "JODL-X-1937-#pV7" 
        }
    }
]

This productID matches the term in the first bool.

These two fields match the term filters in the nested bool.

This was a simple example, but it demonstrates how Boolean filters can be used as building blocks to construct complex logical conditions.