# Boolean Query returns wrong results

**URL:** <https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177>\
**Category:** Elasticsearch\
**Created:** [July 7, 2020, 2:08pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177 "2020-07-07T14:08:30Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![buka\_bidzina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buka_bidzina/32/50141_2.png) [@buka\_bidzina](https://discuss.elastic.co/u/buka_bidzina)\
**Post date:** [July 7, 2020, 2:08pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/1 "2020-07-07T14:08:30Z")

</div>

> Mapping

```
PUT index
PUT /index/_mapping
{
  "properties": {
    "categories": {
      "type": "nested",
      "properties": {
        "id": {
          "type": "integer"
        }
      }
    },
    "deleted": {
      "type": "boolean"
    },
    "id": {
      "type": "long"
    },
    "manufacturers": {
      "type": "nested",
      "properties": {
        "id": {
          "type": "integer"
        }
      }
    },
    "vendors": {
      "type": "nested",
      "properties": {
        "id": {
          "type": "long"
        },
        "isDeletedVendor": {
          "type": "boolean"
        },
        "isPublishedVendor": {
          "type": "boolean"
        }
      }
    },
    "published": {
      "type": "boolean"
    }
  }
}

```

> Fill with Data

```
PUT index/_doc/1
{
  "categories": [
    {
      "id": 9
    }
  ],
  "manufacturers": [
    {
      "id": 2452
    }
  ],
  "vendors": [
    {
      "id": 8,
      "isDeletedVendor": true,
      "isPublishedVendor": true
    },
    {
      "id": 9,
      "isDeletedVendor": true,
      "isPublishedVendor": true
    }
  ],
  "published": true,
  "deleted": false
}

```

> Query

```
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "terms": {
                "categories.id": [
                  9
                ]
              }
            }
          }
        },
        {
          "terms": {
            "deleted": [
              false
            ]
          }
        },
        {
          "terms": {
            "published": [
              true
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "manufacturers",
                  "query": {
                    "terms": {
                      "manufacturers.id": [
                        2452
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "manufacturers"
                      }
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "vendors",
                  "query": {
                    "terms": {
                      "vendors.id": [
                        24
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "vendors"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "_source": {
    "includes": [
      "vendors.id",
      "manufacturers.id",
      "id"
    ]
  }
}

```

The Logic Should be something Like that:

**Categories in values** AND **Deleted in False** AND **Published in True** AND **ManufacturerIds in values** OR **manufacturersIds not exist** AND **vendorIds in values** OR **vendorIds not exist**

But sometimes it returns hits where manufacturers id is 10 or vendor id is 34 and so on...

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [July 7, 2020, 2:48pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/2 "2020-07-07T14:48:02Z")

</div>

You missed the nested part in the must not exist condition. It should be something like:

```auto
GET index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "categories",
            "query": {
              "terms": {
                "categories.id": [
                  9
                ]
              }
            }
          }
        },
        {
          "term": {
            "deleted": false
          }
        },
        {
          "term": {
            "published": true
          }
        },
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "nested": {
                  "path": "manufacturers",
                  "query": {
                    "terms": {
                      "manufacturers.id": [
                        2452
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "nested": {
                        "path": "manufacturers",
                        "query": {
                          "exists": {
                            "field": "manufacturers.id"
                          }
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "nested": {
                  "path": "vendors",
                  "query": {
                    "terms": {
                      "vendors.id": [
                        24
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "nested": {
                        "path": "vendors",
                        "query": {
                          "exists": {
                            "field": "vendors.id"
                          }
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "_source": {
    "includes": [
      "vendors.id",
      "manufacturers.id",
      "id"
    ]
  }
}

```

---

<div class="post-metadata">

**Author:** ![buka\_bidzina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buka_bidzina/32/50141_2.png) [@buka\_bidzina](https://discuss.elastic.co/u/buka_bidzina)\
**Post date:** [July 7, 2020, 2:49pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/3 "2020-07-07T14:49:44Z")

</div>

> [@dadoonet](#):
>
> ```auto
> GET index/_search
> {
> "query": {
> "bool": {
> "must": [
> {
> "nested": {
> "path": "categories",
> "query": {
> "terms": {
> "categories.id": [
> 9
> ]
> }
> }
> }
> },
> {
> "term": {
> "deleted": false
> }
> },
> {
> "term": {
> "published": true
> }
> },
> {
> "bool": {
> "minimum_should_match": 1,
> "should": [
> {
> "nested": {
> "path": "manufacturers",
> "query": {
> "terms": {
> "manufacturers.id": [
> 2452
> ]
> }
> }
> }
> },
> {
> "bool": {
> "must_not": [
> {
> "nested": {
> "path": "manufacturers",
> "query": {
> "exists": {
> "field": "manufacturer.id"
> }
> }
> }
> }
> ]
> }
> }
> ]
> }
> },
> {
> "bool": {
> "minimum_should_match": 1,
> "should": [
> {
> "nested": {
> "path": "vendors",
> "query": {
> "terms": {
> "vendors.id": [
> 24
> ]
> }
> }
> }
> },
> {
> "bool": {
> "must_not": [
> {
> "nested": {
> "path": "vendors",
> "query": {
> "exists": {
> "field": "vendors.id"
> }
> }
> }
> }
> ]
> }
> }
> ]
> }
> }
> ]
> }
> },
> "_source": {
> "includes": [
> "vendors.id",
> "manufacturers.id",
> "id"
> ]
> }
> }
> 
> ```

It stil gives me records with manufacturerds of 166 or 10...

---

<div class="post-metadata">

**Author:** ![buka\_bidzina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buka_bidzina/32/50141_2.png) [@buka\_bidzina](https://discuss.elastic.co/u/buka_bidzina)\
**Post date:** [July 7, 2020, 2:51pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/4 "2020-07-07T14:51:29Z")

</div>

```
 {
        "_index" : "index",
        "_type" : "_doc",
        "_id" : "3133",
        "_score" : 2.0176597,
        "_source" : {
          "manufacturers" : [
            {
              "id" : 166
            }
          ],
          "id" : 3133,
          "vendors" : [
            {
              "id" : 8
            },
            {
              "id" : 24
            }
          ]
        }
      },
```

---

<div class="post-metadata">

**Author:** ![buka\_bidzina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buka_bidzina/32/50141_2.png) [@buka\_bidzina](https://discuss.elastic.co/u/buka_bidzina)\
**Post date:** [July 7, 2020, 2:56pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/5 "2020-07-07T14:56:39Z")

</div>

I don't understand why this is happening... Query is written logically.

---

<div class="post-metadata">

**Author:** ![buka\_bidzina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buka_bidzina/32/50141_2.png) [@buka\_bidzina](https://discuss.elastic.co/u/buka_bidzina)\
**Post date:** [July 7, 2020, 3:07pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/6 "2020-07-07T15:07:47Z")

</div>

Problem is in exist query

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [July 7, 2020, 3:11pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/7 "2020-07-07T15:11:14Z")

</div>

There was a typo. Sorry.

> "field": "manufacturer.id"

Should be

> "field": "manufacturers.id"

---

<div class="post-metadata">

**Author:** ![buka\_bidzina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buka_bidzina/32/50141_2.png) [@buka\_bidzina](https://discuss.elastic.co/u/buka_bidzina)\
**Post date:** [July 7, 2020, 3:13pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/8 "2020-07-07T15:13:33Z")

</div>

no problem, anyway funny thing is that problem can be found in this little code, but I can't get it, it's so logical

```
{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                "nested": {
                  "path": "manufacturers",
                  "query": {
                    "terms": {
                      "manufacturers.id": [
                        2452
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "manufacturers"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "size": 10000, 
   "_source": {
    "includes": [
      "deleted",
      "published",
      "vendors.id",
      "categories.id",
      "manufacturers.id",
      "id"
    ]
  }
}
```

---

<div class="post-metadata">

**Author:** ![buka\_bidzina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buka_bidzina/32/50141_2.png) [@buka\_bidzina](https://discuss.elastic.co/u/buka_bidzina)\
**Post date:** [July 7, 2020, 3:16pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/9 "2020-07-07T15:16:52Z")

</div>

even this query not working

```
{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "should": [
              {
                "bool": {
                  "must_not": [
                    {
                      "exists": {
                        "field": "manufacturers"
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "size": 10000, 
   "_source": {
    "includes": [
      "deleted",
      "published",
      "vendors.id",
      "categories.id",
      "manufacturers.id",
      "id"
    ]
  }
}
```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [July 7, 2020, 3:36pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/10 "2020-07-07T15:36:00Z")

</div>

You are still missing the `nested` level... Look at my example:

```
          {
            "bool": {
              "must_not": [
                {
                  "nested": {
                    "path": "manufacturers",
                    "query": {
                      "exists": {
                        "field": "manufacturers.id"
                      }
                    }
                  }
                }
              ]
            }
          }
```

---

<div class="post-metadata">

**Author:** ![buka\_bidzina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buka_bidzina/32/50141_2.png) [@buka\_bidzina](https://discuss.elastic.co/u/buka_bidzina)\
**Post date:** [July 7, 2020, 3:40pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/11 "2020-07-07T15:40:17Z")

</div>

> [@dadoonet](#):
>
> ```auto
> {
> "bool": {
> "minimum_should_match": 1,
> "should": [
> {
> "nested": {
> "path": "manufacturers",
> "query": {
> "terms": {
> "manufacturers.id": [
> 2452
> ]
> }
> }
> }
> },
> {
> "bool": {
> "must_not": [
> {
> "nested": {
> "path": "manufacturers",
> "query": {
> "exists": {
> "field": "manufacturer.id"
> }
> }
> }
> }
> ]
> }
> }
> ]
> }
> },
> 
> ```

```
{
  "query": {
    "bool": {
      "must": [
        {
          "bool": {
            "minimum_should_match": 1,
            "should": [
              {
                "nested": {
                  "path": "manufacturers",
                  "query": {
                    "terms": {
                      "manufacturers.id": [
                        2452
                      ]
                    }
                  }
                }
              },
              {
                "bool": {
                  "must_not": [
                    {
                      "nested": {
                        "path": "manufacturers",
                        "query": {
                          "exists": {
                            "field": "manufacturer.id"
                          }
                        }
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "size": 10000,
  "_source": {
    "includes": [
      "deleted",
      "published",
      "vendors.id",
      "categories.id",
      "manufacturers.id",
      "id"
    ]
  }
}

```

Yeah sorry, but alone this is not working too

---

<div class="post-metadata">

**Author:** ![buka\_bidzina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buka_bidzina/32/50141_2.png) [@buka\_bidzina](https://discuss.elastic.co/u/buka_bidzina)\
**Post date:** [July 7, 2020, 3:40pm UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/12 "2020-07-07T15:40:34Z")

</div>

it is so logical

---

<div class="post-metadata">

**Author:** ![buka\_bidzina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buka_bidzina/32/50141_2.png) [@buka\_bidzina](https://discuss.elastic.co/u/buka_bidzina)\
**Post date:** [July 8, 2020, 7:12am UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/13 "2020-07-08T07:12:08Z")

</div>

[quote="buka\_bidzina, post:11, topic:240177"]

````auto
    {
      "query": {
        "bool": {
          "must": [
            {
              "bool": {
                "minimum_should_match": 1,
                "should": [
                  {
                    "nested": {
                      "path": "manufacturers",
                      "query": {
                        "terms": {
                          "manufacturers.id": [
                            2452
                          ]
                        }
                      }
                    }
                  },
                  {
                    "bool": {
                      "must_not": [
                        {
                          "nested": {
                            "path": "manufacturers",
                            "query": {
                              "exists": {
                                "field": "manufacturer.id"
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      "size": 10000,
      "_source": {
        "includes": [
          "deleted",
          "published",
          "vendors.id",
          "categories.id",
          "manufacturers.id",
          "id"
        ]
      }
    }
    ```
    [/quote]

    {
      "query": {
        "bool": {
          "must": [
            {
              "bool": {
                "minimum_should_match": 1,
                "should": [
                  {
                    "nested": {
                      "path": "manufacturers",
                      "query": {
                        "terms": {
                          "manufacturers.id": [
                            2452
                          ]
                        }
                      }
                    }
                  },
                  {
                    "bool": {
                      "must_not": [
                        {
                          "nested": {
                            "path": "manufacturers",
                            "query": {
                              "exists": {
                                "field": "manufacturer.id"
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      "size": 10000,
      "_source": {
        "includes": [
          "deleted",
          "published",
          "vendors.id",
          "categories.id",
          "manufacturers.id",
          "id"
        ]
      }
    }

problem was that in exist query i have written "field": "manufacturer.id", but I wanted "field": "manufacturers.id"
````

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 5, 2020, 7:12am UTC](https://discuss.elastic.co/t/boolean-query-returns-wrong-results/240177/14 "2020-08-05T07:12:16Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.