ElasticSearch / OpenSearch term search with logical OR

Viewed 165

I have been scratching my head for a while looking at OpenSearch documentation and stackoverflow questions. How can I do something like this:

Select documents WHERE studentId in [1234, 5678] OR applicationId in [2468, 1357].

As long as studentId exactly matches one of the supplied values, or applicationId exactly matches one of the supplied values, then that document should be included in the response.

When I want to search for multiple values for a single field and get an exact match the following works:

{
    "must":[
        {
            "terms": {
                "studentId":["1234", "5678"]
            }
        }
    ]
}

This will find me exact matches on studentId in [1234, 5678].

If I try to add the condition to also look for (logical or) applicationId in [2468, 1357] then the following will not work:

{
    "must":[
        {
            "terms": {
                "studentId":["1234", "5678"]
            }
        },
        {
            "terms": {
                "applicationId":["2468", "1357"]
            }
        }
    ]
}

because this will do a logical and on the two queries. I want logical or.

I cannot use should because this returns irrelevant results. The following does not work for me:

{
    "should":[
        {
            "terms": {
                "studentId":["1234", "5678"]
            }
        },
        {
            "terms": {
                "applicationId":["2468", "1357"]
            }
        }
    ]
}

This seems to return all results, ranked by relevance. I find that the returned results do not actually match, despite the fact that this is a terms search.

1 Answers

Can you try with following query..

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "terms": {
                  "studentId":["1234", "5678"]
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "terms": {
                  "applicationId":["2468", "1357"]
                }
              }
            ]
          }
        }
      ]
    }
  }
}
Related