Boolean Query returns wrong results

Elastic Stack Elasticsearch
1

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...

2

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

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"
    ]
  }
}
3

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

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

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

6

Problem is in exist query

7

There was a typo. Sorry.

"field": "manufacturer.id"

Should be

"field": "manufacturers.id"

8

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"
    ]
  }
}
9

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"
    ]
  }
}
10

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

          {
            "bool": {
              "must_not": [
                {
                  "nested": {
                    "path": "manufacturers",
                    "query": {
                      "exists": {
                        "field": "manufacturers.id"
                      }
                    }
                  }
                }
              ]
            }
          }
11 
{
  "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

12

it is so logical

13

[quote="buka_bidzina, post:11, topic:240177"]

    {
      "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"
14

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