ElasticSearch should with nested and bool must_not exists

user_93812 0
Viewed 794

With the following mapping:

"categories": {
  "type": "nested",
  "properties": {
      "category": {
          "type": "integer"
      },
      "score": {
          "type": "float"
      }
  }
},

I want to use the categories field to return documents that either:

  • have a score above a threshold in a given category, or
  • do not have the categories field

This is my query:

{
  "query": {
    "bool": {
      "should": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "bool": {
                "must": [
                  {
                    "terms": {
                      "categories.category": [
                        <id>
                      ]
                    }
                  },
                  {
                    "range": {
                      "categories.score": {
                        "gte": 0.5
                      }
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "bool": {
            "must_not": [
              {
                "exists": {
                  "field": "categories"
                }
              }
            ]
          }
        }
      ],
      "minimum_should_match": 1
    }
  }
}

It correctly returns documents both with and without the categories field, and orders the results so the ones I want are first, but it doesn't filter the results having score below the 0.5 threshold.

elasticsearch
1 Answers

Great question.

That is because categories is not exactly a field from the elasticsearch point of view[a field on which inverted index is created and used for querying/searching] but categories.category and categories.score is.

As a result categories being not found in any document, which is actually true for all the documents, you observe the result what you see.

Modify the query to the below and you'd see your use-case working correctly.

POST <your_index_name>/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "bool": {
                "must": [
                  {
                    "terms": {
                      "categories.category": [
                        "100"
                      ]
                    }
                  },
                  {
                    "range": {
                      "categories.score": {
                        "gte": 0.5
                      }
                    }
                  }
                ]
              }
            }
          }
        },
        {
          "bool": {
            "must_not": [                        <----- Note this
              {
                "nested": {
                  "path": "categories",
                  "query": {
                    "bool": {
                      "must": [
                        {
                          "exists": {
                            "field": "categories.category"
                          }
                        },
                        {
                          "exists": {
                            "field": "categories.score"
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      ],
      "minimum_should_match": 1
    }
  }
}
user_38383280
Related