How to specify conditional expressions (OR, AND, NOT) when searching documents in Elasticsearch?

We can specify conditional expressions like OR, AND using the Query expression during search in Elasticsearch.

We have an index named account and in the index we have details of account owners including their name, address, age, sex, employer etc.

Let’s search the documents with AGE=25 and STATE IN (‘ca’, ‘ny’) in the index.

Use SHOULD in place of OR

Use MUST in place of AND

SHOULD and MUST

Here is how we search documents with AGE=25 and STATE IN (‘ca’, ‘ny’). We got a total of 3 hits with that search.

Note that must and should takes in term which specifies the filtering criteria and they evaluate to a boolean (bool).

curl -X GET "localhost:9200/account/_search?pretty" -H 'Content-Type: application/json' -d'

{
    "query": {
        "bool": {
            "must": [
                {
                    "term": {"age": 25}
                },
                {
                    "bool": {
                        "should": [
                            {"term": {"state": "ca"}},
                            {"term": {"state": "ny"}}
                        ]
                    }
                }
            ]
        }
    }
}'

Here is the output.

{

  "took" : 8,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },

  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 5.046554,
    "hits" : [
      {
        "_index" : "account",
        "_type" : "_doc",
        "_id" : "68",
        "_score" : 5.046554,
        "_source" : {
          "account_number" : 68,
          "balance" : 44214,
          "firstname" : "Hall",
          "lastname" : "Key",
          "age" : 25,
          "gender" : "F",
          "address" : "927 Bay Parkway",
          "employer" : "Eventex",
          "email" : "hallkey@eventex.com",
          "city" : "Shawmut",
          "state" : "CA"
        }
      },
      {
        "_index" : "account",
        "_type" : "_doc",
        "_id" : "325",
        "_score" : 5.046554,
        "_source" : {
          "account_number" : 325,
          "balance" : 1956,
          "firstname" : "Magdalena",
          "lastname" : "Simmons",
          "age" : 25,
          "gender" : "F",
          "address" : "681 Townsend Street",
          "employer" : "Geekosis",
          "email" : "magdalenasimmons@geekosis.com",
          "city" : "Sterling",
          "state" : "CA"
        }
      },
      {
        "_index" : "account",
        "_type" : "_doc",
        "_id" : "308",
        "_score" : 4.88833,
        "_source" : {
          "account_number" : 308,
          "balance" : 33989,
          "firstname" : "Glass",
          "lastname" : "Schroeder",
          "age" : 25,
          "gender" : "F",
          "address" : "670 Veterans Avenue",
          "employer" : "Realmo",
          "email" : "glassschroeder@realmo.com",
          "city" : "Gratton",
          "state" : "NY"
        }
      }
    ]
  }
}

MUST NOT

Lets see how to execute a search with a not condition. Let’s try to search documents with AGE=25 and STATE IN (‘ca’, ‘ny’) and employer != ‘eventex’

Check out the use of MUST_NOT and we got 2 hits this time.

curl -X GET "localhost:9200/account/_search?pretty" -H 'Content-Type: application/json' -d'

{
    "query": {
        "bool": {
            "must": [
                {
                    "term": {"age": 25}
                },
                {
                    "bool": {
                        "should": [
                            {"term": {"state": "ca"}},
                            {"term": {"state": "ny"}}
                        ]
                    }
                },
                {
                    "bool": {
                        "must_not": [
                            {"term": {"employer": "eventex"}}
                        ]
                    }
                }
            ]
        }
    }
}'

Here is the output

{

  "took" : 9,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 5.046554,
    "hits" : [
      {
        "_index" : "account",
        "_type" : "_doc",
        "_id" : "325",
        "_score" : 5.046554,
        "_source" : {
          "account_number" : 325,
          "balance" : 1956,
          "firstname" : "Magdalena",
          "lastname" : "Simmons",
          "age" : 25,
          "gender" : "F",
          "address" : "681 Townsend Street",
          "employer" : "Geekosis",
          "email" : "magdalenasimmons@geekosis.com",
          "city" : "Sterling",
          "state" : "CA"
        }
      },
      {
        "_index" : "account",
        "_type" : "_doc",
        "_id" : "308",
        "_score" : 4.88833,
        "_source" : {
          "account_number" : 308,
          "balance" : 33989,
          "firstname" : "Glass",
          "lastname" : "Schroeder",
          "age" : 25,
          "gender" : "F",
          "address" : "670 Veterans Avenue",
          "employer" : "Realmo",
          "email" : "glassschroeder@realmo.com",
          "city" : "Gratton",
          "state" : "NY"
        }
      }
    ]
  }
}