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?
Elastic Cloud on Kubernetes (ECK) is the official operator by Elastic for automating the deployment, provisioning, management, and orchestration of Elasticsearch, Kibana, APM Server, Beats, Enterprise Search, Elastic Agent and Elastic Maps Server on Kubernetes.
gte for Greater than or equal to. lt for Less than. lte for Less than or equal to.
Joining queriesedit Instead, Elasticsearch offers two forms of join which are designed to scale horizontally. Documents may contain fields of type nested . These fields are used to index arrays of objects, where each object can be queried (with the nested query) as an independent document.
The match query is of type boolean . It means that the text provided is analyzed and the analysis process constructs a boolean query from the provided text. The operator parameter can be set to or or and to control the boolean clauses (defaults to or ).
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).
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" } } ] } } ] } } }
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" } } ] }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With