ElasticSearch : IN equivalent operator in ElasticSearch
Solution 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).
Solution 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"
}
}
]
}
}
]
}
}
}
- 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"
}
}
]
}