How to specify conditional expressions (OR, AND, NOT) when searching documents in Elasticsearch? - Big Data In Real World

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

How to see the first few lines from a file in S3 using AWS CLI?
April 12, 2021
How to properly remove a node from a Hadoop cluster?
April 16, 2021

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

 

Big Data In Real World
Big Data In Real World
We are a group of Big Data engineers who are passionate about Big Data and related Big Data technologies. We have designed, developed, deployed and maintained Big Data applications ranging from batch to real time streaming big data platforms. We have seen a wide range of real world big data problems, implemented some innovative and complex (or simple, depending on how you look at it) solutions.

Comments are closed.

gdpr-image
This website uses cookies to improve your experience. By using this website you agree to our Data Protection Policy.

Hadoop In Real World is now Big Data In Real World!

X