Combining must_not in ElasticSearch Query

Question

I'm currently struggling with an ElastSearch query which currently looks the following:

...
"query": {
    "bool": {
        "must_not": [
            {
                "term": {
                    "bool-facet.criteria1": {
                        "value": false
                    }
                }
            },
            {
                "term": {
                    "bool-facet.criteria2": {
                        "value": false
                    }
                }
            }
        ]
    }
}
...

So now when either criteria1 OR criteria2 matches, the documents are ignored. How must the query look like so that only documents that match criteria1 AND criteria2 are ignored?

Eatables

which version ?! – Harsh

This is kind of confusing, so must_not(A,B) means -(A OR B) = -A AND -B, or does it mean -(A AND B) = -A OR -B ? Then you speak about those that are ignored, so the negation of the negation, not(must_not(A,B)) means --(A OR B) = A OR B, or does it mean --(A AND B) = A AND B? Maybe you can clarify. I guess when I do the logic then the right interpretation is that it matches if -A AND -B. And then even more confusing you are actually looking for the values to be FALSE, so another negation... – Pfennig

Answer 1

Since updating elasticsearch version was not possible I had to find another solution. This is what worked for me:

"query": {
    "bool": {
        "must_not" : [
            {
                "query": {
                    "bool": {
                        "must": [
                            {
                               "term": {
                                 "bool-facet.criteria1": false
                               }
                            },
                            {
                               "term": {
                                 "bool-facet.criteria2": false
                               }
                            }
                        ]
                    }
                }
            }
        ]
    }
}

Eatables

In version 5.5.1, I get a parse error with the following reason: "no [query] registered for [query]" – Loaves

I believe, inside 'must_not' there is no need write 'query' again. The inner 'must' clause can be written directly under 'must_not'. Not sure if its good practice or not. edit: aahh.. its already mentioned in below's answer. – Bose

Answer 2

If you want simple AND-behavior, then just nest another bool query inside of it:

"query": {
  "bool": {
    "must_not": [
      {
        "bool": {
          "filter": [
            {
              "term": {
                "bool-facet.criteria1": false
              }
            },
            {
              "term": {
                "bool-facet.criteria2": false
              }
            }
          ]
        }
      }
    ]
  }
}

Note that by using the filter (as it's a yes/no question with no scoring needed, but if you wanted scoring, then you would use must instead of filter) you get the desired AND behavior. This changes the question to "not(any document that has criteria1 == false AND criteria2 == false)".

Jocularity

This results in the following error for me: bool query does not support [filter] – Eatables

What version are you running? – Jocularity

Seems to be 1.4.5... you mean its possible with a later version? – Eatables

ok, i see this is a feature of 2.+ versions. i will try it out with the latest version. – Eatables

It was possible in order versions using a more convoluted syntax. But since it sounds like you're starting out, I would start with the latest version (if not even starting with a 5.0 alpha release if you're in development). – Jocularity

In 5.5.1, this did not work. It did not give me an error, but I saw documents in the results which should have been filtered out by the must_not. – Loaves

@Loaves If that's the case, then you should submit a bug with example data and the query to github.com/elastic/elasticsearch – Jocularity

In 7.X, this did not work. It did not give me an error, but I saw documents in the results which should have been filtered out by the must_not – Harsh

Answer 3

The following arrangement worked for me on a similar query in 5.5.1:

"query": {
    "bool": {
        "must": [
            {
                "bool":{
                    "must_not":{
                        "term":{
                            "bool-facet.criteria1": false
                        }
                    }
                }
            },
            {
                "bool":{
                    "must_not":{
                        "term":{
                            "bool-facet.criteria2": true
                        }
                    }
                }
            }
        ]
    }
} 

The other answers may have been correct for other versions, but did not work for me in 5.5.1.

Loaves

not working in 7.X. good that you mentioned your version in the answer! – Harsh

!(x and y) which was the question can be translated to !x or !y, so it seems to me your answer should have should instead of must on the top + minShouldClauses = 1, as must = and. But I haven't tried it. – Optimum

I'll gladly welcome an edit if there's an easy change that makes it work with 7.x. I'd recommend appending the 7.x query after the 5.x query so the reader can see the difference. If it's more complicated (like explaining should vs must) then you might want to add it as your own answer with longer explanation (and get SO points for it too). – Loaves

Answer 4

This behavior worked for me under 6.5.4:

GET model/_search
{
    "query": {
        "bool": {
            "must_not": [
                {
                    "exists": {
                        "field": "field1"
                    }
                },
                {
                    "exists": {
                        "field": "field2"
                    }
                },
                {
                    "exists": {
                        "field": "field3"
                    }
                }
            ]
        }
    }
}

Florous

Answer 5

This is what worked for me:

GET index/_search
{
  "query": {
    "bool": {
      "must_not": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "bool-facet.criteria1": false
                }
              },
              {
                "term": {
                  "bool-facet.criteria2": false
                }
              }
            ]
          }
        }
      ]
    }
  }
}

Phenomenon

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Tobolsk