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

can u please share your index data, mapping, and expected search result? — Bhavya
– Bhavya, Commented Oct 14, 2020 at 10:46

Bhavya · Accepted Answer · 2020-10-14 10:59:37Z

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

Saeed Nasehi · Accepted Answer · 2020-10-14 10:56:36Z

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

Collectives™ on Stack Overflow

How to write a conditional in a search query?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related