ElasticSearch query with AND and OR criteria
Frequently when we will need to construct query to elasticsearch which bear AND and OR criteria similar to the following SQL statements:
Statement 1:
SELECT * FROM tblOrder WHERE orderDate=2015-01-01 14:00:00 AND customerID=29439;
Statement 2:
SELECT * FROM tblOrder WHERE orderDate=2015-01-01 14:00:00 OR customerID=29439;
Statement 3:
SELECT * FROM tblOrder WHERE orderDate <= 2015-01-01 14:00:00 AND customerID=29439;
Statement 4:
SELECT * FROM tblOrder WHERE (orderDate=2015-01-01 14:00:00 AND customerID=29439) OR customerID = 20991;
Statement 5:
SELECT * FROM tblOrder WHERE orderDate=2015-01-01 14:00:00 AND (customerID=29439 OR customerID = 20991);
In ElasticSearch, we use "must" and "should" in place of AND and OR and "bool" in place of WHERE.
Suppose in our ElasticSearch (at 179.168.0.1:9200), we have indexed documents having the following structures at index myOrder/myOrder:
{
"orderID" : xxxxx,
"customerID" : xxxxx,
"orderDate" : "yyyyMMddTHHmmss",
"itemLines" [
{
"itemLineID" : xxxx,
"productID" : yyyyy,
"quantity" : xxxxx
},
...
]
}
Below is the translation of the above SQL statements to equivalent ElasticSearch query, in the following example we use curl, and we want to get a max of 100 records in each query from start, i.e., 0):
Statement 1:
curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d
{
"from" : 0,
"size" : 100,
"query": {
"bool" : {
"must" : [
"match" : {"orderDate" : "20150101T140000" },
"match" : {"customerID" : 29439 }
]
}
}
}
Statement 2:
curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d
{
"from" : 0,
"size" : 100,
"query": {
"bool" : {
"should" : [
"match" : {"orderDate" : "20150101T140000" },
"match" : {"customerID" : 29439 }
]
}
}
}
Statement 3:
curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d
{
"from" : 0,
"size" : 100,
"query": {
"bool" : {
"must" : [
"range" : {"orderDate" : { "lte" : "20150101T140000" } },
"match" : {"customerID" : 29439 }
]
}
}
}
Statement 4:
curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d
{
"from" : 0,
"size" : 100,
"query": {
"bool" : {
"should" : [
"bool" : {
"must" : [
"match" : {"orderDate" : "20150101T140000" },
"match" : {"customerID" : 29439 }
]
},
"match" : { "customerID " : 20991}
]
}
}
}
Statement 5:
curl -XGET http://179.168.0.1:9200/myOrder/myOrder/_search d
{
"from" : 0,
"size" : 100,
"query": {
"bool" : {
"must" : [
"match" : {"orderDate" : "20150101T140000" },
"bool" : {
"should" : [
"match" : { "customerID " : 20991},
"match" : {"customerID" : 29439 }
]
}
]
}
}
}
download file now