How can I write elastic query for below SQL query
Select from myindex where data > last_one_dat and (property1 >= 0 OR property2 >=0 )
data is in milliseconds.
Regards,
How can I write elastic query for below SQL query
Select from myindex where data > last_one_dat and (property1 >= 0 OR property2 >=0 )
data is in milliseconds.
Regards,
You can do this part with a script query but it'll be slow because it has to load both fields and check them. If the rest of the query is selective enough it'll still be fast enough though.
Use a bool
query for this with two range
queries in the should
clause.
Pardon me for missing some information previously. Here I need to skip the document when property1 and property2 are 0. I need to pick if either one or both is present. But the above query is giving me the document where property1 and property2 is 0
you can do like this:
POST /index/_search
{
"query": {
"bool": {
"must": [
{
"bool": {
"should": [
{
"range": {
"property1": {
"gte": 0
}
}
},
{
"range": {
"property2": {
"gte": 0
}
}
}
]
}
},
{
"range": {
"data": {
"gte": last_one_dat
}
}
}
]
}
}
}
use script:
POST index/_search
{
"query": {
"bool": {
"filter": {
"script": {
"script": {
"inline": "(doc['property1'].value>=0 | doc['property2'].value>0) & doc['date']> last_one_dat"
}
}
}
}
}
}
Thank you very much for the reply. I am able to use script but it works only after escaping the quotes
doc['"'"'property1'"'"'].value>=0
like this:
"(doc['"+property1+"'].value>=0"
And you can use "gt" instead of "gte" if you don't want to include the
lower bound.
© 2020. All Rights Reserved - Elasticsearch
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant logo are trademarks of the Apache Software Foundation in the United States and/or other countries.