I have a model for Book saved in elastic search. It has couple of attribute minimumAge and maximumAge, which determines what age the book is recommended for

I have couple of usecases for searching booking. First case is to search books by minimumAge. So suppose user searches for minimumAge 5, I have to get all the books which requires minimumAge to be 5 or those books which does not have minimumAge .

I did this using should clause and setting one minimum should clause to satisfy the query which works fine as shown below

private void addSearchByMinimumAge(ProductsFilter filter, BoolQueryBuilder rootQuery) {
    BoolQueryBuilder minAgeQuery = boolQuery();
    minAgeQuery.minimumShouldMatch(1);
    minAgeQuery.should(rangeQuery("minAge").lte(filter.getMinAge()));

    BoolQueryBuilder minAgeNotExistsQuery = boolQuery();
    minAgeNotExistsQuery.mustNot(existsQuery("minimumAge"));
    minAgeQuery.should(minAgeNotExistsQuery);
    rootQuery.must(minAgeQuery);
  }

As mentioned above use case works fine.

Second use case that I am trying to achieve is to get books by age. For example if a person searches for age 5, I need all the books where

• Age 5 is between or is equal to minAge and maxAge.
• If both minAge and maxAge is not available for a book, I should still return the book.
• If minAge is empty for a book but maxAge is less than or equal to 5 then that book should be returned.
• And finally if maxAge is empty then all the books with minAge greater than or equal to 5 should also be returned.

{ "name" : "Book1" , "minAge": 3 , "maxAge": 8 }
{ "name" : "Book2" , "minAge": 6 , "maxAge": 8 }
{ "name" : "Book3" , "minAge": 3 , "maxAge": 5 }
{ "name" : "Book4" , "minAge": 5 , "maxAge": 8 }
{ "name" : "Book5" }
{ "name" : "Book6" , "minAge": 3 }
{ "name" : "Book7" , "minAge": 6 }
{ "name" : "Book8" , "maxAge": 6 }
{ "name" : "Book9" , "maxAge": 4 }

In the above dataset if I pass Books 1,3,4,5,6,8 are valid book results

So I am stuck as to what would be a compact query to address this particular scenario. My first thought process is similar to first query but four should clause and matching any 1. Any pointers will be appreciated

CodePudding user response：

You can use below elasticsearch query and it will work for your requirements. I hope you will be able to create query in Java.

{
  "query": {
    "bool": {
      "should": [
        {
          "range": {
            "minAge": {
              "gte": 5
            }
          }
        },
        {
          "range": {
            "maxAge": {
              "lte": 5
            }
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "minAge"
                }
              },
              {
                "exists": {
                  "field": "maxAge"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

CodePudding user response：

You have to write each case in should clause to satisfy all the scenarios. Below elastic query will cover all your requirements -

{
  "query": {
    "bool": {
      "should": [
        {
          "bool" : {
             "must" : [
               {
                 "range": {
                   "minAge": {
                     "lte": 5
                    }
                  }
               },
               {
                 "range": {
                    "maxAge": {
                      "gte": 5
                     }
                  }
               }
             ] 
          } 
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "minAge"
                }
              },
              {
                "exists": {
                  "field": "maxAge"
                }
              }
            ]
          }
        }, 
        {
          "bool" : {
              "must" : [
                {
                 "range": {
                   "minAge": {
                     "lte": 5
                    }
                  }
                },
                {
                  "bool" : {
                     "must_not" : {
                         "exists" : {
                            "field" : "maxAge" 
                          } 
                      } 
                   } 
                } 
              ] 
           } 
        }, 
        {
          "bool" : {
              "must" : [
                {
                 "range": {
                   "maxAge": {
                     "gte": 5
                    }
                  }
                },
                {
                  "bool" : {
                     "must_not" : {
                         "exists" : {
                            "field" : "minAge" 
                          } 
                      } 
                   } 
                } 
              ] 
           } 
        } 
      ]
    }
  }
}