ElasticSearch : IN equivalent operator in ElasticSearch

Question

I am trying to find ElasticSearch query equivalent to IN \ NOT in SQL.

I know we can use QueryString query with multiple OR to get the same answer, but that ends up with lot of OR's.

Can anyone share the example?

Answer 1

Similar to what Chris suggested as a comment, the analogous replacement for IN is the terms filter (queries imply scoring, which may improve the returned order).

SELECT * FROM table WHERE id IN (1, 2, 3);

The equivalent Elasticsearch 1.x filter would be:

{
  "query" : {
    "filtered" : {
      "filter" : {
        "terms" : {
          "id" : [1, 2, 3]
        }
      }
    }
  }
}

The equivalent Elasticsearch 2.x+ filter would be:

{
  "query" : {
    "bool" : {
      "filter" : {
        "terms" : {
          "id" : [1, 2, 3]
        }
      }
    }
  }
}

The important takeaway is that the terms filter (and query for that matter) work on exact matches. It is implicitly an or operation, similar to IN.

If you wanted to invert it, you could use the not filter, but I would suggest using the slightly more verbose bool/must_not filter (to get in the habit of also using bool/must and bool).

{
  "query" : {
    "bool" : {
      "must_not" : {
        "terms" : {
          "id" : [1, 2, 3]
        }
      }
    }
  }
}

Overall, the bool compound query syntax is one of the most important filters in Elasticsearch, as are the term (singular) and terms filters (plural, as shown).

Answer 2

1 terms

you can use terms term query in ElasticSearch that will act as IN

terms query is used to check if the value matches any of the provided values from Array.

2 must_not

must_not can be used as NOT in ElasticSearch.

ex.

GET my_index/my_type/_search
{
    "query" : {
         "bool" : {
              "must":[
                {
                   "terms": {
                        "id" : ["1234","12345","123456"]
                    }
                },
                {
                   "bool" : {
                        "must_not" : [
                            {
                              "match":{
                                  "id" : "123"
                               }
                            }
                        ]
                    }
                }
              ]
         }
    }
}

3. exists

Also if it helps you can also use "exists" query to check if the field exists or not. for ex, check if the field exists

"exists" : {
      "field" : "mobileNumber"
   }

check if a field does not exist

"bool":{
    "must_not" : [
        {
           "exists" : {
               "field" : "mobileNumber"
           }
        }
     ]
}

Answer 3

I saw what you requested. And I wrote the source code as below.

I hope this helps you solve your problem.

sql query :

select * from tablename where fieldname in ('AA','BB');

elastic search :

{
    query :{
        bool:{
            must:[{
              "script": {
                "script":{
                  "inline": "(doc['fieldname'].value.toString().substring(0,2).toUpperCase() in ['AA','BB']) == true"
                }
              }
            }],
            should:[],
            must_not:[]
        }
    }
}