CODEWITHSUNDEEP
elasticsearch
Gunner
Gunner

Reputation: 347

Elastic Search - OR querying for non matches

I'm having trouble querying in elastic search. I'm searching over a specific set of data defined by the state_id, and then wanting to return all the states which do not have either one of the cities defined by the identifiers below.

The query below returns 18 results with just "city_id_1", and 0 results with "city_id_2". With both though, I return 0 results (since "city_id_2" is on every state record). What I want to do is still return the 18 results, but query over both cities.

I feel like my query should be working, and basically doing a NOT (A or B) style query, equivalent to NOT A and NOT B, but basically the 0 results seems to be overriding the 18.

Is there a way I can change my query to get the results I want, or is this something elasticsearch cannot do?

{ 
  "query": {
    "bool": { 
      "must": [
        { "terms": { "state_id": ["4ca16f80-da79-11e5-9874-64006a4f57cb"]}}
      ],
      "must_not": [
        { 
          "nested": {
            "path": "cities",
            "query": {
              "bool": { 
                "should": [
                  {"term": { "cities.identifier": "city_id_1"}},
                  {"term": { "cities.identifier": "city_id_2"}}
                ]
              }
            }
          }
        }
      ]
    }
  },
 "size": 10

}

Upvotes: 2

Views: 79

Answers (3)

devon_m
devon_m

Reputation: 21

Try this on for size. Elasticsearch is silly. The filter needs to be in each of the nested queries.

{
  "query": {
    "bool": { 
      "should": [
        {
          "query": {
            "bool": {
              "must_not": [
                { 
                  "nested": {
                    "path": "cities",
                    "query": {
                      "term": { "cities.identifier": "city_id_1"}
                    }
                  }
                }
              ],
              "filter":[
                {
                  "term":{
                    "state_id":"4ca16f80-da79-11e5-9874-64006a4f57cb"
                  }
                }
              ]
            }
          }
        },
        {
          "query": {
            "bool": {
              "must_not": [
                { 
                  "nested": {
                    "path": "cities",
                    "query": {
                      "term": { "cities.identifier": "city_id_2"}
                    }
                  }
                }
              ],
              "filter":[
                {
                  "term":{
                    "state_id":"4ca16f80-da79-11e5-9874-64006a4f57cb"
                  }
                }
              ]
            }
          }
        }
      ]  
    }
  },
  "size": 10
}

Upvotes: 2

Rahul
Rahul

Reputation: 16335

As per my understanding, you are looking our for NOT A or NOT B kind of a clause. Please check the query below and see if it fits your requirement

{ 
  "query": {
    "bool": { 
      "must": [
        { "terms": { "state_id": ["4ca16f80-da79-11e5-9874-64006a4f57cb"]}}
      ],
      "should": [
        { 
          "nested": {
            "path": "cities",
            "query": {
              "bool": { 
                "must_not": [
                  {"term": { "cities.identifier": "city_id_1"}}
                  ]
              }
            }
          }
        },
        { 
          "nested": {
            "path": "cities",
            "query": {
              "bool": { 
                "must_not": [
                  {"term": { "cities.identifier": "city_id_2"}}
                  ]
              }
            }
          }
        }
      ],
      "minimum_number_should_match": 1
    }
  },
 "size": 10

}

Upvotes: 0

Richa
Richa

Reputation: 7649

If you want NOT A AND NOT B behaviour you need to make a little change

{ 
 "query": {
"bool": { 
  "must": [
    { "terms": { "state_id": ["4ca16f80-da79-11e5-9874-64006a4f57cb"]}}
  ],
  "must_not": [
    { 
      "nested": {
        "path": "cities",
        "query": {
          "bool": { 
            "must": [          ====> Use must instead of should
              {"term": { "cities.identifier": "city_id_1"}},
              {"term": { "cities.identifier": "city_id_2"}}
            ]
          }
        }
      }
     }
   ]
  }
 },
 "size": 10

}

This will exclude those record which will have both city_id_1 and city_id_2.

Upvotes: 0

Related Questions