How to write a conditional in a search query?

Question

I am searching among documents in a particular district. Documents have various statuses. The aim is to return all documents, except when document's status code is ABCD - such documents should only be returned if their ID is greater than 100. I have tried writing multiple queries, including the one below, which returns only the ABCD documents with ID greater than 100, and none of the other documents. What is wrong here? How can I get the non-ABCD documents as well?

    "_source": true,
    "from": 0,
    "size": 50,
    "sort": [
      {
        "firstStamp": "DESC"
      }
    ],
    "query": {
      "bool": {
        "must": [
          {
            "term": {
              "districtId": "3755"
            }
          },
          {
            "bool": {
              "must": [
                {
                  "terms": {
                    "documentStatus.code.keyword": [
                      "ABCD"
                    ]
                  }
                },
                {
                  "bool": {
                    "must": {
                      "script": {
                        "script": "doc['id'].value > 100"
                      }
                    }
                  }
                }
              ]
            }
          }
        ]
      }
    }
  }```

Answer 1

Since you have not added any index mapping, looking at your search query data seems to be of object field data type. As far as I can understand, your aim is to return all documents, except when the document's status code is ABCD and document with status code ABCD should only be returned if their ID is greater than 100.

Adding a working example with index data, search query, and search result

Index Data:

{
  "id":200,
  "documentStatus":{
    "code":"DEF"
  }
}
{
  "id":200,
  "documentStatus":{
    "code":"ABCD"
  }
}
{
  "id":100,
  "documentStatus":{
    "code":"ABCD"
  }
}

Search Query:

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "terms": {
                  "documentStatus.code.keyword": [
                    "ABCD"
                  ]
                }
              },
              {
                "bool": {
                  "must": {
                    "script": {
                      "script": "doc['id'].value > 100"
                    }
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must_not": {
              "terms": {
                "documentStatus.code.keyword": [
                  "ABCD"
                ]
              }
            }
          }
        }
      ]
    }
  }
}

Search Result:

"hits": [
      {
        "_index": "stof_64351595",
        "_type": "_doc",
        "_id": "2",
        "_score": 2.0,
        "_source": {
          "id": 200,
          "documentStatus": {
            "code": "ABCD"
          }
        }
      },
      {
        "_index": "stof_64351595",
        "_type": "_doc",
        "_id": "3",
        "_score": 0.0,
        "_source": {
          "id": 200,
          "documentStatus": {
            "code": "DEF"
          }
        }
      }
    ]

Answer 2

You need to use must_not in your query if you want to have documents which don't have status code = ABCD. So your query would be some thing like this:

    "from": 0,
    "size": 50,
    "sort": [
      {
        "firstStamp": "DESC"
      }
    ],
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "districtId": "3755"
          }
        },
        {
          "range": {
            "id": {
              "gt": 100
            }
          }
        }
      ],
      "must_not": [
        {
          "terms": {
            "documentStatus.code.keyword": [
              "ABCD"
            ]
          }
        }
      ]
    }
  }
}