elasticsearch must OR must

I have this query for my elasticsearch request:

{
    "query": {
        "bool": {
            "filter": {
                "bool": {
                    "should" : [
                        {                
                            "bool" : {
                                "must_not": {
                                    "exists": {
                                        "field": "visibility_id"
                                    }
                                }
                            }
                        },
                        {
                            "bool" : {
                                "must": {
                                    "terms": {
                                        "visibility.visibility": ["visible"]
                                    }
                                }
                            }
                        }
                    ]
                }
            }
        }
    }
}

The goal is to check if the row visibility_id is in the table. If not it will return true has it reach the "must_not". But if the visibility_id column is present it needs to check that this is set to "visible".

At the moment it works if the visibility_id is null but it does not check the terms. terms can be anything else but visible and it will works.

Can someone help me please, I am new to elasticsearch. (I have tried without the filter, bool, only with the should but it does not work neither.)

CodePudding user response：

Try this query, you're missing minimum_should_match: 1

{
  "query": {
    "bool": {
      "minimum_should_match": 1,
      "should": [
        {
          "bool": {
            "must_not": {
              "exists": {
                "field": "visibility_id"
              }
            }
          }
        },
        {
          "terms": {
            "visibility.visibility": [
              "visible"
            ]
          }
        }
      ]
    }
  }
}

If visibility is nested in your mapping, your query needs to be like this instead:

{
  "query": {
    "bool": {
      "minimum_should_match": 1,
      "should": [
        {
          "bool": {
            "must_not": {
              "exists": {
                "field": "visibility_id"
              }
            }
          }
        },
        {
          "nested": {
            "path": "visibility",
            "query": {
              "terms": {
                "visibility.visibility": [
                  "visible"
                ]
              }
            }
          }
        }
      ]
    }
  }
}